Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Poor performance of parameterised NOT IN clause - SQL 2008 Expand / Collapse
Author
Message
Posted Monday, December 16, 2013 2:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 4:22 AM
Points: 2, Visits: 62
I'm comparing the logical reads done by two slightly differently phrased parameterised SQL statements that return the same results from an SAP database table :
DECLARE @P1 varchar(3) = '400';
DECLARE @P2 varchar(2)= '30';
DECLARE @P3 varchar(2) ='60';

SELECT "COUNTER" AS c ,"REFCOUNTER" AS c ,"STATUS" AS c
FROM "CATSDB"
WHERE "MANDT" = @P1 AND NOT ("STATUS" = @P2 OR STATUS = @P3 )
ORDER BY "COUNTER"


In my case the query stats say:
Scan count 2, logical reads 22084

but when I run :
DECLARE @P1 varchar(3) = '400';
DECLARE @P2 varchar(2)= '30';
DECLARE @P3 varchar(2) ='60';

SELECT "COUNTER" AS c ,"REFCOUNTER" AS c ,"STATUS" AS c
FROM qe7."CATSDB"
WHERE "MANDT" = @P1 AND NOT "STATUS" IN (@P2, @P3)
ORDER BY "COUNTER"


the query stats say :
Scan count 2, logical reads 113454
The CATSDB table has an index consisting of MANDT, STATUS, REFCOUNTER.
Why do these two statements do such different numbers of logical reads and take such different times ?
Thanks.
Post #1523426
Posted Tuesday, December 17, 2013 4:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:08 AM
Points: 1,380, Visits: 2,703
So, in the second query, all you're doing is using the schema_name.table_name?

What do the execution plans say?
Post #1523606
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse