Trim Functions in the WHERE clause

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
begin
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))
end

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.

Trim Functions in the WHERE clause

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s