2

I am trying to compare the performance between two queries which give otherwise the same results.

The idea is that I want to compare using Windowing Functions available to newer versions to faking them with subqueries in older versions.

Here are the two queries:

select
    *,
    sum(total) over (order by id) as running,
    sum(total) over (partition by ordered) as daily,
    sum(total) over (partition by ordered order by id) as daily_running
from sales
order by id;

select
    *,
    (select sum(total) from sales s where s.id<=sales.id) as fake_running,
    (select sum(total) from sales s where s.ordered=sales.ordered) as fake_daily,
    (select sum(total) from sales s where s.ordered=sales.ordered and s.id<=sales.id) as fake_daily_running
from sales
order by id;

The results are identical.

I know that I can use EXPLAIN, but (a) I’m unclear how to interpret the results, and (b) I can’t see how to get a direct comparison between the two.

Microsoft SQL Management Studio has a tool which displays shows the estimated execution plan, and, most importantly, the cost of each query relative to the whole.

How can I get details on how the performance of the two queries would compare?

5
  • You can compare the performance in practice only. On the real array of data. EXPLAIN will not help - it is used to optimize the single query, and all its data is relative, not absolute. PS. The change in source data (and/or its statistic) may fully change the EXPLAIN result for the same query...
    – Akina
    Aug 20, 2018 at 5:59
  • The explain uses the same information as the query optimizer. While it is far from perfect it is often good enough, PROVIDED that the statistics are up-to-date and that the data is production-like and usually it is acceptable to run EXPLAIN in production. With outdated statistics and/or unrealistic data a practical test is just as bad. Start with the explain, which is cheap and easy to obtain; then by all means measure just in case.
    – ewramner
    Aug 20, 2018 at 8:31
  • Well, let's see the EXPLAINs, maybe we can help. As for running timings -- be sure to have enough data, and for the data to realistic.
    – Rick James
    Aug 27, 2018 at 13:01
  • SUM(...) OVER (ORDER BY ..) does not make sense. wouldn't it be the same to simply do SUM(...)?
    – Rick James
    Jul 1, 2021 at 18:49

1 Answer 1

3

You should use EXPLAIN, or by all means test with realistic data. Microsoft's tool is nice, but it is basically the same thing under the hood. The classic explain syntax (EXPLAIN SELECT ...) does not include cost, but it does include the estimated number of rows for each step. Better, you can use JSON to get more details:

EXPLAIN FORMAT=JSON SELECT ...

This will return a JSON document that includes query_cost. You don't get a fancy graph, but you can compare the estimated cost. Good luck!

Your Answer

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

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