10

I'm learning about Execution plans and am trying out different queries and comparing their performance and stumbled upon this:

SELECT StatisticID
FROM (
    SELECT StatisticID, ROW_NUMBER() OVER (ORDER BY StatisticID) AS rn
    FROM FTCatalog.Statistic
    ) AS T
WHERE T.rn <= 1000
ORDER BY rn

SELECT TOP 1000 StatisticID
FROM FTCatalog.Statistic
ORDER BY StatisticID

They both return same result set - however first one executes faster and is less resource hungry (at least SSMS tells me that) Here are execution plans: Execution plans

Comparison from SQL Query Plan Explorer: enter image description here Could anyone give me some insight on what's actually happening behind the scenes and why results differ? If there's anything else you need - just let me know.

Thanks, Evaldas.

Improve this question
1
  • For some reason SQL Server does not have good query rewrites for paging queries. These differences in plan and estimation should not exist for such a common case.
    – usr
    Mar 19, 2015 at 14:42

1 Answer 1

Reset to default
12

I guess you are comparing the estimated costs for the queries. Those are just estimates based on (among other things) the estimated number of rows returned by the query. Not the actual number of rows.

Your first query estimated that it would return 30 rows and your second query estimated 1000 rows. That is where your difference in query cost comes from.

If you change the queries to fetch only 30 rows you will see that the estimated rows is the same for the queries and the first query actually is costed a bit higher, at least for me in SQL Server 2014.

Don't use the estimates when comparing performance of queries. Use things like duration, number of reads and size of memory grants instead.

Improve this answer
4
  • 1
    To ascertain actual performance, run each of the queries several times (GO 10) with the Include Client Statistics option. I suspect you will find the actual execution times are closer than the relative cost estimates. There is no magic under the covers; the query plan operators tell the real story.
    – Dan Guzman
    Mar 19, 2015 at 11:52
  • Does relative cost (percentage expression) actually mean something in SSMS? That was bugging me most.
    – Evaldas Buinauskas
    Mar 19, 2015 at 11:53
  • @EvaldasBuinauskas Not sure if relative cost is useful for anything. Perhaps sometimes but I don't think that could ever be worth all the confusion it creates when people start to use the estimated percentage to compare performance of different queries. It will always be an estimate and estimates are always (almost) wrong.
    – Mikael Eriksson
    Mar 19, 2015 at 12:01
  • @EvaldasBuinauskas, those costs are gathered during the optimization process and exposed, but not intended to be an indicator or actual run times. The costs are just a best guess. See blogs.msdn.com/b/sqlqueryprocessing/archive/2006/10/11/…
    – Dan Guzman
    Mar 19, 2015 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.