Does adding more columns to ORDER BY have a performance penalty?
ORDER BY STYLE
vs.
ORDER BY STYLE, SIZE, COLOR
A:
Yes, but I don't think it is significant since you already have order by clause.
Andrew Bezzub
2010-10-29 10:15:23
Think that depends on de diversity of the first "order by" in general i'd highly doubt there is much of a performance hit but if you only have Two styles in your table, one of which you have twice, the other 90000 times then the second order by (if more diverse) would add some overhead.
F.B. ten Kate
2010-10-29 10:18:16
+3
A:
Generally speaking, yes. Imagine a table which has thousands of identical styles, and each style has many identical sizes with different colors. Then the SQL engine has to perform sorting for styles first, then for each style it has to sort sizes and finally colors. It's more time consuming than to sort styles alone. But the actual penalty may vary depending on table structure, indexes used, SQL server flavour, etc. You mileage may vary.
Igor Korkhov
2010-10-29 10:19:04
A:
It may have, that depends on which data you select, and which data you sort by.
Often there is no measurable performance difference at all. I just compared the execution plans of two queries that are identical, except one sorts on a single field, while the other sorts on 95 fields, and there was no difference at all in the CPU cost or the I/O cost for the sorting operation.
If I reduced the number of fields selected, so that the sort had to access data that would otherwise not be needed, there was a 1.6 percent difference in CPU cost. That is a difference, but certainly not significant.
Guffa
2010-10-29 10:45:12
A:
If you have an index that covers the sort that would get used then I guess it wouldn't change perf by that much if at all. i.e. If you have an indedx that has STYLE, SIZE, COLOR If you have 3 separate indexes STYLE, SIZE, COLOR or no indexes then perf would be worse becuase it would probably only be able to use one index to do the sort. Somtimes though SQL server may not use the index for the query plan even if a good one exists, depends on what the rest of your query is doing...
How do you find out the perf penalty.. In SQL Management studio write both queries and turn on execution plans, look for the subtree cost of the node on the left as an indicator of which query is the best in terms of cost on machine and therefore on performance. The results can change based on data though (If you have test server with small amounts of data you may get very different query plans than for large production data)
MikeG
2010-10-29 10:52:14
+1
A:
If you have a compound index on (style, size, color), and there are no WHERE clauses based on columns that aren't also in the index, you can have color ordering for free.
If you only have a compound index (style, size), then adding the color column to the ORDER will require the database to do some re-ordering, which will be a bit slower.
If you don't have an index that would have been used for ORDER-BY optimisation before, the database will have to re-order anyway, so you don't lose much by adding a column to the order.
Some background on ORDER-BY optimisation. The details here are MySQL-specific, but the general concept is common to indexing in relational databases.
bobince
2010-10-29 10:58:04