Wildcard % % Performance

• alic

  Valued Member

  Points: 62

  More actions

  November 7, 2020 at 6:45 pm

  #3805523

  Hi

  I have a legacy application that uses a like %searchvalue% condition.

  I have looked at using a computed column with reverse(column_like_is_used_on) which also has an index on the computed column.

  The sql is then “select column from table where reverse_column like reverse(‘%searchstring’)

  Which means the like is now “where reverse_column like ‘gnirtshcraes%’

  This will use an index seek but the problem is for it to work I need to use substring or a left to get the rest of the start of the string for it to work and I get an index scan.

  Really I want to get a fully qualified vale from the legacy app to use in the sql but it’s difficult to change it so any advice is appreciated thanks

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  November 8, 2020 at 7:10 pm

  #3805732

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• Jeff Moden

  SSC Guru

  Points: 1004592

  More actions

  November 8, 2020 at 10:39 pm

  #3805756

  alic wrote:

  This will use an index seek but the problem is for it to work I need to use substring or a left to get the rest of the start of the string for it to work and I get an index scan.

  I guess I'm not understanding why you think you'd need to do that.  Please post the code you're actually using.  A table definition would help, as well.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)