0

Simple query example:

Select t1.A, t1.B, t1.C, t1.CustomerID
from table1 t1
join table2 t2 on t1.CustomerID = t2.Id
where t1.A = 'x'
and t1.B = 'xx'
and t1.C = 'xxx'

I created a non clustered with A, B, C, CustomerID for table1

I turned on statistic io. I recreated the same index and moved the order of the columns around and ran the queries at least twice for each order and checked the logical read count. After several trials, I noticed that one order: A, B, CustomerID, C produced the least number of logical reads, instead of something like A, B, C, CustomerID. I know this is a simple query and I haven't indicated the schema.

In general, it seems the order matters but I thought if I placed the where clause columns together, it doesn't matter in what order as SQL Server will figure it out. In the case above, the join column came between the where clause columsn and had an effect. I am not sure why? Does column selectivity matter in deciding where to place the column in the order? What are other considerations? Right now I am just doing it by trial and error.

1

If all your filters are equality matches (such as =, rather than >, LIKE, !=, etc), then it's going to useful to list them first. Then the data that matches those three conditions will be easily locatable, and in CustomerID order, which gives good options for being used in a join. Remember that your matching on CustomerID needs to join on several things.

Oh, and if your CustomerID is non-nullable and has a FK, then the Query Optimizer might not even be looking at t2.

| improve this answer | |
  • Is it different if the CustomerID is nullable? I mean if there is an FK, it still shouldn't have to look at the t2 table, just remove the rows where CustomerID IS NULL. – ypercubeᵀᴹ Apr 15 '16 at 12:48
  • Yes, but the Optimizer doesn't do that. If you had WHERE CustomerID IS NOT NULL it will simplify it out, even though that predicate looks like it's redundant. – Rob Farley Apr 15 '16 at 12:51
  • Thnx, didn't know this. – ypercubeᵀᴹ Apr 15 '16 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.