Search code examples
databasesql-server-2005

SQL 'ORDER BY' slowness

Is it true that ORDER BY is generally pretty slow? I am trying to run some sql statements where the WHERE clause is pretty simple, but then I am trying an ORDER BY on a VARCHAR(50) indexed column.

I need to sort alphabetically for display reasons. I figured that getting the database to do it for me is the most efficient.

At this point, I am looking to either

  • optimize the sql query
  • sort the result set in code

Here is the actual query I am trying to run:

// B.SYNTAX is a TEXT/CLOB field
// Indexes on NAME, MODULENAME. PREVIOUS is a CHAR(1) with no index
"SELECT A.NAME, B.SYNTAX, B.DESCRIPTION, A.RATE1, A.RATE2, 
A.RATE3, A.STARTDATE, A.ENDDATE, A.HIDE, A.CATEGORYNAME 
FROM A, B WHERE A.MODULENAME='"+loadedModuleName+"' 
AND A.NAME = B.NAME AND (A.PREVIOUS<>'N' OR A.PREVIOUS IS NULL) 
ORDER BY A.NAME"

The size of table A is ~2000 rows and B is about ~500.

I should probably also mention that I cannot do much database specific optimization since we support multiple databases. Also, the application is deployed at a customer site.

I am expecting hundreds of records to be returned (less than 1000).

What would you do? Any tips are appreciated. Thanks.

Solution

  • I did some performance testing last night on a more production-type database (not the developer one) and here is what I found:

    Total rows in table A: 13000

    Total rows in table B: 5000

    Rows returned by join query : 5000

    Time taken if using with ORDER BY clause: ~5.422 seconds

    Time taken if not using ORDER BY clause: ~5.345 seconds.

    So it looked like the ORDER BY wasnt making much of a difference. (I am okay with the few milliseconds added).

    I also tested by setting all B.SYNTAX values to NULL to make sure that it wasnt just the network latency with transferring so much data.

    Now I removed the B.SYNTAX from the SELECT clause and the query took only 0.8 seconds!

    So it seems that the whole CLOB column is the bottleneck. Which doesnt mean that I have gotten the solution to making this query faster, but at least I wont spend time writing a sorting algorithm.

    Thanks to all who replied. I learned quite a bit and it led me to try a few different things out.