Quick Tip - Remove CONVERTS/CASTS From WHERE/JOIN Clauses

USE [AdventureWorks2017]
GO
CREATE OR ALTER PROCEDURE [dbo].[ConvertExample]
(
       @fromDate AS VARCHAR(MAX),
       @toDate AS VARCHAR(MAX)
)
AS
BEGIN
SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2017].[Sales].[SalesOrderDetail]
  WHERE CONVERT(varchar(8), [ModifiedDate],112) BETWEEN @fromDate and @toDate
END
SET STATISTICS IO,TIME ON
GO

Execute dbo.[ConvertExample] '20110501','20110531'

--------------------------------------------------------------------------
--REWRITE Convert Variables Instead, and REMOVE CONVERT from WHERE Clause
-----------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE [dbo].[ConvertExample]
(
       @fromDate AS VARCHAR(MAX),
       @toDate AS VARCHAR(MAX)
)
AS
BEGIN
SET @fromDate= CONVERT(dateTime, @fromDate)
SET @toDate= CONVERT(dateTime, @toDate)
SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2017].[Sales].[SalesOrderDetail]
  WHERE [ModifiedDate] BETWEEN @fromDate and @toDate
END
-----------------------------------------------------------------------------
--RERUN The Proc
-----------------------------------------------------------------------------
Execute dbo.[ConvertExample] '20110501','20110531'

Query Plan and Statistics IO, TIME results for the second version.

Note the large difference in CPU time, it drops from 219ms to 15ms. You’ll also note the type conversion warning is gone from our SELECT statement in the plan and the estimated number of rows is now 356 instead of 10918. Lastly, we also now have a missing index warning—with the conversion in place the query optimizer was unable to identity the missing index.

Now for fun let’s add an index on Modified date using the code below. Then rerun the old procedure and compare it to the new procedure. You may be surprised on what you see.

USE [AdventureWorks2017]
GO
CREATE NONCLUSTERED INDEX [IDX_SalesOrderDetail_Modifed Date] ON [Sales].[SalesOrderDetail]
(
       [ModifiedDate] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
GO

Now, Recompile the first version of the procedure, then Execute and look at the plan. Then Recompile the 2nd version and Execute and compare

Here is the original run. Note it uses a Clustered Index Scan and bypasses our index.

Now let’s look at the new one. You’ll see that by removing the CONVERT for every row it compares, it now can take advantage of the index we created. You’ll also note there is now a Key Lookup, take a look at this blog to find out how you can further optimize this query by getting rid of the Key Lookup.

Summary

This was just a quick tip and reminder that whenever possible you should remove CONVERTS\CASTS from your WHERE clauses and set the variables to proper data types instead. This same logic applies to JOINS. Do this not only to reduce the CPU time, I/O and reads, but to also take advantage of your indexes.