Solved

SQL Server - having OR in WHERE clause causes Index Scan

Posted on 2013-10-23
3
493 Views
Last Modified: 2013-10-23
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.

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

Open in new window




 
 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

Open in new window

0
Comment
Question by:jxharding
3 Comments
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
ID: 39593419
Hello,

How about splitting the INSERT statement into two statements?

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)

insert into #temp(myint,my2ndint)
    SELECT intcol, intcol
    FROM    TestImplicitConverts
    WHERE  intcol in (select my2ndint from #temp  )
          and  intcol not in (select myint from #temp  )

Open in new window

-Harish
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39593420
there is way more than just one 'OR' in that where clause...

when you use IN () / NOT IN() the contents you are judging are all treated as OR's

I would suggest trying EXITS and NOT EXISTS instead
0
 

Author Closing Comment

by:jxharding
ID: 39593426
Brilliant!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware ā€“ malware that gets on your customersā€™ computers, encrypts their data, and extorts a hefty ransom for the decryption keys ā€“ is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Simple SQL query from two tables 13 54
TSQL query to generate xml 4 35
Tsql query 6 22
Present Absent from working date rage 11 21
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question