SQL Performance – Death by non searchable argument

A common problem in SQL server is non searchable arguments and it is killing the performance big time. What is a non searchable argument then? A non searchable argument is a T-SQL statement that can’t make use of index seeks and is doomed to use index scans with lots of slow IO operations as result. What is causing the T-SQL statement to be non searchable argument? Well, it can be caused by a couple of things:

– No WHERE clause at all is an obvious cause
– Incorrect use of functions
– Implicit conversions
– LIKE ‘%somevalue%’

I don’t think I need to explain the lack of WHERE clause, It is too obvious. In this blog post I’ll focus on the incorrect use of functions, because Incorrect use of functions is far too common and a common reason is the use of datetime where date should have been used. Take a look at the following sample where I try to illustrate the non searchable argument by trying to get all records that are modified a specific day. The ModifiedDate column contains date and time, but I only care about the date part.

CREATE TABLE dbo.A( ID INT IDENTITY(1,1), ModifiedDate DATETIME );
CREATE CLUSTERED INDEX ix_A_modifiedDate ON dbo.A (ModifiedDate);
INSERT INTO dbo.A (ModifiedDate) VALUES(GETDATE()),(GETDATE()+1),(GETDATE()-1);
DECLARE @date DATETIME=CONVERT(date,GETDATE()) --This is equal to 'yyyy-mm-dd 00:00:00.000' ;
SELECT * FROM dbo.A WHERE ModifiedDate=@date; --This will not return any records, but is searchable
SELECT * FROM dbo.A WHERE CONVERT(VARCHAR(10),ModifiedDate,121)=@date; --This will return records, but is non searchable
SELECT * FROM dbo.A WHERE ModifiedDate>=@date AND ModifiedDate<@date+1; --This will return records, and is searchable

The execution plans look almost the same for the searchable and the non searchable argument, with a slight difference, a seek instead of  a scan. As we already know a seek is the fastest access method in most cases.

Execution plan of the searchable argument:

Execution plan of the non searchable argument:

The non searchable argument will work for small datasets, but is a big problem as the number of records in the table increases. Feel free to contact any of our SQL server experts and we will help you with your performance issues.