How can I improve query performance

Question

I have a piece of query that spinning forever. I am new in DBA stuff. I would like to know what would be the first thing to deal with such problems? I looked at the Execution Plan The table has only 1 non clustered index on ClassCode. The reason I am using so many ClassCode because in SSRS report parameter @ClassCode by default will be multiple values, which is over 200 of them.

SELECT  PolicyNumber 
FROM    tblClassCodesPlazaCommercial 
WHERE   PolicyNumber NOT IN (
                            SELECT  PolicyNumber 
                            FROM    tblClassCodesPlazaCommercial 
                            WHERE   ClassCode IN    (
                                                    33489,31439,41894,68189,01239,01199,68528,67139,68128,739889,
                                                    33428,5561,68428,01484,5281,67227,01184,50199,23528,33283,03228,
                                                    50499,41594,50427,5181,31484,03199,6481,68239,50439,68489,36127,    
                                                    50472,67128,23149,33439,03149,5452,23189,50228,01428,69183,50527,
                                                    67499,739189,50159,31183,33499,01283,33128,50239,6451,33159,
                                                    21199,67272,67127,69427,5451,23239,67199,67449,67189,01599,40228,
                                                    50184,5551,33299,7398,40179,40128,50139,7381,33199,50497,23428,33129,
                                                    738299,67149,40184,23128,69199,68499,50299,31449,40497,68169,67197,
                                                    5191,67259,5252,03489,67459,21299,5262,01181,03428,31483,68183,68228,
                                                    31199,40484,738199,03499,31499,40189,7382,67439,21527,50449,01427,
                                                    68199,5453,50528,36228,50259,68299,50227,23459,33528,40199,40427,
                                                    21289,42594,5283,34489,5251,21228,50197
                                                    ) 
                            )

Pரதீப் · Accepted Answer · 2016-11-18T18:16:39.630

3

Use conditional aggregate to do this

SELECT  PolicyNumber 
FROM    tblClassCodesPlazaCommercial 
group by PolicyNumber
having count(case when ClassCode IN  (33489,31439,41894,..) then 1 end) = 0

Case statement will generate 1 for the ClassCode that is present in the list else NULL will be generated. Now the count aggregate will count of 1 for each PolicyNumber. By setting = 0 we can make sure the PolicyNumber does not have any ClassCode present in given list.

Count can be replaced with

SUM(case when ClassCode IN  (33489,31439,41894,..) then 1 else 0 end) = 0

edited Nov 18 '16 at 18:16

answered Nov 18 '16 at 17:53

Pரதீப்

91,748
19
131
172

Do you need an else 0 in the case? I'd expect values not in the list would be NULL which doesn't = 0. I could be wrong though. Either way, much better idea than OP. – S3S Nov 18 '16 at 17:57
@scsimon - Simple fact is `Count` aggregate will not count `NULL` values. If I add `0` in `else` part then count will consider it as a value and counts it as well then logic will go for toss – Pரதீப் Nov 18 '16 at 18:00
Thanks. There are no `NULL` `ClassCode`. So I should leave it `then 1 end`. Correct? – Serdia Nov 18 '16 at 18:07
Thats awesome. If Possibe, could you please explain the logic behind that? Especially `=0` at the end? Thanks – Serdia Nov 18 '16 at 18:13
@Oleg - Added explanation hope it makes sense. English is not my primary language I struggle a bit for explanations – Pரதீப் Nov 18 '16 at 18:17
1

this is really neat, I haven't seen anyone use having like this. – DForck42 Nov 18 '16 at 21:41

score 0 · Answer 2 · answered Nov 18 '16 at 18:01

First thing to do is add indexes to ClassCode and PolicyNumber. You can add non-clustered indexes to these columns.

After you add indexes, make sure to run it more than once so SQL can build a execution plan for you.

That will definitely help your query.

How can I improve query performance

2 Answers2