OK so the title might not be very specific so I'm sure I'm breaking some rule about how to be a “successful” author… but if I can get a bunch of people on April Fool's Day “Rickrolled” I'm hoping enough people will read on.
In SQL there are so many ways to solve the same problem that sometimes people just pick the first thing that works. While this may be the fastest way to get a solution in place depending on the size and scope of the problem you are trying to solve you may be having to revisit your solution down the road to find a “better way”.
Someone could make a years worth of articles of examples but the one I'm going to touch on today is using a function in a where clause.
OK for this example I'm not going to build my own table cause I don't know enough superhero characters to make a decent sample size so I'm going to be working with the AdventureWorks (SQL 2005) databases.
OK first off let's set our STATISTICS TIME ON as well as our STATISTICS IO ON using the following statement:
SET STATISTICS TIME ON
GO
SET STATISTICS IO ON
GO
OK now let's run the following statement:
SELECT FirstName, LastName, EmailAddress, Phone, ModifiedDate
FROM Person.Contact
WHERE LEFT(EmailAddress, 5) = 'cryst'
GO
So in that statement we're doing a function (LEFT) in our WHERE clause. AdventureWorks isn't the biggest database in the world so the query comes back in a subsecond and our STATISTICS (at least on my test server) look like this:
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
(22 row(s) affected)
Table 'Contact'. Scan count 1, logical reads 220, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 15 ms, elapsed time = 12 ms.
Now let's do the same thing but this time we're NOT going to use a function in the WHERE clause:
SELECT FirstName, LastName, EmailAddress, Phone, ModifiedDate
FROM Person.Contact
WHERE EmailAddress LIKE 'cryst%'
GO
OK subsecond results… as expected but in looking at the STATISTICS we see this:
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
(22 row(s) affected)
Table 'Contact'. Scan count 1, logical reads 56, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
OK so at this point we see that using the LIKE statement and not a function directly against a column in our WHERE clause resulted in less logical reads, less CPU time and had an overall a smaller value for elapsed time. So how could that be?
Let's include the actual exection plan (CTRL-M) and run our two queries again:
SELECT FirstName, LastName, EmailAddress, Phone, ModifiedDate
FROM Person.Contact
WHERE LEFT(EmailAddress, 5) = 'cryst'
GO
SELECT FirstName, LastName, EmailAddress, Phone, ModifiedDate
FROM Person.Contact
WHERE EmailAddress LIKE 'cryst%'
GO
In looking at the execution plan you will see the first query (using LEFT) is doing an index scan and the second query (using LIKE) is doing an index seek and as we know an index seek is preferable as it only pulls back the rows that satisfy the query.
So what's the takeaway from this article… we solved the same problem (finding email addresses that start with the characters “cryst”) 2 different ways and got very different results, not in the result set but in the way SQL Server executed the request.
By not using a function in our WHERE clause we took an index scan (expensive) and turned it into an index seek (more efficient).
Enjoy!!
Good article.
The only thing worse is using a UDF in the where clause *shudder*
Great post, Colin! Thanks for sharing that.
Great article Colin! I’ll certainly come back to checkout your other posts.