0

I've got to filter data in a string column and thus need to compare the column using NOT LIKE with a bunch of strings.

I'm using SQL Server and my code looks like this:

SELECT history.date,
       history.new_value,
       res.resource_name
FROM   item_history history, 
       owned_resource   res
WHERE  (history.attr_name = 'resource_contact' OR history.attr_name = 'Kontakt-UUID')
       AND res.inactive = 0 
       AND history.com_par_id = res.own_resource_uuid
       AND lower(history.new_value) NOT LIKE '%tp für%'
       AND lower(history.new_value) NOT LIKE '%rücklieferung%'
       AND lower(history.new_value) NOT LIKE '%rückläufer%'
       AND lower(history.new_value) NOT LIKE '%stoerreserve%'
       AND lower(history.new_value) NOT LIKE '%zentrallager%'
       AND lower(history.new_value) NOT LIKE '%bhs-pool%'
       AND lower(history.new_value) NOT LIKE '%lager halle%'        
       AND lower(history.new_value) NOT LIKE '%lager logistik%'
       AND lower(history.new_value) NOT LIKE 'reserve %'
       AND lower(history.new_value) NOT LIKE '%igeko%bhs%'
       AND lower(history.new_value) NOT LIKE '%service%ecg%'
       AND lower(history.new_value) NOT LIKE '%multifunktionsdrucker%'
       AND lower(history.new_value) NOT LIKE 'nn%gisa%raum%'
       AND lower(history.new_value) NOT LIKE '%citrix%admins%'
       AND lower(history.new_value) NOT LIKE '%personalwesen%'
       AND lower(history.new_value) NOT LIKE '%etagendrucker%'
       AND lower(history.new_value) NOT LIKE '%schulungsraum%'
       AND lower(history.new_value) NOT LIKE '%team%raum%'
       AND lower(history.new_value) NOT LIKE  '%beratungsraum%'
       AND lower(history.new_value) != 'reserve'
)

I guess the performance is not the best calling "lower()" over and over. Also, as a programmer, my nails are rolling up seeing so much redundant code. Unfortunately I did not find a nice way to use a variable or something. (I want to add that I cannot simply add a new computed column, which would be a good way here, I'm only authorized to read data.)

Any advise to make the code smarter would be appreciable?

  • You seem to be raising at least two very distinct issues here: one is about using lower() and the other about repetition of code. Even though both have to do with the same query, IMHO it would be best to address them separately. This is a Q&A site with the goal to make the questions and answers re-usable, so it's best to make the questions (and, consequently, the answers) as focused as possible, and separating different issues into different questions is one way to do that. – Andriy M Nov 14 '19 at 8:23
  • @Andriy thanks for the hint! I did not consider my question to be of two different issues. – Jana Nov 14 '19 at 9:40
  • Oh well, I just re-checked and the collation setting is Latin1_General_CI_AI. That means it already is case-insensitive, right? Wow, then it's totally redundant to lower the values. – Jana Nov 14 '19 at 9:48
  • Doesn't the collation refer to sorting and not searching? I would personally use a computed column as LOWER(history.new_value). Some of your expressions are not SARGable - maybe a full text index could help? – Vérace Nov 14 '19 at 12:38
  • @Vérace hm, I need to research about this. And jep, computed column could help, but I cannot alter the table. – Jana Nov 14 '19 at 13:37
3

I would use join here instead of old writing way
Regarding "lower" are you sure that this is needed?
Did you check collation of new_value?
If it has CI(case insensitive) in collation name it you can avoid using lower.

If you do, you can write something like this

WITH cte
    AS ( SELECT history.date
               ,history.new_value
               ,res.resource_name
               ,LOWER(history.new_value) AS new_value
         FROM   item_history history
         JOIN   owned_resource res
                ON (   history.attr_name = 'resource_contact'
                       OR history.attr_name = 'Kontakt-UUID' )
                   AND res.inactive = 0
                   AND history.com_par_id = res.own_resource_uuid )
SELECT *
FROM   cte
WHERE  new_value NOT LIKE '%tp für%'
       AND new_value NOT LIKE '%rücklieferung%'
       AND new_value NOT LIKE '%rückläufer%'
       AND new_value NOT LIKE '%stoerreserve%'
       AND new_value NOT LIKE '%zentrallager%'
       AND new_value NOT LIKE '%bhs-pool%'
       AND new_value NOT LIKE '%lager halle%'
       AND new_value NOT LIKE '%lager logistik%'
       AND new_value NOT LIKE 'reserve %'
       AND new_value NOT LIKE '%igeko%bhs%'
       AND new_value NOT LIKE '%service%ecg%'
       AND new_value NOT LIKE '%multifunktionsdrucker%'
       AND new_value NOT LIKE 'nn%gisa%raum%'
       AND new_value NOT LIKE '%citrix%admins%'
       AND new_value NOT LIKE '%personalwesen%'
       AND new_value NOT LIKE '%etagendrucker%'
       AND new_value NOT LIKE '%schulungsraum%'
       AND new_value NOT LIKE '%team%raum%'
       AND new_value NOT LIKE '%beratungsraum%'
       AND new_value != 'reserve';
| improve this answer | |
  • Yep ..suggest _CI collation .. if you need special columns .. make them varbinary – eagle275 Nov 14 '19 at 8:20
  • 1
    Thanks for the hint both of you. I am not able to alter the table settings, but I checked it and the collation is Latin1_General_CI_AI, so it already is caseinsensitive I assume. So fortunately I can omit all the lower() calls. – Jana Nov 14 '19 at 9:50
  • @Michael Cherevko, is there any performance benefit of using join or is it just for readability? – Jana Nov 14 '19 at 9:50
  • I believe that there will be no performance benefit, so a.readability b.because Microsoft suggests so :-) – Michael Cherevko Nov 14 '19 at 9:53
  • 1
    @Michael Cherevko thanks! – Jana Nov 14 '19 at 9:55
1

For the sake of completeness, you could have solved the problem (many excludes) on the following way too:

SELECT history.date
     , history.new_value
     , res.resource_name
  FROM item_history        AS history
 INNER JOIN owned_resource AS res
    ON history.com_par_id = res.own_resource_uuid
   AND res.inactive       = 0
 WHERE (history.attr_name = 'resource_contact' OR history.attr_name = 'Kontakt-UUID')
   AND NOT EXISTS
     (SELECT *
        FROM (VALUES ('%tp für%')
                   , ('%rücklieferung%')
                   , ('%rückläufer%')
                   , ('%stoerreserve%')
                   , ('%zentrallager%')
                   , ('%bhs-pool%')
                   , ('%lager halle%')
                   , ('%lager logistik%')
                   , ('reserve %')
                   , ('%igeko%bhs%')
                   , ('%service%ecg%')
                   , ('%multifunktionsdrucker%')
                   , ('nn%gisa%raum%')
                   , ('%citrix%admins%')
                   , ('%personalwesen%')
                   , ('%etagendrucker%')
                   , ('%schulungsraum%')
                   , ('%team%raum%')
                   , ('%beratungsraum%')
                   , ('reserve')) AS ex (exclude)
       WHERE history.new_value LIKE ex.exclude);

PS: I don't like the old oracle-school and non-ANSI-standard comma style JOINs...

| improve this answer | |
0

Based in my experience, using SQL functions in WHERE conditions is bad for the SQL performance.

You can rewrite your script to do the following:

  1. Create a temp table or variable table
  2. INSERT INTO SELECT to that temp or variable table but in the SELECT use the lower() to the column that you are currently using in WHERE condition.
  3. SELECT [columns] FROM the temp or variable table.
| improve this answer | |
  • sounds like it could work, I did not try it though. Others spotlighted the collation setting of the table and fortunately it already is caseinsensitive... so my issue is solved :-) – Jana Nov 14 '19 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.