0

I have Table1 with 10 VARCHAR(37) columns and an ID. It has 250 lines. I want to get for a specific set of 4 char string the count of the lines in TABLE1 which have at least one of the 10 columns containing this 4 char string.

The result will look like this

str    count
............
T100     4
T010    50
  .     .
  .     .
  .     .
............

My question is not about how to do it, but about the performance

I did it like this

>  DECLARE @Helper TABLE(Str Varchar(4), Count INT DEFAULT 0 )
>  --I will skip here insering those 150  4char strings into @Helper on Str column
>  
>  UPDATE @Helper SET [Count] = SELECT COUNT(ID) FROM Table1 T WHERE
>     CHARINDEX(Str,T.Col1) > 0 OR CHARINDEX(Str,T.Col2) > 0 ...... OR
>     CHARINDEX(Str,T.Col10) > 0
> 
> --where Col1...Col10 are the VARCHAR(37) columns

This takes ~2 seconds. Isn't it too much? My guess is that SQL Server evaluates all the conditions in the WHERE clause, even if only 1 will suffice ( i have only OR). Am i right ? Or how can I get this done under 2 seconds ? I am using SQL server 2008.

Thank you all!

darkdante
  • 707
  • 1
  • 17
  • 36
  • 1
    http://stackoverflow.com/a/11219864/884862 – Louis Ricci Nov 22 '13 at 18:38
  • Thank you for the answer LastCoder. I did search for something similar, but it seems I didn't use the right words (short-circuit). So I was right and there is nothing to to about this :( – darkdante Nov 22 '13 at 18:42
  • FYI: The time that this is taking has very little to do with the CPU overhead of extra tests and everything to do with the fact that a `WHERE` clause like this cannot leverage any indexes, it has to scan the whole table. – RBarryYoung Nov 22 '13 at 18:48
  • 1
    You may be able to short circuit with a CASE expression. `WHERE 1 = CASE WHEN CHARINDEX(Str,T.Col1) > 0 THEN 1 WHEN CHARINDEX(Str,T.Col2) > 0 THEN 1 ... END` - of course you'd want to put the one that is most likely to match first, and also keep in mind that `CASE` can evaluate subqueries twice (you're not using any here) and can skip short circuiting when aggregates are involved (you're also not using any of those). – Aaron Bertrand Nov 22 '13 at 19:21

0 Answers0