What are most people doing to handle queries with large result sets. By
large, I mean results that have perhaps 200 or more rows and in which having
the full resultset would likely be a performance and memory issue. Often,
we have queries that could return thousands of rows, especially those
performed by batch processing programs/threads.
It seems that the setFetchSize() is not well implemented across JDBC
platforms, and that the LIMIT/OFFSET combo, while workable despite the
potential for overhead on the database (since it returns only a subset, yet
the entire query must be evaluated) is not implemented everywhere, including
Oracle 8i if I'm not mistaken.
The other option appears to be to use cursors, though I'm not sure how well
that's supported across JDBC drivers either. For sure, the syntax for
declaring and using a cursor vary among different database, though I can
work around that pretty easily.
What are most people doing these days for handling large queries, especially
those that have to support not only PostgreSQL, but also Oracle, DB2/UDB
and/or MSFT SQLServer?
Thanks,
David