March 13, 2013 at 12:56 pm
Hello - How smart is the SS2008R2 query optimizer? For example, consider the following query:
select top 10 * from ActivityPointer where
Subject like '%new registrant%' and
OwningUser = 'F9841D46-5904-E011-949A-005056BC3615'
A guid search is going to be faster than a text search. And a like search (%%) makes text searches even slower. So is SS2008R2 smart enough to optimize this query before it executes it?
For example, will SS2008R2 determine on its own that OwningUser is a guid search that provides better performance than a text like search so therefore apply the guid search first in order to reduce the number of rows that the text like search is applied to?
March 13, 2013 at 1:50 pm
Oftentimes yes it will make that adjustment. Occasionally it may miss on it because it is checking so many different possible query plans in split second to find a "good enough" plan that is better than the rest in that quick instant.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 13, 2013 at 1:50 pm
Depends on the indexes you have.
If you have no indexes, that's a table scan and SQL will apply both filters as it scans the table. If you have an index on OwningUser, then it will seek on that index, apply the secondary filter and stop reading after finding 10 matching rows. If you only have an index on Subject then it might scan that index, apply secondary filter or it might ignore the index and scan the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2013 at 2:14 pm
Thanks for the feedback. In my first query please ignore the "top 10" in my select statement to keep my question more focused. Thanks for being detail oriented - I should have thought about removing the "top 10" before posting the query.
It sounds like if I'm running ad hoc queries on an unfamiliar database then it makes sense to include the where conditions in a logical order based on the logic in my first post....
March 13, 2013 at 2:25 pm
sqlguy-736318 (3/13/2013)
Thanks for the feedback. In my first query please ignore the "top 10" in my select statement to keep my question more focused.
The TOP has very little to do with what I said, you can just remove the 'and stop reading after finding 10 matching rows' from my post and everything I said will still be true..
It sounds like if I'm running ad hoc queries on an unfamiliar database then it makes sense to include the where conditions in a logical order based on the logic in my first post....
It makes no sense whatsoever to do that, familiar or unfamiliar database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2013 at 2:56 pm
"If you have no indexes, that's a table scan and SQL will apply both filters as it scans the table."
Gail - Correct me if I'm wrong but if I filter by guid first then this will filter the number of rows to be processed by subsequent conditions.
So if I have 1M rows and 5 rows are associated with a given owner guid then the subsequent like text search (more processing intensive) will only have to process those 5 rows that were returned by the first guid filter.
However, if the query is filtered by the like text search first then SQL Server would first apply this filter to 1M rows and then apply the guid filter to the rows returned by the first like text search. Since the more processor intensive condition has to process more rows the performance would not be as good.
So assume I need to run a quick query on an unfamiliar database. Assume the database has no indexes aside from standard PK's/FK's. Isn't there a greater probability that putting the guid filter before the like text filter will process the query more quickly?
Or is SS always smart enough to evaluate the where conditions before execution to determine which condition execution order will process the query the most quickly?
March 13, 2013 at 3:46 pm
sqlguy-736318 (3/13/2013)
"If you have no indexes, that's a table scan and SQL will apply both filters as it scans the table."Gail - Correct me if I'm wrong but if I filter by guid first then this will filter the number of rows to be processed by subsequent conditions.
No.
SQL doesn't scan the table, apply the guid filter then scan the rows that matched the first condition and apply the second filter, repeat until it runs out of rows or conditions.
If it does a table scan, it applies all conditions to each row as it is scanned, if the row matches all the conditions it's returned. If it does not match one or more, the row is not returned.
If you have 1 million rows and 5 rows match the guid and text, then 1 million rows are read, the guid and text compared on each one and the resulting 5 rows returned.
The order that conditions are evaluated depends on the indexes on the table and the optimiser's estimates of which index would be more efficient to use. If you're working on a database that has no indexes other than primary keys (foreign keys don't have indexes unless explicitly created), then you'll be far better off adding useful indexes if you want decent performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply