Requirements: I have to scan the whole table and update every single record, period.
As suggested by other people, I should create a temporary table with identical schema with the original one and do insertion on updated values, then rename table instead of updating on original one.
The idea is something like this:
ResultSet row = select * from old_table;
While row.next
do something to update values in this row
insert updated values in to a identical table (different name of course)
endWhile
The question here is I am using Java JDBC, and I have to deal with ResultSet object. So is there a way to prevent "ResultSet row = select * from old_table" generate a out of memory exception?
A potential solution is to paging, but that means I have to use ORDER BY and LIMIT, which can be very slow on a 3 millions rows table.
Is there some trick with ResultSet, like specifying some of the flags like FOWARD_ONLY | NON-SCROLLABLE, etc. Or does Mysql server have some configuration to do smart things like somehow mysql understands that I am doing a full table scan, so just sequentially return records for me, but not all at once.
Any suggestion is welcomed
[UPDATE] seems like MySQL connector/J has configuration parameter called useCursorFetch, if set to true then statement.setFetchSize(1000) will work. Not sure if this is the ultimate solution or not.
You can set a JDBC statement to non-buffered this way:
stmt.setFetchSize(Integer.MIN_VALUE);
But I would also suggest tying to use an INSERT... SELECT statement, so you don't have to use a while loop and don't have to fetch anything. If you can use SQL expressions for your step of "do something to update values" then you can do the whole operation in one SQL statement.
PS: You'll have to be more specific about QuerySet. I find multiple classes called QuerySet in different libraries, e.g. org.dbunit.ant.QuerySet, org.gusdb.wdk.model.QuerySet, etc.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments