Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004

    Question 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.getXXX() calls)
    take about 1 ms, while without the ORDER BY each of them take around
    25 ms.

    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
    anyway ?

    Am also posting this at the JAVA forumn.

    Please advice.

  2. #2
    Join Date
    Mar 2002
    Reading, UK
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts