Quick Tip: Remove CONVERTCAST from your WHEREJOIN clauses

Quick Tip

Remove CONVERT/CAST from your WHERE clauses and JOINS when comparing to variables of different data types. Set their data types to match your table definitions before using them as a filter. Optimizing your queries this way will greatly reduce the amount of CPU time, reads, and I/O generated in your queries and allow your code to take better advantage of indexes.

Example

We are going to create a very simple stored procedure called ConvertExample. In this procedure we will see two things. One, the first procedure we create will declare two variables as VARCHAR( MAX) data types, then in the WHERE clause it will convert a table column called Modified Date and compare the variables to that value. You’ll note using SET STATISTICS IO, TIME ON, this code takes considerably more CPU time compared to our second example that will CONVERT the variables first then compare it to a field without having to CONVERT in the WHERE clause.

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

Now, turn on Actual Execution Plans and execute the procedure.

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

Query Plan and Statistics IO,TIME results

This code generates a warning on our SELECT and generates 219ms CPU time. Also note estimated number of rows 10,918.

Now let’s rewrite the code by setting the variables to match the datatype of the field we want to filter on, Modified Date, which is a datetime.

--------------------------------------------------------------------------
--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 CONVERTSCASTS 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.

The post Quick Tip: Remove CONVERTCAST from your WHEREJOIN clauses appeared first on A Shot of SQLEspresso.