ResultSet interface - regarding

[Sub swamy]

When we have a statement such as

            ResultSet rs = stmt.executeQuery(<SQL_QUERY> :wink: ;

Assume a scenario where SQL_QUERY returns 100 rows.

1) Would objects corresponding to these 100 records be created immediately after this statement in the appserver side?

2) Or is it that no objects get created in the app server side until we start accessing the records by saying rs.next() ? In this case, even though the SQL_QUERY could run into a large number of records (a few thousands say), we would create only as many objects corresponding to the number of iteration of while (rs.next())

Would appreciate if someone could throw more light into this.

[Rajasekar Elango]

Hi,

The no of records fetched depends on fetchSize of statement object.
Check out setFetchSize()/getFetchSize() methods of statement.

Basically fetchSize of records will be bufferred and call to rs.next() will reload next fetchSize of records if buffer is exhausted..

You could measure the performance by adjusting fetch size..

Regards,
Raja

[Maximilian Xavier Stocker]

Originally posted by Rajasekar Elango:
Hi,

The no of records fetched depends on fetchSize of statement object.
Check out setFetchSize()/getFetchSize() methods of statement.

I feel this statement needs some correction. The number of records fetched depends on how the driver implements this. setFetchSize allows you to provide "hints" to the driver which may or may not be taken into consideration.

[Maximilian Xavier Stocker]

Originally posted by Subramanian PN:
When we have a statement such as

            ResultSet rs = stmt.executeQuery(<SQL_QUERY>  :wink:  ;

Assume a scenario where SQL_QUERY returns 100 rows.

1) Would objects corresponding to these 100 records be created immediately after this statement in the appserver side?

2) Or is it that no objects get created in the app server side until we start accessing the records by saying rs.next() ? In this case, even though the SQL_QUERY could run into a large number of records (a few thousands say), we would create only as many objects corresponding to the number of iteration of while (rs.next())

Would appreciate if someone could throw more light into this.

There is no answer to these questions. What will actually happen depends on the type of cursors you use and how your driver and database support those.

The best answer is to use best practices like these

- select as small subsets of data as possible. the database should be doing your record filtering not you
- forward only and read only cursors will cost less than their counterparts. use scrollable and updatable cursors when appropriate but don't abuse them either.
- Always make sure to close all the result sets and statements you open. This will allow the driver and database to release all the resources allocated to your queries, cursors etc.