CODEWITHSUNDEEP
sqlperformanceoracle-databasedb2
chip
chip

Reputation: 11

Order By Columns

Does adding more columns to ORDER BY have a performance penalty?

ORDER BY STYLE

vs.

ORDER BY STYLE, SIZE, COLOR

Upvotes: 1

Views: 276

Answers (6)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

In the absence of any indexes on the columns: No.

The ORDER BY clause would never be turned into a series of SORT operations when executed. When sorting a list, the query engine will make one or more comparisons between rows in the result set; it uses the ORDER BY clause to determine whether one row should be before or after another row. It only needs to do this once.

The complexity of the expressions in the ORDER BY clause, of course, can have a performance impact (e.g. if one of them involved calling a function that performed some expensive operation), but the performance penalty is not directly related to the number of sort columns per se.

Upvotes: 2

bobince
bobince

Reputation: 536539

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.

Upvotes: 1

MikeG
MikeG

Reputation: 1069

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)

Upvotes: 0

Guffa
Guffa

Reputation: 700552

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.

Upvotes: 0

Igor Korkhov
Igor Korkhov

Reputation: 8558

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.

Upvotes: 3

Andrew Bezzub
Andrew Bezzub

Reputation: 16032

Yes, but I don't think it is significant since you already have order by clause.

Upvotes: 0

Related Questions