I've looking at code that has about 9 million rows to process, and see no attempts at optimization (in the code). I say that, because I don't see any attempts at setting fetch size, or use cursors. Now: Perhaps that is because the defaults are already the most performant.
There seems to be two approaches:
1) retrieve the entire resultset to the client
2) use server-side cursors, and a fetch size
Is this statement true:
1) if the client has unlimited (ie: "enough") memory, does fetching "all at once" to the client outperform a solution where cursors are used, and multiple fetches are performed?
I would tend to think so, because you're hitting the db and network just once. Granted, the Resultset will be massive on the client, but assuming you have the memory and CPU to handle it....
I suppose when you really get *right* down to measuring seconds in an hours-long process, perhaps there's a performance benefit (or only a perceived one?) to doing fetches. That being: You can start to processing the resultset much faster (after the first fetch) rather than waiting for it all to traverse. But... it's not like the driver is doing a background fetch, right? So now it's a discussion between "wait a long while, then never again" vs "wait less time, but many times over".
My database is Sybase: from googling, I think this tends to matter.
For example, here's an Oracle post that makes it clear that anyone not setting their batchsize is asking for sucky performance:
http://blog.lishman.com/2008/03/jdbc-fetch-size.html But... jTDS seems to indicate that for Sybase, it (by default) fetches everything anyways (note #4)...
http://jtds.sourceforge.net/resultSets.html So.. I'm thinking that the Oracle speed-boost is really only about " *IF* you are using cursors, then set your batch size correctly" But if I'm not using that, then I'm using a faster/fastest resultset possible already...
Do I have that sort of correct?
edit: changed title from:
JDBC - Resultsets - What is the right 'size' for best performance?
[ May 26, 2008: Message edited by: Mike Fourier ]