Accidental DBA – Query Optimization Tip
One of the simplest ways to improve query performance is to write your SQL in such as way as to reduce or eliminate table or index scans. A scan occurs when the SQL engine must look at every single row in the object being scanned and evaluate the result.
On smaller tables the performance is not quite as bad…but as your tables grow, the performance implications can be enormous.
I recently read a blog, Sargable vs. Non-Sargable, talking about how to create Search Arguments not resulting in a table scan. This blog is a good read worth your time. One of the things left out in the blog is that formulas used to modify data to be evaluated in the where clause result in a scan of an Object (index or the table).
Here is an example of a query that forces an Object Scan:
SELECT Col1, Col2, Col3
FROM SomeTable
WHERE DATEPART(day, SomeTable .RecordDateTime) = 5
Because you are wrapping the column RecordDateTime within a function, “DATEPART”, each row must be evaluated individually; no index provides any benefit at this point. One good way to resolve this particular example is to maintain the DAY portion of the record in a separate column which may be indexed.
Another great technique is to have a Date dimension recording different attributes of a date as possible permutations, and relate the date dimension to SomeTable.
Even if you had an index on RecordDateTime, this would not resolve the problem. The DATEPART function must be performed on every single row before the comparison to the scalar value 5 may be evaluated.
To summarize, anytime you have a function wrapped around a table column that must be resolved for comparison in either a join or a where clause, the performance of your query will be diminished.
This rule is consistent for Microsoft SQL Server. I have no proof, haven’t tested and have not done any research. But, I would predict that this rule would be pretty consistent for any relational database engine. I don’t mean that an engine won’t still perform a table/index scan if you don’t use a function. But I think I am pretty safe to say it is unlikely that another engine would not result in a table/index scan just like SQL Server.
Drop me a note if you find this tip helpful, or have other questions you think would benefit our readers. My email is btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Taking Objective Stock of Your Business
As a business owner, we are constantly faced with changing markets, client requests and resource conflicts. As we deal with the everyday details, we often lose sight of our business goals. It’s an interesting paradox that by only focusing on the details we lose sight of what is really important. I have some solutions.
Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ
SQL supports a concept called all-at-onc… (read more)