Performance Tuning- Avoid applying functions directly to columns in WHERE, JOIN-ON clauses(Day 5)

Speaker: Rajasekhar Reddy Bolla, +91 9966246368 (whatsapp) Avoiding functions in the WHERE clause of SQL Server queries is important for ensuring optimal performance. Using functions in WHERE clauses can lead to non-SARGable queries, meaning SQL Server cannot take full advantage of indexes, resulting in slower execution. Why Avoid Functions in the WHERE Clause? 1. Prevents Index Usage: When you use functions, SQL Server applies the function to every row, making it unable to use indexes efficiently. 2. Increases CPU Load: Applying functions repeatedly on large datasets increases resource consumption. 3. Full Table/Index Scans: Functions may force SQL Server to scan the entire table or index rather than seeking specific rows. ________________________________________ Examples of Problematic Queries and Alternatives: Here are practical examples using the AdventureWorks database to demonstrate the impact of avoiding functions in the WHERE clause and writing more efficient queries: ________________________________________ 1. Avoiding Date Functions Problematic Query: USE AdventureWorks2019; GO SELECT SalesOrderID,OrderDate FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = 2014; • Why it's inefficient: The YEAR function is applied to every row in the OrderDate column, preventing index usage. Optimized Query: SELECT SalesOrderID,OrderDate FROM Sales.SalesOrderHeader WHERE OrderDate gt= '2014-01-01' AND OrderDate lt '2015-01-01'; CREATE INDEX IX_SalesOrderHeader_OrderDate ON Sales.SalesOrderHeader(OrderDate); --Drop INDEX IX_SalesOrderHeader_OrderDate ON Sales.SalesOrderHeader • Why it's better: It directly compares the column value with a range, allowing the use of an index on OrderDate. ________________________________________ 2. Avoiding String Functions Problematic Query: SELECT BusinessEntityID,FirstName+' '+LastName FROM Person.Person WHERE LEFT(LastName, 1) = 'S'; • Why it's inefficient: The LEFT function forces a table or index scan because it must evaluate the function for every row. Optimized Query: SELECT BusinessEntityID,FirstName+' '+LastName FROM Person.Person WHERE LastName LIKE 'S%'; • Why it's better: The LIKE clause can leverage an index on LastName for efficient retrieval. ________________________________________ 3. Avoiding Mathematical Functions Problematic Query: SELECT ProductID,ListPrice FROM Production.Product WHERE ListPrice * 0.9 gt 100; • Why it's inefficient: The calculation ListPrice * 0.9 is applied to every row. Optimized Query: SELECT ProductID,ListPrice FROM Production.Product WHERE ListPrice gt 100 / 0.9; • Why it's better: By rewriting the query, the comparison avoids using a function, enabling index usage. ________________________________________ 4. Avoiding Case Conversion Functions Problematic Query: SELECT * FROM Person.Person WHERE UPPER(FirstName) = 'JOHN'; • Why it's inefficient: The UPPER function is applied to each row, making it non-SARGable. Optimized Query: SELECT * FROM Person.Person WHERE FirstName = 'John'; • Alternative: To handle case-insensitive searches, store names in a consistent case or use collations: SELECT * FROM Person.Person WHERE FirstName COLLATE SQL_Latin1_General_CP1_CI_AS = 'JOHN'; ________________________________________ Key Takeaways: 1. Avoid applying functions directly to columns in WHERE, JOIN, or ON clauses. 2. Use computed columns and indexes where functions are unavoidable. 3. Rewrite queries to compare raw column values or ranges. 4. Leverage SQL Server's indexing capabilities for better performance.