Inspect JDBC select query for batch size - 【StackMirror】|java|jdbc|resultset|p6spy

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.

2012-04-05 22:04
by Nik

What does 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

@MiserableVariable I really think what he's trying to do is paginatio - Kevin 2012-04-05 22:16

@Kevin pagination should be done within SQL itself, I think OP wants to fetch all the rows but limit the number of round-trips to the db serve - Miserable Variable 2012-04-05 22:25

@MiserableVariable you are correct. I am trying to limit the number of round-trips to the db server. and also, what I meant was if there are 100 records in db, p6spy outputs the query 100 times, as I am iterating through resultSet.next(). I think the output is more deceptive and confusing me more - Nik 2012-04-05 22:53

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.

2012-04-05 23:00
by Miserable Variable

Thanks for the reply. So is there no tool or quick and easy way to debug what is sent by the JDBC driver to the database server? I tried wireshark and found it little difficult to use - Nik 2012-04-06 04:53

Did you try request logging on server? If you want to know what's going on between JDBC Driver and Database Server either one of them will have to tell you or you will have to snoop - Miserable Variable 2012-04-06 18:19

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.

2012-04-09 16:56
by Nik