It’s fairly well-known that functions should be avoided in T-SQL WHERE clauses, so when I recently spotted one running long on our server and delaying our overnight processing, I knew it could be easily fixed. Here’s how.
The code was long and complex, but in this case, only a couple lines of code needed to be updated to fix the query. In the portion of code I was working with, a column that contained the same data was defined differently in two different tables.
In Table_1, sku1 was defined as a varchar(26), but in Table_2, it was defined as a nchar(26). The sql code was loading some of the data from table1 into a cursor variable, which was also defined as a varchar(26). If there was a value in the sku1 cursor variable, some other attributes were queried by joining table2 to table3, trimming both the column value in table2 and the declared cursor variable. Here’s what the code looked like:
if @cursor_sku is not null and len(@cursor_sku) > 0
select A.sku_attributes from Attribute_Table A left outer join Table_2 T2 on T2.sku_id=A.sku_id where ltrim(rtrim(T2.sku2)) = ltrim(rtrim(@cursor_sku))
The ltrim and rtrim functions in the WHERE clause require that every row in the table is scanned to trim extra spaces before executing the join, so to get the attributes for a single row, the query is performing more than 4,000 logical reads per execution. Average run time is 931 ms per execution.
To fix this, change the cursor variable to match the data definition in Table_2:
declare @cursor_sku nchar(26);
Change the select statement that’s retrieving data to load the cursor to:
cast(sku1 as nchar(26))
Since we’re testing the length of the sku, we should trim before evaluating the length:
if @cursor_sku is not null and len(ltrim(rtrim(@cursor_sku))) > 0
Finally, remove the trim statements from the where clause:
where T2.sku2 = @cursor_sku
The query now does two index seeks, each of which returns 1 row. The query does 6 logical reads and executes in 1 ms.