July 14, 2022 at 12:12 pm
Suppose I have a table called PEOPLE having three sections, ID, LastName, and FirstName. These segments are not really recorded. LastName is more interesting, and FirstName is less remarkable.
Assuming I do two hunts:
select * from PEOPLE where FirstName="F" and LastName="L"
select * from PEOPLE where LastName="L" and FirstName="F"
My belief is the second one is faster because the more unique criterion (LastName) comes first in the where clause, and records will get eliminated more efficiently. See this Here, I also don't think the optimizer is smart enough to optimize the first SQL query.
Any Suggestions, Thanks in advance.
July 14, 2022 at 1:38 pm
It generally matters little, if at all -- the optimizer should be able to use statistics to order the predicates optimally.
Far more important are statistics and indexing. Without an appropriate index, the query will have to scan the table or some index. If the first n columns in an index match search predicates, the query engine can seek or at least scan a subset. Without up-to-date statistics, the optimizer may make bad guesses on which index to use and how to use it.
An index on FirstName and LastName, with one of them being the first column in the index and the other being the second column in the index, would probably be able to directly seek the matching rows with either of these queries.
An index starting w/ FirstName (but not LastName) would be able to find all the "F"s, but would then still have to scan through the LastNames to find "L".
An index starting w/ LastName (but not FirstName) would be able to find all the "L"s, but would then still have to scan through the FirstNames to find "F".
July 14, 2022 at 1:59 pm
The optimizer will absolutely rearrange your WHERE clause. It's part of how it works. It'll also rearrange and reorder your JOINs. So, from the most simplistic approach, WHERE & JOIN order don't matter.
HOWEVER...
The optimizer only uses so many resources and so much time it can spend doing this sort of thing. So, it's a good habit to get into ordering your JOIN and WHERE criteria so that you put the most restrictive stuff first. Sometimes, you doing it will actually work better than relying on the optimizer to do it.
THAT SAID...
In the example you give, the optimizer will absolutely be able to figure it all out. After that, everything @ratbak said.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2022 at 4:31 pm
To add to what ratbak and Grant have stated, the other thing that paying attention to the order of the criteria in the WHERE helps with troubleshooting. Yeah... I know that sounds strange but it helps others more easily understand the code as a whole.
Combining that a bit with what Grant said, I normally put the order of the tables in the FROM clause as those that return the smallest result sets first and then the criteria in the WHERE in the same order. There are always exceptions to every such "general" rule but that's what I normally do.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply