UpdateRow() is way too slow. Can anyone help?
843854Sep 24 2003 — edited Sep 26 2003Hello, I'm running j2sdk1.4.1_01 and the Microsoft JDBC driver, against SQL Server 2000. I'm trying to update several fields in every row of a large table (~8 million rows), with values parsed out of another field in the same rows of the same table. Currently everything works, sort of. I'm reading the table into an updatable ResultSet, performing my parsing operations on each row, and then updating that row. The problem is that I need it to update fast, and right now it's running at a miserable 20 rows per minute with the UpdateRow() statement in. Without that statement, it shoots up to 5-10,000 rows per minute so I know my table reads and parsing aren't the slowdown. I need fast performance or I'll never be able to finish this operation, since my database will eventually grow to > 100 million rows. Here's a code snippet. Does anyone have any clues? This is aggravating me to death.
Statement LegalPull = conn.createStatement (ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
ResultSet lrs = LegalPull.executeQuery("SELECT top 100 * FROM [QA_REDMASTER].dbo.[Legal2] WHERE Legal is not null");
int i = 0;
while (lrs.next())
{
System.out.println(++i);
lp.setLegal(lrs.getString("Legal")); //constructor for my parser
lrs.updateString("LT",lp.getLegal("LT")); //parsing data...
lrs.updateString("UN",lp.getLegal("UN")); //parsing data...
lrs.updateRow();
}