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?
Best Answer
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: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!