SQL Performace using WHERE clause

[ideafixer]

For the two following SQL Statements:

SELECT *
FROM LargeTable
INNER JOIN SmallerTable ON LargeTable.ID = SmallTable.ID
WHERE SmallerTable.FilterField = ‘Sample’
LargeTable.FilterField = ‘Sample1’

SELECT *
FROM LargeTable
INNER JOIN SmallerTable ON LargeTable.ID = SmallTable.ID
WHERE LargeTable.FilterField = ‘Sample’
SmallerTable.FilterField = ‘Sample1'

Does precedence of the WHERE clause matter? If I specify the order of WHERE conditions in a different sequence will this positively/negatively impact performance. I know sub selects are more efficient when filtering data from a large data set before the join is done. Since the JOIN clause will only join the needed records not perform the join on all data then implement the WHERE.

What about the WHERE itself? Does SQL server filter in a certain order that can be taken advantage of for efficiency?

 

[Catadmin]

I have a question for you. Are you using AND or OR in your WHERE clause?

Actually, I've never noticed a performance difference in a SQL WHERE clause that small. Nor have I noticed a difference in the returned recordset unless I was using a Left Outer or Right Outer JOIN, or the WHERE clause used a datetime comparison that I didn't phrase correctly.

The difference between AND and OR will get you though. If you use AND, both conditions must be true in order for your records to return. If you use OR, either condition can be true and you'll get more records.

Does that make sense?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ideafixer]

I was thinking something a bit more complex like this:

filter a = a AND
(
Filtera = 1 or
(
(Filterb = 2 or filterb = 3) and (filter3 = x and filter4 = c)
)
or filter 5 = j
)

 

[Catadmin]

In a case like this, I would say, yes, it does matter conditional goes first.

For performance's sake, you want to place the conditional that will be met most often right after the WHERE clause. So, design your nested OR clause with MostUsed, SometimesUsed, and LeastUsed conditionals in that order. I don't know if anyone else has noticed this, but I have noticed when checking values, if I put the most used first, I get a better return time on my queries.

Additionally, if you put your OR statements outside of your main AND statement, you will get a completely different recordset than the way you currently have your WHERE clause constructed.

Does this information help?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ideafixer]

Yeah.. thanks much!