SQL Server 2012 (performance tuning queries)

Question

I have a quick performance question. I have a select statement as follows. Which one would be faster if all tables are indexed and pretty huge (over million records in each).

SELECT A.col1, B.col2, C.col3, D.col4
FROM tableA A WITH (NOLOCK) 
INNER JOIN tableB B WITH (NOLOCK) ON A.col1 = B.col1
INNER JOIN tableC WITH (NOLOCK) ON B.col2 = C.col2
INNER JOIN tableD WITH (NOLOCK) ON C.col3 = D.col3
INNER JOIN tableE WITH (NOLOCK) ON D.col4 = E.col4
INNER JOIN tableF WITH (NOLOCK) ON E.col5 = F.col5
INNER JOIN tableG WITH (NOLOCK) ON F.col6 = G.col6
WHERE A.col1 = 95
AND B.col2 = 96
AND C.col3 = 97
AND G.col4 = 98 

OR

SELECT A.col1, B.col2, C.col3, D.col4
FROM tableA A WITH (NOLOCK) 
INNER JOIN tableB B WITH (NOLOCK) ON A.col1 = B.col1 AND A.col1 = 95 AND B.col2 = 96
INNER JOIN tableC WITH (NOLOCK) ON B.col2 = C.col2  AND C.col3 = 97
INNER JOIN tableD WITH (NOLOCK) ON C.col3 = D.col3
INNER JOIN tableE WITH (NOLOCK) ON D.col4 = E.col4
INNER JOIN tableF WITH (NOLOCK) ON E.col5 = F.col5
INNER JOIN tableG WITH (NOLOCK) ON F.col6 = G.col6 AND G.col4 = 98

Answer 1

The query you write is very different then what is actually executed in sql server, sql server optimiser interoperate the query it thinks is best performance wise.

In the given scenario sql server optimiser is intelligent enough to see both queries are essentially the same and will come up with the same execution plan.

Hence I think the query performance for both queries will be the same.

On a side note since you have used table hint WITH (NOLOCK) for every table in the query it is equivalent to having transaction isolation level set to read uncommitted, you can clean up your query a little bit by removing all of these table hints and just changing the transaction isolation level to read uncommitted before executing the query something like......

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

<Your query here>
GO

Test

I wrote two different queries in AdventureWorks2012 one with filter condition in WHERE clause and one with filter conditions in ON clause.

SELECT
    s.SalesOrderID,
    s.CustomerID,
    p.FirstName
FROM Sales.SalesOrderHeader AS s
 INNER JOIN    Sales.Customer AS c    ON s.CustomerID = c.CustomerID
 INNER JOIN    Person.Person    AS p  ON c.PersonID = p.BusinessEntityID  
WHERE C.CustomerID = 29825
 AND  p.FirstName = 'James'
GO

SELECT
    s.SalesOrderID,
    s.CustomerID,
    p.FirstName
FROM Sales.SalesOrderHeader AS s
 INNER JOIN    Sales.Customer AS c    ON s.CustomerID = c.CustomerID
                                     AND C.CustomerID = 29825
 INNER JOIN    Person.Person    AS p  ON c.PersonID = p.BusinessEntityID  
                                     AND p.FirstName = 'James'
GO

Now if you look at the execution plans for both queries they are identical.