Unanswered: JDBC: Will ORDER BY impact performance
Am using an Oracle 9i db with JDBC 2.0 drivers provided by Oracle.
I have a query which operates on a large volume of data and returns
about 1000 records (Out of around a million). Am fetching each of these
1000 records using a scrollable resultset.
My problem is, the time taken to fetch the records seems to depend
on whether my query contains an ORDER BY clause or not !
If my resultset is ordered, then it takes lesser time for the fetch than
if i don't have an ORDER BY clause.
Please note that my tables are quite big (joining 6/7 tables each having
about half a million records and taking top 1000 rows).
If i have the ORDER BY clause, each set of (rs.next(), rs.getXXX() calls)
take about 1 ms, while without the ORDER BY each of them take around
Also note that my server is in the US, while i connect to it from India.
So does the JDBC layer depend upon the structure of my query in
The database does the order by , nothing to do with JDBC or Java end of things. So what it means is the database is selecting a different execution plan depending on the order by clause. This indicates the order by clause is probably forcing the query to go in on an index which it wouldn't choose otherwise. Look at the execution plan of the query using sqlplus, TOAD or something similar.
To improve performance of the non order by clause make sure the tables have been analyzed recently. If this still doesnt fix it then you can either use hints in your query (if you have access), get your dba to check the optimizer settings (which can affect wether it goes for an index or not) or use plan stability to fix the execution plan for the non ordered by query.