-1

I am working on some legacy SQL code, and am looking for some help trying to fix an odd query I have. It looks like this:

SELECT * 
FROM   TA LEFT OUTER JOIN TB ON TA.a1 = TB.b3
WHERE  TA.a1 LIKE 'usersearch'
    OR TB.b1 + ':' + TB.b2 LIKE 'usersearch'

usersearch is a user supplied regex that is unknown at the time of the query's creation.

The usersearch variable is the same in both LIKE sections. This is an insane bit of code, and takes forever to run, but I'm having a hard time figuring out how I can optimize it.

The terrible part is that it constructs a string to perform a regex on for every single line. However, I'm not really sure how to avoid this.

If anyone has any ideas I'd love to hear them!

caffein
  • 575
  • 8
  • 26
  • First remove LIKE and replace with `=`. – forpas May 30 '19 at 17:00
  • Second, how do you expect `TB.b1 + ':' + TB.b2 LIKE 'usersearch'` to match (be equal) `'usersearch'`? So all this: `OR TB.b1 + ':' + TB.b2 LIKE 'usersearch'` is useless. – forpas May 30 '19 at 17:02
  • So, an example might be: TB.b1 = 'foo' , TB.b2 = 'bars' and usersearch = '%foo:bar%'. So in all it would be 'foo:bars' LIKE '%foo:bar%' . – caffein May 30 '19 at 17:06
  • `'usersearch'` is not a variable, it's a string literal. How do you expect to get help if you don't post the correct code? – forpas May 30 '19 at 17:07
  • I think you need to explain what you expect that the query do. For example: search if the 'usersearch' is present in both tables or both have some row that contains 'usersearch' – filipe May 30 '19 at 17:07
  • Ah, sorry, usersearch is an unknown regex supplied by the user. I have no idea what this will be when the query is built. Essentially the usersearch value could be described by either TA.a1 or TB.b1 + ':' + TB.b2 . So, I want to get all of the information about them where one of those matches. For exampe: if the user search is '%foo%' that could match TA.a1 = 'foo', but TA.a1 might not have that value, in which case we should check if TB.b1 + ':' TB.b2 matches it. – caffein May 30 '19 at 17:19

1 Answers1

1

Your where condition: TB.b1 + ':' + TB.b2 LIKE 'usersearch' is non-sargable and will always result in a table scan. You might want to consider making a computed column that is persisted so that you can index it. That should improve performance. However, your question indicated that 'usersearch' is a RegEx. LIKE does not work with RegEx. It does work with the % and _ wildcards. I'm hoping that this was just a terminology mistake. If it really is a RegEx, then you'll need a very different solution. Regardless, the sargable issue still needs to be resolved.

DeadZone
  • 1,633
  • 1
  • 17
  • 32
  • Thanks DeadZone, I was contemplating doing something along those lines. The non-sargable info was really helpful as well. Also, you are correct about the regex vs like part. The input would only have % and _ wildcards, so it was just a terminology mistake. Thanks again! – caffein May 30 '19 at 23:46