Instantly Speed Up SQL Query by Using Conditional In Your Joins
Filed under SQL , SQL Server , T-SQL
Looking for a fast and easy tip to speed up your SQL queries then look no further. Using a conditional AND in your join will improve performance rather than putting it in the traditional WHERE clause. Let me explain by using a simple book order example.
Say we want to pull all the orders and the associated book name. We have to query the Orders Table INNER JOIN with the Books Table (assuming Referencial Integrity is enforced). This would look something like this:
SELECT tblBooks.Book_Name, tblOrders.Order_ID
FROM tblOrders
INNER JOIN tblBooks
ON tblBooks.Book_pk = tblOrders.Book_fk
Now lets say we want to grab all the orders and the associated book name of just one book. Here is where you can gain significant performance.
A lot of developers attack this the wrong way and correct me if I am wrong. Here is the SLOWER way of accomplishing this task:
SELECT tblBooks.Book_Name, tblOrders.Order_ID
FROM tblOrders
INNER JOIN tblBooks
ON tblBooks.Book_pk = tblOrders.Book_fk
WHERE tblBooks.Book_pk = 123
What this does is grab ALL the orders associated with books and AFTER it has gotten ALL the Orders back it then does the WHERE clause and filters only the Orders with the primary key "123". This is inefficient because we dont need to grab ALL the Orders in the first place. Instead the WHERE clause should be placed in the JOIN because that gets fired first. The more efficient and FASTER way is this way:
SELECT tblBooks.Book_Name, tblOrders.Order_ID
FROM tblOrders
INNER JOIN tblBooks
ON tblBooks.Book_pk = tblOrders.Book_fk
AND tblBooks.Book_pk = 123
This way you only grab the Orders that are needed to begin with. I might have stated something wrong and feel free to let me know if there is a better way of doing this. One last thing to remind yourself whenever writing a query, always look at the order in which the clauses get fired.
Feb19