IS NULL in WHERE clause causing query to run slow

Question

I have a query that runs quickly without the is null parameter in the Where clause, but when I add it back in it takes more than 10 minutes to run.

SELECT
    Parent_FRID, Name, lvl2desc, capyr, crpprjamt, empprjamt,
    othprjamt AS Other_Projected, Fcrprepamt, Femprepamt, Fothrepamt, NFcrprepamt,
    NFemprepamt, NFothrepamt, crpamt, empamt, othamt, Audited, Previous_Corp_Amt,
    Previous_Emp_Amt, Previous_Total_Projected, Previous_Total_Reported,
    Previous_Audited, crpfnl, empfnl, othfnl
FROM
    Camp_Sum_6_Current_14
WHERE
    cnttyp IS NULL AND
    lvl2 <> '1020' AND
    doncls in ('AG','CO')

I have tried everything and I can't get it figured out. I tried a subquery. I tried changing the Null values to 'None' in the original table. The same query runs fine with is not null.

the problem is not because you are using `IS NULL` or not, but because there's a new filter added, one that probably forces to perform a scan on your table. Are there any indexes on the `cnttyp` column? — Lamak, Oct 30 '14 at 17:42
Search the phrase "sargable". http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable — EBarr, Oct 30 '14 at 17:46

Olivier Jacot-Descombes · Answer 1 · 2014-10-30T17:54:06.517

If you put an index on doncls, this will allow SQL-Server to filter the "AG" and "AC" very quickly. Then the other conditions will run on a very reduced row set. (Assuming that only a small percentage of the rows have "AG" or "AC".)

An index on lvl2 will not help much, because the condition on this column is formulated as an exclusion.

Depending on how many NULLs are in cnttyp an index can help there too.

Indexes only help, if the conditions using them are very selective. I.e. if and index helps to exclude 95% of the rows, it is useful. If it excludes only 50% of the rows, a table scan or index scan will probably be faster.

score 0 · Answer 2 · edited Aug 27 '20 at 09:13

0

try subquery

select   
 
    Parent_FRID, Name, lvl2desc, capyr, crpprjamt, empprjamt,
    othprjamt AS Other_Projected, Fcrprepamt, Femprepamt, Fothrepamt, NFcrprepamt,
    NFemprepamt, NFothrepamt, crpamt, empamt, othamt, Audited, Previous_Corp_Amt,
    Previous_Emp_Amt, Previous_Total_Projected, Previous_Total_Reported,
    Previous_Audited, crpfnl, empfnl, othfnl
from 

    Camp_Sum_6_Current_14

WHERE

    lvl2 <> '1020' AND
    doncls in ('AG','CO') AND
    Parent_FRID in

(SELECT
    Parent_FRID
FROM
    Camp_Sum_6_Current_14
WHERE
    cnttyp IS NULL )

edited Aug 27 '20 at 09:13

Thorsten Kettner

89,309
7
49
73

answered Oct 30 '14 at 18:05

Behnam

1,039
2
14
39

"ID" usually refers to Primary key , so i assumed that it is realy unique. – Behnam Oct 30 '14 at 18:50
. . Undoubtedly, it is unique in *some* table. I see no reason why that table would be called `Camp_Sum_6_Current_14`. – Gordon Linoff Oct 30 '14 at 21:52

score 0 · Answer 3 · edited Nov 01 '14 at 20:09

try use cte

with xyz as (SELECT
    Parent_FRID, Name, lvl2desc, capyr, crpprjamt, empprjamt,
    othprjamt AS Other_Projected, Fcrprepamt, Femprepamt, Fothrepamt, NFcrprepamt,
    NFemprepamt, NFothrepamt, crpamt, empamt, othamt, Audited, Previous_Corp_Amt,
    Previous_Emp_Amt, Previous_Total_Projected, Previous_Total_Reported,
    Previous_Audited, crpfnl, empfnl, othfnl FROM
    Camp_Sum_6_Current_14)

select *  from xyz WHERE
    cnttyp IS NULL AND
    lvl2 <> '1020' AND
    doncls in ('AG','CO')

score -2 · Answer 4 · edited Aug 27 '20 at 10:40

-2

WITH DATA AS 
(
SELECT
    Parent_FRID, Name, lvl2desc, capyr, crpprjamt, empprjamt,
    othprjamt AS Other_Projected, Fcrprepamt, Femprepamt, Fothrepamt, NFcrprepamt,
    NFemprepamt, NFothrepamt, crpamt, empamt, othamt, Audited, Previous_Corp_Amt,
    Previous_Emp_Amt, Previous_Total_Projected, Previous_Total_Reported,
    Previous_Audited, crpfnl, empfnl, othfnl, ISNULL(cnttyp,0) AS NULLCHECK
FROM
    Camp_Sum_6_Current_14
WHERE
        lvl2 <> '1020' AND
    doncls in ('AG','CO')
)
SELECT * FROM DATA
WHERE NULLCHECK = 0

edited Aug 27 '20 at 10:40

Shubham Srivastava

1,807
1
10
17

answered Aug 27 '20 at 08:46

Joe Pilgrim

1
1

2

This request is six years old. And when writing an answer, then please write an answer and don't only post some code. What do you think is the issue with the original query? How does your query address this issue? Why do you think that this will work better? – Thorsten Kettner Aug 27 '20 at 09:15

IS NULL in WHERE clause causing query to run slow

4 Answers4