Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Toronto,Canada
    Posts
    27

    Unanswered: ltrim on indexed column

    Hello,

    I am using an indexed column(LastName) in my select. I realised there are some names that has a space before them. I applied ltrim to get rid off them. This (applying ltrim on indexed col) has slowed my query considerably. I used index hint as well. The showplan shows that the query is using the index on this table. Any suggestions how to improve the quey performance ? Offcourse, Cleaning up the data is an option. I am considering to use two selects (with temp table),one with rows where i dont have to trim and other select to retrive rows taht has lastnames with space in front

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Any function applied in the left side of a where clause avoid the use of the index in this specific column.

    According with an index in name
    Code:
    select * from persons
    where name like "A%"
    name like "A%" is a SARG => use the index

    According with an index in name
    Code:
    select * from persons
    where ltrim(name) like "A%"
    ltrim(name) like "A%" is not a SARG => don't use this index

  3. #3
    Join Date
    Feb 2004
    Posts
    28

    Re: ltrim on indexed column

    Originally posted by chrisP
    Hello,

    I am using an indexed column(LastName) in my select. I realised there are some names that has a space before them. I applied ltrim to get rid off them. This (applying ltrim on indexed col) has slowed my query considerably. I used index hint as well. The showplan shows that the query is using the index on this table. Any suggestions how to improve the quey performance ? Offcourse, Cleaning up the data is an option. I am considering to use two selects (with temp table),one with rows where i dont have to trim and other select to retrive rows taht has lastnames with space in front
    I suggest you clean up the data. Also, fix any application that is inserting data with spaces in begining. Use ltrim when inserting the data.

    I am afraid your solution of using a temp table is not the right approach.

    j

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •