SQL Server Query Optimization- Effective use of functions in the Where clause

SQL Server Query Optimization- Learn about the use of functions (DateDiff) in the Where clause. Learn how to tune these queries to run faster with an optimized query execution plan.

Indexing the database is extremely helpful to improve the performance of a query. However, it's sometimes because of bad practices that SQL Server does not use an existing index for an optimized query execution plan. Indexes are useless if they aren't used! We might notice that the SQL server cannot use index seek on a column despite creating a proper index. These conditions for using the indexes by SQL Server are known as sargable and non-sargable.

Wikipedia refers to the following definition of sargable and non-sargable queries. The sargable query can use an index for optimizing query performance. However, due to a few non-sargable expressions, SQL Server might not use the optimized query execution plan, adversely affecting performance.

SQL Server DATETIME() functions are useful for performing calculations on date-time values. For example, the DATEDIFF() function is useful for finding the difference between two dates.

Let's run the following query and view its actual execution plan in SSMS.

SELECT [SalesOrderID]
     ,[SalesOrderDetailID]
     ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
     ,[SpecialOfferID]
      ,[UnitPrice]
     ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[ModifiedDate]
  FROM [AdventureWorks2019].[Sales].[SalesOrderDetail]
  where   DATEDIFF(mm,getdate(), ModifiedDate) > 30

The following actual query execution plan shows a non-clustered index scan.

The clustered index scan property shows the following

• Estimated number of executions: 1

• Estimated number of rows of all executions:1

• Estimated number of rows per execution: 1

• Estimated number of rows to read: 121317

SQL Server needs to read all data rows for returning relevant data. Therefore, SQL Server read 121317 rows to return a single row in the output. Imagine if the table is enormous and has millions of rows. Therefore, you need to find ways to covert index scans into index seek with minimal changes.

To covert query into the sargable(effectively using index), we can rewrite to use the expressions towards the right side of the comparison operator. For example, we use the DATEADD function using the Index Seek operator in the execution plan.

the SELECT [SalesOrderID]
     ,[SalesOrderDetailID]
     ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
     ,[SpecialOfferID]
      ,[UnitPrice]
     ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[ModifiedDate]
  FROM [AdventureWorks2019].[Sales].[SalesOrderDetail]
  where  ModifiedDate >  DATEADD(mm, -30, GETDATE())

The index seek (NonClustered) property shows that SQL Server reads only 1 row for returning the result. Therefore, the query executes quickly and does not put any resource pressure on SQL Server.

SQL Server Query Optmization - Computed Columns

Another way to change a non-sargable query into a sargable is to use computed columns. Let's look at the query below that uses the DATEDIFF function on the start date and end date column.

SELECT [BillOfMaterialsID]
      ,[ProductAssemblyID]
      ,[ComponentID]
      ,[StartDate]
      ,[EndDate]
     ,[UnitMeasureCode]
      ,[BOMLevel]
     ,[PerAssemblyQty]
      ,[ModifiedDate]
  FROM [AdventureWorks2019].[Production].[BillOfMaterials]
  where datediff(DD,StartDate,EndDate)>100

It is also using a clustered index scan for retrieving query results.

Let's create a computed column that calculates the date difference similar to what we specified in the where clause.

ALTER TABLE [AdventureWorks2019].[Production].[BillOfMaterials]
  ADD DateDiffStartEndDate AS DATEDIFF(dd, StartDate, EndDate)

We also create an index on the newly created computed column.

CREATE INDEX IX_BillofMaterials
  ON [AdventureWorks2019].[Production].[BillOfMaterials](DateDiffStartEndDate)

If we run the same query, SQL Server uses the Index seek of Clustered Index Scan. You do not need to modify your select statement. SQL Server query optimizer is wise to use the computed column for returning the required result set.

There are several ways for SQL Server Query Optimization. This article explored the computed column or change query to be sargable so that query optimizer can use an index for an optimized execution plan.