Comparison Between Two Queries Using Execution Plan

44 views Asked by At

I am trying to compare two queries using this link from Stack Overflow Query Performances. It's an old post that I checked and when tried to analyse the query performances using execution plan, bit confused. So here that I did to find out the differences:

Created the table with no index:

create table tbl 
(
    id int identity primary key, 
    a int,
    b int,
    c int, 
    d int
)

insert tbl values (1, 2, 3, null)
insert tbl values (2, null, 3, 1)
insert tbl values (1, null, 1, 4)
insert tbl values (1, null, 3, 5)
insert tbl values (1, null, 3, 6)

insert tbl select a, b, c, d from tbl --10
insert tbl select a, b, c, d from tbl --20
insert tbl select a, b, c, d from tbl --40
insert tbl select a, b, c, d from tbl --80
insert tbl select a, b, c, d from tbl --160
insert tbl select a, b, c, d from tbl --320
insert tbl select a, b, c, d from tbl --640
insert tbl select a, b, c, d from tbl --1280
insert tbl select a, b, c, d from tbl --2560
insert tbl select a, b, c, d from tbl --5120
insert tbl select a, b, c, d from tbl --10240

I then executed these queries:

SELECT 
    (SELECT SUM(a) FROM tbl WHERE a = 1) AS a,
    (SELECT SUM(b) FROM tbl WHERE b = 2) AS b,
    (SELECT SUM(c) FROM tbl WHERE c = 3) AS c

SELECT
    SUM((CASE WHEN a = 1 THEN a ELSE NULL END)),
    SUM((CASE WHEN b = 2 THEN b ELSE NULL END)),
    SUM((CASE WHEN c = 3 THEN c  ELSE NULL END))
FROM
    tbl

According to the previous post and accepted as answer (the link that I've given above); I won't bore you with images here but look at the plan which will show a cost of about 75% against the top query and 25% against the bottom. That's expected, 75%:25% = 3:1 which is due to the first query passing through the table 3 times exactly.

In my case, when I run queries, I get the opposite results as follows:

For the first query with subquery:

Three Subqueries

For the second query with case:

Query With Case

According to my understanding the second query would be faster and cost would be less as compared to the one I get (Got 85% and the original poster mentioned that should be around 25%) as it runs in a single query. For query one, as it has three queries, have cost of 27%. I believe, combinedly that would become 81% that seems less than the second query in my execution plan.

My question would be - Is there anything that I missed or misinterpreted with the execution plan or it's the expected cost that I supposed to get?

0

There are 0 answers