Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    15

    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.next(), 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 ?

    Please advice.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: JDBC: Will ORDER BY impact performance

    Originally posted by sjanarth
    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
    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 ?

    Please advice.
    I don't think JDBC has anything to do with your issue. I believe what's happening is when you have an ORDER BY clause the query results (~1000 rows) are placed by Oracle into a temporary table to perform the sort and all fetches come from that relatively small temporary table. If you don't sort the results then there's no temporary table and each fetch goes to the joined original tables to find the next matching record. I think it's just one of the possibilites; you may need to work with your DBA to figure out what actually is going on.

Posting Permissions

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