Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been trying to figure out why this query (on integers) is using an index scan when both values (the original table, and the table its looking up in) are integers.

The first part is used to fill a table with values.

The second part is the search query that I'm battling with. If you run the execution plan on the query, you will see that even though there is an index on the intcol, and the #temp table is of type int, it still uses an index scan and scans all 298 000 rows.

The problem is the query that I'm using, it has where (a = a) or (b = b)

When i take out the "or (b = b)" then it does not do an index scan. But I need the "or (b == b)" to have this complicated query work.

Implicit Conversions that cause Index Scans

    CREATE TABLE dbo.TestImplicitConverts
(
  RowID int NOT NULL IDENTITY (1, 1),
  BigIntCol bigint NOT NULL,
  BitCol bit NOT NULL,
  CharCol char(10) NOT NULL,
  DateTimeCol datetime NOT NULL,
   DecimalCol decimal(10, 2) NOT  NULL , 
  FloatCol float(53) NOT  NULL  ,
  IntCol int NOT NULL,
  MoneyCol money NOT NULL,
  NCharCol nchar(10) NOT NULL,
  NumericCol numeric(10, 2) NOT NULL,
  NVarchrCol nvarchar(50) NOT NULL,
  RealCol real NOT NULL,
  SmallDateTimeCol smalldatetime NOT NULL,
  SmallIntCol smallint NOT NULL,
  SmallMoneyCol smallmoney NOT NULL,
  TinyIntCol tinyint NOT NULL,
  GUIDCol uniqueidentifier NOT NULL,
  VarcharCol varchar(50) NOT NULL,
  CONSTRAINT PK_TestImplicitConverts PRIMARY KEY CLUSTERED (RowID)

 )
GO

-- Create nonclustered indexes on all columns to test implicit conversion affects

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_BigIntCol ON dbo.TestImplicitConverts (BigIntCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_BitCol ON dbo.TestImplicitConverts (BitCol);
GO
CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_CharCol ON dbo.TestImplicitConverts (CharCol);
GO


CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_DateTimeCol ON dbo.TestImplicitConverts (DateTimeCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_DecimalCol ON dbo.TestImplicitConverts (DecimalCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_FloatCol ON dbo.TestImplicitConverts (FloatCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_GUIDCol ON dbo.TestImplicitConverts (GUIDCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_NVarcharCol ON dbo.TestImplicitConverts (NVarchrCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_RealCol ON dbo.TestImplicitConverts (RealCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_SmallDateTimeCol ON dbo.TestImplicitConverts (SmallDateTimeCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_SmallIntCol ON dbo.TestImplicitConverts (SmallIntCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_IntCol ON dbo.TestImplicitConverts (IntCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_MoneyCol ON dbo.TestImplicitConverts (MoneyCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_NCharCol ON dbo.TestImplicitConverts (NCharCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_NumericCol ON dbo.TestImplicitConverts (NumericCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_SmallMoneyCol ON dbo.TestImplicitConverts (SmallMoneyCol);
GO


CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_TinyIntCol ON dbo.TestImplicitConverts (TinyIntCol);
GO

CREATE NONCLUSTERED INDEX IX_TestImplicitConverts_VarcharCol ON dbo.TestImplicitConverts (VarcharCol);
GO



INSERT INTO dbo.TestImplicitConverts
(   BigIntCol, BitCol, CharCol, DateTimeCol, 
    DecimalCol, FloatCol, IntCol, MoneyCol, NCharCol, NumericCol, NVarchrCol, RealCol,
    SmallDateTimeCol, SmallIntCol, SmallMoneyCol,  TinyIntCol, GUIDCol, VarcharCol)
SELECT a.number, a.number%1, CAST(b.name AS CHAR(10)),  DATEADD(ms, -1*a.number, GETDATE()), 
   a.number, a.number, a.number, a.number, CAST(b.name AS NCHAR(10)), a.number, b.name, a.number,
    DATEADD(ms, -1*a.number, GETDATE()), a.number, a.number, a.number%255, NEWID(), b.name

FROM master.dbo.spt_values AS a
CROSS JOIN master.dbo.spt_values AS b
WHERE a.type = N'P'
  AND a.number < 1000
  AND b.name IS NOT NULL;
GO
ALTER INDEX ALL ON TestImplicitConverts REBUILD;
GO

 create table #temp(myint int, my2ndint int)
 insert into #temp(myint, my2ndint)
 select 800,140



while @@rowcount>0
begin
    insert into #temp(myint,my2ndint)
    SELECT intcol, intcol
    FROM    TestImplicitConverts
    WHERE   
       (
       intcol in (select myint from #temp  )
         and 
       intcol not in (select my2ndint from #temp)
       )
    or (
       intcol in (select my2ndint from #temp  )
          and 
       intcol not in (select myint from #temp  )
       )

 end
 drop table #temp
share|improve this question

2 Answers 2

up vote 5 down vote accepted

Your predicates are of the form

WHERE  (A AND NOT B) OR (B AND NOT A)

Testing on SQL Server 2008 with the FORCESEEK hint it cannot produce a plan.

If you rearrange them to

WHERE (A OR B) AND NOT (A AND B)

You can then get a plan with a seek (doesn't need the hint).

SELECT intcol,
       intcol
FROM   TestImplicitConverts
WHERE  ( ( intcol IN (SELECT myint
                      FROM   #temp) )
          OR ( intcol IN (SELECT my2ndint
                          FROM   #temp) ) )
       AND NOT ( intcol IN (SELECT myint
                            FROM   #temp)
                 AND intcol IN (SELECT my2ndint
                                FROM   #temp) )

enter image description here

Or another possibility would be

WITH T
     AS (SELECT DISTINCT ISNULL(t1.myint, t2.my2ndint) AS intcol
         FROM   #temp t1
                FULL OUTER JOIN #temp t2
                  ON t1.myint = t2.my2ndint
         WHERE  t1.myint IS NULL
                 OR t2.my2ndint IS NULL)
SELECT T.intcol,
       T.intcol
FROM   T
       JOIN TestImplicitConverts TIC
         ON TIC.intcol = T.intcol 
share|improve this answer
    
I was thinking of something like WHERE intcol IN (SELECT myint FROM #temp UNION SELECT my2ndint FROM #temp EXCEPT SELECT myint FROM #temp INTERSECT SELECT my2ndint FROM #temp) –  ypercube Oct 23 '13 at 11:49
    
@ypercube - I had to think a bit about the order of precedence on that one but yes that looks neater. –  Martin Smith Oct 23 '13 at 12:26

The reason is your OR forces the engine to do a scan, because it has to hit multiple values and therefore cannot seek the index, as you see clearly yourself.

Either avoid the OR by rewriting the logic/query or you'll have to tolerate the scan. Ways to rewrite to avoid could be to union the two results together for example. Ideally - union as small an amount as possible and then join possible other tables on after the join is made.

Generally speaking one should avoid OR and NOT INs if possible for optimal index usage.

This was answered - although for Oracle, but it is the same issue - in this thread: Slow join behaviour with 'or' in predicate

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.