0

I am going to be given a staging table, which I run a process to load the data into a bigger table, and remove data from the staging table. Some rows in the staging table, denote deletions of rows in the main table though.

Right now the algorithms is as follows:

ResultSet dataToLoad = select * from ...;
ResultSet mainTable = select * from ...;

while (dataToLoad.next())
{
     if(insert)
         //insert this row into main table, and remove row from this table
     else if(delete)
         //Find the row that matches in the main table and delete it. remove row from this table
}

My question is for the delete block. Would it be best to make a new ResultSet to find the row using a where and just delete the single row in the set, or to put the cursor to the beginning and loop through the cursor checking conditions? Essentially like implementing a where clause in java code.

How much does JDBC cache when you load in a ResultSet? is it going to do a full network round trip for each cursor jump?

Lastly, I read up briefly on the Halloween Problem. Am I at risk of that here?

Cruncher
  • 7,641
  • 1
  • 31
  • 65
  • 2
    Why don't you use a PreparedStatement with a delete query for the deletes? – Marcelo Sep 10 '13 at 21:00
  • Cursors are Evil. Learn about SETS and how to perform updates, deletes, etc on an enitre set rather than on each individual record in the set. http://stackoverflow.com/questions/287445/why-do-people-hate-sql-cursors-so-much – Declan_K Sep 10 '13 at 21:05
  • @Declan_K A lot of people talk about set techniques on that answer, but ironically I don't see a single actual link. – Cruncher Sep 11 '13 at 12:37

1 Answers1

1

Why dont you try to load a List of inserts and deletes, for example

List deletesId = .....

Whie(...){
  if(delete) {
      deletesId.add(idDataForDeleting);
  } 
}

And then you can do something like:

String sql = "DELETE FROM TABLE WHERE ID IN (" + separateByComma(deletesId) + ")";
executeDelete(sql);

Of course, try to use PreparedStamment, I mean with this you will the delete operatabion in one step against the database, is better than one by one.

Daniel Hernández
  • 4,078
  • 6
  • 27
  • 38
  • I thought about building the list. My problem is that the data in the staging table, doesn't have ids to the main table. I've been told of 3 columns that guarantee uniqueness, so that's what I would use to find my row. – Cruncher Sep 10 '13 at 21:53
  • does syntax like `(col1, col2, col3) in ((val1, val2, val3), (val1, val2, val3), ...)` work? – Cruncher Sep 11 '13 at 12:42
  • I do not think that will works, What about building a big String of delete? And then you cand send it against it the db. – Daniel Hernández Sep 11 '13 at 14:22
  • For example: while(...){ if(delete) deleteString += makeStringDelete() + ";" ; } executeDelete(deleteString); send all the deletes will be better than one by one, I do not know how you are handling the database connections, but If you are openning and closing it, Will be betther execute them in one step. – Daniel Hernández Sep 11 '13 at 14:25
  • Well, to add to the confusion, I don't use JDBC directly. I work with a library that's been built on top of it before I came. I am fairly sure that connections are held for the life cycle of the application. This is inside a web application, and the databases are accessed frequently. By make a delete string do you mean `where col1=val1 and col2=val2 and col3=val3 OR ...`? – Cruncher Sep 11 '13 at 16:01
  • This is also why I can't use prepared statements, the library just accepts a database name, which maps to the connection, and a query. It gives me back a ResultSet object(which at least is actual JDBC) – Cruncher Sep 11 '13 at 16:03