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}