>SQL Server: Why to Avoid TRIM functions in WHERE and JOIN clauses


>

Just creating indexes on JOIN, WHERE and GROUP clause columns doesn’t mean that your query will always return your required results quickly. It is query optimizer which selects proper index for a query to give you an optimum performance but query optimizer can only suggest optimum query plan by using proper indexes WHEN your are helping it by writing good query syntax.
Using any type of function (system or user defined) in WHERE or JOIN clause can dramatically decrease query performance because this practice create hurdles in query optimizer work of proper index selection. One common example is TRIM functions, which are commonly used by developers in WHERE clause.  For more understandings, let’s compare performance of two queries, one with TRIM function in WHERE clause and other one without TRIM functions.
USE AdventureWorks
GO
SELECT pr.ProductID,pr.Name,pr.ProductNumber,wo.*  fROM Production.WorkOrder wo
INNER JOIN Production.Product pr
ON PR.ProductID = wo.ProductID
WHERE LTRIM(RTRIM(pr.name)) = ‘HL Mountain Handlebars’

GO
SELECT pr.ProductID,pr.Name,pr.ProductNumber,wo.*  fROM Production.WorkOrder wo
INNER JOIN Production.Product pr
ON PR.ProductID = wo.ProductID
WHERE pr.name = ‘HL Mountain Handlebars’
Though outputs of both queries are same but first query took almost 99% of total execution time. This huge difference is just because of these trim functions so on production databases we must avoid these TRIM and other functions in both JOIN and WHERE clauses.
Advertisements

Posted on May 3, 2011, in Index, Performance Tuning, Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 4 Comments.

  1. >Nice article , you have indeed covered topic in details with sample query and nice explanation. query optimization is long walk to me and these kind of small tips which really mean a lot while getting a faster query response. hope to see moreJavin 10 tips on working fast in Unix Commands

  2. >You should clarify that the problem is using ANY function or operation (not just trim) on the "database" side of the comparation.For example, the following line won't cause any problem:WHERE pr.Name = RTRIM(LTRIM(' HL Mountain Handlebars ' )

  3. is this applicable to Oracle Database as well?

  4. Is there any difference in the output if we use the below condition:LTRIM(RTRIM(example1.name)) = LTRIM(RTRIM(example2.name))instead of WHERE LTRIM(RTRIM(pr.name)) = 'HL Mountain Handlebars'Note: the column 'name' in exapmle1 is of 'char' datatype whereas the column 'name in exapmle2 is of 'varchar' datatype.

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

%d bloggers like this: