SQL query performance
I have 3 different but equivalent queries. Do you mind tell me how to check and see which one has the best execution performance?
-- 1
select a from t1
where mod(a,5) = 0 and b in
( select b from t2
where c in ( SELECT c FROM t3 where mod(d,5) = 0 ));
-- 2
select t1.a from t1, t2, t3 where
t1.b = t2.b and t2.c = t3.c and mod(t1.a,5) = 0 and mod(t3.d,5) = 0;
-- 3
select t1.a from (t1 join t2 on t1.b = t2.b ) join t3 on t2.c = t3.c
where mod(t1.a,5) = 0 and mod(t3.d,5) = 0;
-- 1
select a from t1
where mod(a,5) = 0 and b in
( select b from t2
where c in ( SELECT c FROM t3 where mod(d,5) = 0 ));
-- 2
select t1.a from t1, t2, t3 where
t1.b = t2.b and t2.c = t3.c and mod(t1.a,5) = 0 and mod(t3.d,5) = 0;
-- 3
select t1.a from (t1 join t2 on t1.b = t2.b ) join t3 on t2.c = t3.c
where mod(t1.a,5) = 0 and mod(t3.d,5) = 0;
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Issue following command in SSMS:
And now you have two sets of results:
The Message Tab contains the number of logical reads and the total execution time. It is the first sign of the query performance.
The Execution plan Tab shows how the query was executed in all details. The Query cost or the percentage in each query shows what of the three variants is the best one.
SET STATISTICS IO, TIME ON
And now you have two sets of results:
The Message Tab contains the number of logical reads and the total execution time. It is the first sign of the query performance.
The Execution plan Tab shows how the query was executed in all details. The Query cost or the percentage in each query shows what of the three variants is the best one.
In Microsoft SQL Server you don't use MOD() ... instead you use % (see % (modulus) )
i.e.
select MOD(15,5) -- will fail in sql server
select 15 % 5 -- this will work (and return 0)
But, in SQL Server you can lose the unnecessary parentheses in your joins (if using Access it might insist on them even if they are redundant).
& while the word "inner" is optional I encourage you to use it.
select t1.a
from t1
inner join t2 on t1.b = t2.b
inner join t3 on t2.c = t3.c
where mod(t1.a,5) = 0
and mod(t3.d,5) = 0
;
Version 2 is simply the old fashioned way of writing exactly the same query .
i.e.
select MOD(15,5) -- will fail in sql server
select 15 % 5 -- this will work (and return 0)
But, in SQL Server you can lose the unnecessary parentheses in your joins (if using Access it might insist on them even if they are redundant).
& while the word "inner" is optional I encourage you to use it.
select t1.a
from t1
inner join t2 on t1.b = t2.b
inner join t3 on t2.c = t3.c
where mod(t1.a,5) = 0
and mod(t3.d,5) = 0
;
Version 2 is simply the old fashioned way of writing exactly the same query .
Hmm... The question was "Do you mind tell me how to check and see which one has the best execution performance?"
Paul pointed to the syntax error which you've solved already but you did not tell which one of the tree queries (with respect to the equality of the two join variants) executed with the best performance? What measurement method did you use?
Of course, you have to measure it yourself because the results depend on the existing data and indexes.
Paul pointed to the syntax error which you've solved already but you did not tell which one of the tree queries (with respect to the equality of the two join variants) executed with the best performance? What measurement method did you use?
Of course, you have to measure it yourself because the results depend on the existing data and indexes.
PLease also note that IN and JOIN are not givng the same results. A joiin B on a.a =b.a can result in rows of A repeated if there is more than one matching row in B.
In general, IN works best if you have a lot of "duplicate" IDs, or the IN list contains a low amount of rows.
Also note that if you perform measuring now with a particular dataset, performance might vary heavily with other data. There is no reliable method to tell if a query runs faster than a different one, because it always depends on data. E.g. you can't test with 100 rows, that will always give "fast" results no matter how "bad" the query is.
In general, IN works best if you have a lot of "duplicate" IDs, or the IN list contains a low amount of rows.
Also note that if you perform measuring now with a particular dataset, performance might vary heavily with other data. There is no reliable method to tell if a query runs faster than a different one, because it always depends on data. E.g. you can't test with 100 rows, that will always give "fast" results no matter how "bad" the query is.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial