SQL Server Does order of order by affect query runtime

piztneat 于 2023-06-28 发布在其他

关注(0)|答案(1)|浏览(142)

I have a discussion with a colleague of mine. Does the following change in the order by statement order really affect query times?

It does not affect the query execution plan...

CREATE TABLE [originals](
    [id] [bigint] NOT NULL,
    [prefix] [bigint] NOT NULL,
    [from_suffix] [int] NOT NULL,
    [to_suffix] [int] NOT NULL,
    [latest] [bit] NOT NULL,
    [order_created_on] [datetime2](7) NOT NULL
)

create index overlap_idx
  on originals (prefix, from_suffix, to_suffix, order_created_on desc)
  include (order_id)
  where latest = 1;

Will Queries A and B be different in performance?

-- A:
select
  ori.order_id,
  ori.prefix,
  ori.from_suffix,
  ori.to_suffix
from originals ori
where ori.latest = 1
and ori.prefix = ?
and ori.from_suffix <= ? and ori.to_suffix >= ?
order by ori.order_created_on desc,
ori.prefix,
ori.from_suffix

-- VS B:
--...
order by ori.prefix,
ori.from_suffix,
ori.order_created_on desc

sql-server

来源：https://stackoverflow.com/questions/76520860/does-order-of-order-by-affect-query-runtime

1条答案

按热度按时间

mfuanj7w1#

Both of these are going to require an extra sort either way, so it makes no difference what you do.

Of the where clauses, latest is in the index filter, and prefix is an equality comparison and a leading key column, so they can be removed from the calculation.

The final two where clauses are range checks and only one of them can be used as it's the next column in the index: from_suffix . The filter on to_suffix is going to be a residual predicate. The data will therefore be sorted by from_suffix, to_suffix, order_created_on desc as that's the order the index is in.

Neither this ordering

order by ori.order_created_on desc,
ori.prefix,
ori.from_suffix

nor this

order by ori.prefix,
ori.from_suffix,
ori.order_created_on desc

are in that order. Note that prefix is not relevant and can be removed, because it's already guaranteed to be a single value.

If you wanted to avoid an extra sort, you would need to either use this

order by
  ori.from_suffix,
  ori.to_suffix,
  ori.order_created_on desc

Or you can rearrange the index to match the order by that you want to use. This may mean that the range seek on from_suffix might not be used, but if it's not gaining much and the sort is the slow part then it could be worth it.

The best thing to do would be to remove the order by altogether. Then you don't need to worry about whether or not the data is in the correct order.

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server Does order of order by affect query runtime

1条答案

相关问题

热门标签

最新问答