Speed Tips For SQL Server SELECT Statements

Heck!

I got no improvement there at all! Both queries generate the same execution plan, and use the same amount of SQL Server’s resources to return the data. So is my theory all blown to hell? Not quite….

The key to understanding why the derived table technique may or may not produce a more efficient result is understanding the query optimizer.

The query optimizer looks at all SQL queries and works out the most efficient way of accessing the tables used in the query, primarily by using index statistics. While by re-coding my query I have given SQL Server a different set of instructions for how to get at the data I want, SQL Server has decided in both cases that the same method – or execution plan – is the optimal one. This is always not the case though.

Following exactly the same principle, here is a Group By and Derived table query that produce different execution plans to return exactly the same data. Again, both are for the Northwind database:

SELECT companyName,
MAX(orderDate)
FROM orders o INNER JOIN customers c
ON o.customerID = c.customerID
GROUP BY companyName


SELECT companyName, MAX(orderDate)
FROM customers c INNER JOIN (
SELECT customerID, MAX(orderDate) AS orderDate 
FROM orders
GROUP BY customerID) AS o
ON o.customerID = c.customerID
GROUP BY companyName

This time the optimizer chose to use different execution plans for the two queries, and the derived table version of the query comes up with roughly 30% improvement in terms of resources used to run the query.

Wrapping Up

You can see from the examples that the Query Optimizer sometimes needs a little help in picking the most efficient way to execute a query. It’s worth coding up a couple of versions of critical queries and comparing their performance characteristics to find the most efficient way of doing things.

Pages: 1 2




Related Articles :

  • No Related Articles Found

One Response to “Speed Tips For SQL Server SELECT Statements”

  1. Hi Neil,

    Nice article as it gave some more idea about writing queries. But I am amazed to see that in first query group by orderid,orderdate is working fine.
    Can you just explain this how this work as there can be more dates for one id.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |