I have a database with >100K records and I want to get the results as batch. I am using SQL server and the fetchsize set is 50. How do I make sure that only 50 records are retrieved and then the next 50.
I tried debugging the SQL using P6SPY and the output shows n queries for n number of records in resultset. I want to inspect the generated SQL and make sure the fetchsize is applied.
Please help.
I think P6SPY is application level tool, i.e. it sits between the application and the original JDBC driver and as such cannot spy the interaction between the JDBC driver and the database server.
If you suspect that the JDBC driver is not honoring the fetchsize, you will have to use either a network packet sniffer such as wireshark, use any request logging in the database server itself or any debug logging that the driver exposes.
Here is what I figured out: SQL server JDBC driver by default fetches all records into memory. Adding selectMethod=cursor
to the connection url string solves the issue and the batch size will now be used.
n queries for n number of records
mean? Do you mean each record is fetched in an individual round-trip to the db server - Miserable Variable 2012-04-05 22:09