0

This is my query with OR LIKE in the where clause. 

SELECT DISTINCT TOP 10 coalesce(cl2desc, hmdesc, gendesc, procdesc) as description,acctcode,uacs FROM 
hpatchrg as t4 INNER JOIN 
hcharge AS t5 ON t4.chargcode = t5.chrgcode LEFT JOIN 
hmisc AS t6 ON t6.hmcode = t4.itemcode AND t5.chrgtable = 'MISCE' LEFT JOIN 
hdmhdr AS t7 ON (t7.dmdcomb +''+ convert(varchar, t7.dmdctr)) = t4.itemcode AND t5.chrgtable = 'DRUGS' LEFT JOIN 
hdruggrp AS t8 ON t7.grpcode = t8.grpcode LEFT JOIN 
hgen AS t9 ON t9.gencode = t8.gencode LEFT JOIN 
hprocm AS t10 ON t4.itemcode = t10.proccode AND t5.chrgtable = 'EXAMI' LEFT JOIN 
hclass2 AS t11 ON t4.itemcode = t11.cl2comb AND t5.chrgtable = 'NONDR' 


WHERE 
(cl2desc LIKE '%cbc%') OR 
(hmdesc LIKE '%cbc%') OR
(gendesc LIKE '%cbc%') OR
(procdesc LIKE '%cbc%')

This takes forever to return anything but when I do:

SELECT DISTINCT TOP 10 coalesce(cl2desc, hmdesc, gendesc, procdesc) as description,acctcode,uacs FROM 
hpatchrg as t4 INNER JOIN 
hcharge AS t5 ON t4.chargcode = t5.chrgcode LEFT JOIN 
hmisc AS t6 ON t6.hmcode = t4.itemcode AND t5.chrgtable = 'MISCE' LEFT JOIN 
hdmhdr AS t7 ON (t7.dmdcomb +''+ convert(varchar, t7.dmdctr)) = t4.itemcode AND t5.chrgtable = 'DRUGS' LEFT JOIN 
hdruggrp AS t8 ON t7.grpcode = t8.grpcode LEFT JOIN 
hgen AS t9 ON t9.gencode = t8.gencode LEFT JOIN 
hprocm AS t10 ON t4.itemcode = t10.proccode AND t5.chrgtable = 'EXAMI' LEFT JOIN 
hclass2 AS t11 ON t4.itemcode = t11.cl2comb AND t5.chrgtable = 'NONDR' 


WHERE 
(cl2desc LIKE '%cbc%')

also tried changing the (cl2desc LIKE '%cbc%') with other value

I get result very fast.

So i assume that the problem is with the OR LIKE in the where clause

What is the correct way to do OR with LIKE in the where clause

Martin
  • 365
  • 4
  • 7
  • 22
  • 1
    So what's your question? – kaineub Aug 08 '17 at 03:21
  • @kaineub i added the question. Sorry to forget im still trying to figure out why it takes long for this to get result – Martin Aug 08 '17 at 03:22
  • Seeing your SQL, there is no issue with extra time. The combination of LIKE and OR in that much quantity is resource killer. The best shot you can give is to introduce more AND conditions to get a subset of the result. And then apply all your OR LIKE on that subset. Or probably design better Indexes.? – ViKiNG Aug 08 '17 at 03:25
  • Depending upon the size of your table(s), a `LIKE` search will be slow. There's no surprise in it. See if you can avoid `LIKE`. Alternately check if you have implemented indexes on the columns that require searching. – dotNET Aug 08 '17 at 03:26
  • the database is not created by me and I dont have control over it. LIKE is used for auto complete for searching. Adding TOP 10 or limiting number of result will not help fasten the search? @ViKiNG @ dotNet. – Martin Aug 08 '17 at 03:28
  • Keyword to lookup ... sargable – S3S Aug 08 '17 at 03:28
  • @scsimon found [this](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) and i used like with `'cbc%'` single `%` the search was a bit faster but there was no explanation offer can you enlighten me here? – Martin Aug 08 '17 at 03:35
  • If the volume of data is larger, the query will take time to execute because you have multiple sorting filters (multiple like conditions), it takes lesser time with one LIKE condition because it has to filter the data once, you keep adding the conditions, keep adding the execution time it requires. – Shahzaib Aug 08 '17 at 03:48
  • Doing a `LIKE '%...%'` will do a table-scan on the tables involved and look at every row, it cannot use an index for this particular part. Removing the first percentage means you now do a "starts with" type of query, which *can* be done using an index. – Lasse V. Karlsen Aug 08 '17 at 12:26
  • @LasseV.Karlsen how to do the index – Martin Aug 09 '17 at 03:16

1 Answers1

1

Too long for comment.

Well, for each column you have In the where clause it has to do a match on every single row. So take sum if the number of rows for each table in the where clause... that's how many times it has to check your logic. Indexes can't be used unless you do a full text index and use something like CONTAINS. I'd expect this query to be slow.

Only using a trailing % means you are searching for where the column value STARTS with the value you supplied. Thus, the engine doesn't have to check all possible locations in a column.

Also using TOP with out an ORDER BY doesn't guarantee the result set returned will be the same each time. So, not using an ORDER BY means you don't care which 10 rows or whatever number is returned.

S3S
  • 24,809
  • 5
  • 26
  • 45
  • i see for example searching for `cbc` with %CBC% will return data like cbc,cbccbc,qwertycbc while `CBC%` will return cbc,cbccbc ? right? – Martin Aug 08 '17 at 03:55
  • Correct. The first one has a preceding wild card – S3S Aug 08 '17 at 11:09