Tuesday, May 3, 2011

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.

10 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 more

    Javin
    10 tips on working fast in Unix Commands

    ReplyDelete
  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 ' )

    ReplyDelete
  3. is this applicable to Oracle Database as well?

    ReplyDelete
  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.

    ReplyDelete
  5. What is work around to get TRIMMED data?

    ReplyDelete
  6. Is It a same case in Mysql database as well?

    ReplyDelete
    Replies
    1. Yes its exactly same in MySQL as well.

      Delete
  7. what's the alternative ? i have to use ltrim to remove the empty in the begining of the world

    ReplyDelete
    Replies
    1. In production we try our best to remove extra spaces at the time of record insertion/update, mostly performed on frontend and never allow users to add extra spaces.

      Delete
  8. I have a varchar field, for which I have to do string matching. Without RTRIM, how else can I do it? Does RTRIM hav ebad performance even on Informix Database? In case of poor performance, what is the alternative?

    ReplyDelete

All suggestions are welcome