Poor performance of parameterised NOT IN clause - SQL 2008

• Andrew Greig

  Grasshopper

  Points: 10

  More actions

  December 16, 2013 at 2:06 pm

  #302457

  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 :

  [font="Courier New"]DECLARE @P1varchar(3) = '400';

  DECLARE @P2varchar(2)= '30';

  DECLARE @P3varchar(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"[/font]

  In my case the query stats say:

  Scan count 2, logical reads 22084

  but when I run :

  [font="Courier New"]DECLARE @P1varchar(3) = '400';

  DECLARE @P2varchar(2)= '30';

  DECLARE @P3varchar(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"[/font]

  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.

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  December 17, 2013 at 4:48 am

  #1674003

  So, in the second query, all you're doing is using the schema_name.table_name?

  What do the execution plans say?