Can anyone provide some insight as to why one query might be more optimal than the other?

DBNewbie_2007 2014-06-04 18:13:04

Sorry if this seems very basic… still somewhat new to query tuning and trying to learn methods to improve queries using query plans.

There are three queries that return the same data from a one to many table join.

Statement1 uses the keyword "DISTINCT"

Statement2 uses a COUNT() to group the data

Statement3 uses EXISTS instead of a join:

-- Statement 1
SELECT DISTINCT d.dept_id, d.dept
FROM dept d
INNER JOIN employee e
     ON e.dept = e.dept
WHERE d.dept = 1;
 
-- Statement 2
SELECT COUNT(d.dept_id), d.dept_id, d.dept
FROM dept d
INNER JOIN employee e
     ON e.dept = e.dept
WHERE d.dept = 1
GROUP BY d.dept_id, d.dept;
 
-- Statement 3
SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT e.dept FROM employee e WHERE  d.dept = 1);
@SQLTrooper 2014-06-04 18:18:29
If Database1.Schema1.Table1 is rather large, you will want to get rid of that clustered index scan. You can do so by creating a non-clustered index on Column1 and Column2, including Column3. That should turn it into an index seek, which is much more efficient. I'm sure others will have more to add.
Ranga 2014-06-04 18:31:54
open sql profiler and see which stmt uses the minimum reads/cpu. Also, run the queries with set statistics io on, copy the read stats from the messages window after query execution and paste the results here: http://www.statisticsparser.com

You can see which tables did the high reads etc and go from there. I am not a big fan of SQL plans which sometimes don't tell the whole story.