SQL 'ORDER BY' slowness

Question

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.

Answer 1

It shouldn't be slow. Optimize your query and database structure (at least indexes and statistcs if it's SQL Server). Maybe there is some other thing in your query other than ORDER BY which causes this slowness?

SELECT A.NAME, B.SYNTAX, B.DESCRIPTION, A.RATE1, A.RATE2, A.RATE3,
       A.STARTDATE, A.ENDDATE, A.HIDE, A.CATEGORYNAME
FROM Table1 A JOIN Table2 B on A.Name = B.Name
WHERE A.MODULENAME = @ModuleName AND A.PREVIOUS<>'N' OR A.PREVIOUS IS NULL
ORDER BY A.NAME

Option 1

If you're quering just a few simple columns (2-4), you can include them into the index as well. This way your query will be ran faster. Also make sure that sorting order on that index column matches sorting order in your query.

// if your query looks like this:
SELECT [Name], [Title], [Count] ORDER BY [COUNT]

// you can create an index on [Name], [Title], [Count]

Option 3

Create a view and bind it to the schema. Then query data from that view.

Option 3

If you use SQL Server 2005 and obove, you can also try to run you query in SQL Server Profiler and it will recommend to you the best index and statistics which you can apply to your table in order to optimize this particular query's performance.

Option 4

Try to rebuild your indexes and statistics.

Option 5

You can try putting you index/table into separate filegroup on different hard drive.

Answer 2

If your filter looks like this:

WHERE col1 = @value1
      AND col2 = @value2
      AND col3 = @value3
ORDER BY
      col4

, then you'll need to create an index on (col1, col2, col3, col4).

The optimizer will use the index both to filter on the first three values and to order by the fourth one.

If you don't have such an index, then one of the following will happen:

1. Optimizer will use an index on to filter on the WHERE condition, but it will still have to ORDER remaining rows.
2. Optimizer will use an index to ORDER the values, but ALL values will need to be looked upon to filter them out.
3. Optimizer will not use an index at all, so both cons from 2 "ALL values will need to be looked upon to filter them out" and 1 "all remaining rows have to be ordered" are true.