Optimize this Query

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  September 20, 2011 at 8:56 am

  #142926

  Can this Query be optimized and if so, how?

  DROP TABLE DBO.AS_TMP_FLAT_WORDS1_LESS_THOU

  SELECT DISTINCT A.EMAILGUID, A.[MESSAGE]

  INTO DBO.AS_TMP_FLAT_WORDS1_LESS_THOU

  FROM DBO.AS_TMP_FLAT_LESS_THOU A,

  DBO.AS_FLAT_WORDLIST1_LESS_THOU B

  WHERE A.[MESSAGE] LIKE '%'+SPACE(1)+B.WORD+SPACE(1)+'%'

  OR A.[MESSAGE] LIKE '%[0-9] FOR $%'

  OR A.[MESSAGE] LIKE '%[0-9]% OFF'

  OR A.[MESSAGE] LIKE '%[$][0-9] OFF%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9] OFF%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9] OFF%'

  OR A.[MESSAGE] LIKE '%[$][0-9].[0-9][0-9] OFF%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9].[0-9][0-9] OFF%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9].[0-9][0-9] OFF%'

  OR A.[MESSAGE] LIKE '%[$][0-9] PER UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9] PER UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9] PER UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9].[0-9][0-9] PER UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9].[0-9][0-9] PER UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9].[0-9][0-9] PER UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9] /UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9] /UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9] /UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9].[0-9][0-9] /UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9].[0-9][0-9] /UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9].[0-9][0-9] /UNIT%'

  OR A.[MESSAGE] LIKE '%[$][0-9] PER ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9] PER ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9] PER ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9].[0-9][0-9] PER ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9].[0-9][0-9] PER ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9].[0-9][0-9] PER ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9] /ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9] /ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9] /ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9].[0-9][0-9] /ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9].[0-9][0-9] /ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9].[0-9][0-9] /ITEM%'

  OR A.[MESSAGE] LIKE '%[$][0-9] PER PIECE%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9] PER PIECE%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9] PER PIECE%'

  OR A.[MESSAGE] LIKE '%[$][0-9].[0-9][0-9] PER PIECE%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9].[0-9][0-9] PER PIECE%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9].[0-9][0-9] PER PIECE%'

  OR A.[MESSAGE] LIKE '%[$][0-9] /PIECE%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9] /PIECE%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9] /PIECE%'

  OR A.[MESSAGE] LIKE '%[$][0-9].[0-9][0-9] /PIECE%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9].[0-9][0-9] /PIECE%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9].[0-9][0-9] /PIECE%'

  GO

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 20, 2011 at 9:07 am

  #1383908

  Very unlikely.

  You can always post the actual execution plan, but I don't see this ever running fast.

  You'll always scan the whole table and worst, make multiple pattern matches on the same, possibly long, string.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  September 20, 2011 at 2:10 pm

  #1384139

  I think you can remove this block:

  OR A.[MESSAGE] LIKE '%[$][0-9] OFF%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9] OFF%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9] OFF%'

  OR A.[MESSAGE] LIKE '%[$][0-9].[0-9][0-9] OFF%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9].[0-9][0-9] OFF%'

  OR A.[MESSAGE] LIKE '%[$][0-9][0-9][0-9].[0-9][0-9] OFF%'

  since all of those conditions are covered by

  OR A.[MESSAGE] LIKE '%[0-9]% OFF'

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  September 21, 2011 at 7:07 am

  #1384468

  I think you are wrong. because if in the data there is :

  20% OFF

  Then it will select that and I don't want that.