By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,557 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,557 IT Pros & Developers. It's quick & easy.

Fetch rows faster with JDBC v2.

P: n/a
System scenario: Linux DB2 Workgroup Server V7 FixPack 13.

I have a java application (JDBC v2 driver) that reads into memory a
considerable amount of data (100k-300k rows), summarizes
the results and then deletes data from the DB. This is done hourly.

According to my developer, the application opens a cursor and (fetch
next) until the end. I have created an event monitor and the
SQL sent to the DB is not poorly written (it uses the table PK).

The time required to read data from the DB is considerably higher than
deleting it. I was wondering if there is any configuration
within JDBC and/or db2 parameters to better handle prefetching, and
decrease my DB read time ?

Thanks in advance,

Michel

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Have you looked at an EXPLAIN PLAN of the statement?

Personally, i usually keep away from CURSORs (unless i am passing
information to a program), with the assumption that CURSORs are much
slower.

B.

Nov 12 '05 #2

P: n/a
It's not surprising that the retrievals take longer than the delete.
Retrieval requires formatting data for presentation to the application
and handling all of the "service me" calls (fetch) that the application
issues.

Try writing a complex SQL statement that performs the "summarization" in
the database manager. Retrieving only the summary results should be much
faster than fetching the source data and summarizing it in your program.

Phil Sherman

Michel Esber wrote:
System scenario: Linux DB2 Workgroup Server V7 FixPack 13.

I have a java application (JDBC v2 driver) that reads into memory a
considerable amount of data (100k-300k rows), summarizes
the results and then deletes data from the DB. This is done hourly.

According to my developer, the application opens a cursor and (fetch
next) until the end. I have created an event monitor and the
SQL sent to the DB is not poorly written (it uses the table PK).

The time required to read data from the DB is considerably higher than
deleting it. I was wondering if there is any configuration
within JDBC and/or db2 parameters to better handle prefetching, and
decrease my DB read time ?

Thanks in advance,

Michel

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.