Web Development

Ask something
Discussion Locked

JDBC using oracle database

0 helpful
2 replies

by suresh.kotra

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

We have two queries
1. A join query involves 32 fields from 4 tables.
2. Query from a single table involving retrieval of the same 32 fields.(Table contains a total of 650 columns)

There is a large variation in times taken for traversing through the database(JDBC resultset)
Both the queries will retrieve around 1 Lakh Records.

The time taken in case of Join Query is:
Time to execute the query --> 11-12 secs
Time to traverse through the dataset(resultset) --> 6 minutes

The timetaken in case of Query from single table is:
Time to execute the query --> 100 millisecs
Time to traverse through the dataset(resultset) --> 12-13 minutes

We would like to know the reason for this variation in time taken for traversal throughthe datasets(resultsets).
and also like to know is this because of the the total number of columns present in the single table.
In case, if it is yes, why does the join query perform well during traversing.

2 replies

by mjzalewski

I bet your query is using a server side cursor.

Try sorting the second query by some column which is not indexed, and see if it takes so long to traverse the recordset. (The idea here is to make sure that nothing gets returned until the server has found all the rows which match the querey).

Without knowing a *lot* of factors, it's hard to answer this question. But I guess that the second query runs faster because of the server side cursor, which can return the first matching rows of a query before the query completes.

You may be able to improve the Recordset traversal time of the second query by setting the batch size. Making the batch size bigger will mean that the query will take longer to execute, but will return a greater number of rows per network transaction.

by saurabhambekar

Could you send us the queries (Both). since its hard to tell from the statistics that you have provided here.
Also, you have written that both the queries retrieve 1 lakh rows and 32 columns. I hope that the results are not identical. since one query is just on one table where as other has 4 tables involved in it with lot of joins (i believe).
"There are lot of factors involved in it."

This discussion is closed to new replies.