does ordering by lot of columns hit the performance badly?

Question

I just ran into a SQL query with about 5 different columns in the ORDER statement, is it a good practice and how does it play with performance?

Answer 1

The number of columns isn't very significant. The most important part is, whether the order matches and index, and whether the database engine is able to recognize this and use the index properly. However, there is some difference, of course. The primary key is usually much faster than other indexes because the other indexes need to do a primary key lookup afterwards, and and indexing a single integer field performs much better than indexing string fields.

There are several religions with regard to how to structure a database. Usually, you can minimize the amount of data by using integers as primary key, but many large databases in enterprises use several string fields in the primary key. There can be several reasons for that, but performance isn't usually one of them.

Answer 2

If the business requirement stipulates that results be sorted by five columns then the only question to answer, assuming that there are at least a client and a server involved, is where to perform the sort?

So I'd guess that any answer to the question about good practice is going to have a huge dose of "it depends".

As to performance, if the requirement is for the sort, and performance is an issue, then again, look to see if there's a chance that sorting could be carried out away from the server.

Answer 3

Ordering on 5 or more columns is not question of good practice, it's about business rules: do you need it or not. If you must sort, then try to optimize it.

Some general steps:

1. First, check execution plan for target database.

2. If performance is at least "tolerable", then you're done.

3. Else:

   • vary number of fields in order by clause and see what performance difference it makes in execution plan. It should give you some hints about bottlenecks.
   • try to improve performance by "appropriate" indexing.
   • check hints your database has - maybe you can make query analyzer do it better way.