T-SQL Performance With INNER JOINs
Lets take a quick look at INNER JOINs in T-SQL. This select statement is a very typical:
SELECT *
FROM Products
INNER JOIN Category ON Products.CategoryId = Category.CategoryId
WHERE Products.AccountId = @AccountId
However, it can be rewritten to be much faster:
SELECT *
FROM Category
INNER ON Products JOIN Products.CategoryId = Category.CategoryId AND
Products.AccountId = @AccountId
Here is why: In the first statement all the rows in the products table are being joined to all the rows in the category table, producing a bigger table, that is then walked to find the correct account. This might make a very large table after the join if there are many accounts and the category table is very wide. SQL Server has to deal with this large in query table being produced. However, in the second statement only the products that are in the required account are joined to the category table -- significantly reducing the join.
It is important to note you can only reduce the size of the join on the tables you are joining too (not the first table). So you might need to rotate the first table. In the case above the category table is likely smaller then the products table and doesn't have accounts so I rotated it up.
{6230289B-5BEE-409e-932A-2F01FA407A92}
Comments
Post a Comment