Query using IN Predicate in WHERE Clause Takes up to 2 Hours to Execute!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query using IN Predicate in WHERE Clause Takes up to 2 Hours to Execute!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-18 : 10:07:13

Jayna writes "Hello,

I posted this question on to 4guysfromrolla.com and Bill Wilkinson suggested I forward this interesting question and findings so far to you guys. So here it is.

Issue: SQL Server query (below) takes more than 1 hour (if more data then could take over 2 hours) to execute is there is IN predicate use in its WHERE clause.

Resolution: Removal of IN predicate and replace it with single condition in different select statements then joining the results by UNION executes the query in mere 1.5 minutes!

***************************************************************
Query that takes 1 to 2 hours to execute:

Select Component = 'IPMDC',
System = DLE.System,
HCEMkt = DLE.HCEMkt,
HCEProd = DLE.HCEProd,
[Select/Choice] = DA.Access,
FinArr = DFA.FinancialArrangement,
MktSeg = DMS.MarketSegment,
RRInd = DLE.MajorProd,
Service = DM.MDCCode,
[Month] = DT.MonthName,
Allowed = Sum(F.CmpAllow),
Net = Sum(F.CmpNet),
[Units/Admits] = Sum(F.CmpAdm),
[Visits/Days] = Sum(F.CmpDays),
ELG = sum(F.CMPELG),
Parindid = dp.parindicator,
Withhold = 0
From dbo.vwFactIPClaim F
inner join dbo.vwDimLegalEntity DLE
on F.LegalEntityID = DLE.LegalEntityID
inner join dbo.tbDimAccess DA
on F.AccessID = DA.AccessID
inner join dbo.tbDimFinArr DFA
on F.FinArrID = DFA.FinArrID
inner join dbo.tbDimMktSeg DMS
on F.MktSegID = DMS.MktSegID
inner join dbo.tbDimMDC DM
on F.MDCID = DM.MDCID
inner join dbo.tbDimTime DT
on F.TimeID = DT.TimeID
inner join dbo.tbDimParInd DP
on f.Parindid = dp.Parindid
Where DLE.MajorProd in ('POS/EPO', 'PPO/INDEM') and
DM.MDCCode <> 'MHCDIP'
AND DT.MonthName Between '200204' AND '200404'
Group By DLE.System,
DLE.HCEMkt,
DLE.HCEProd,
DA.Access,
DFA.FinancialArrangement,
DMS.MarketSegment,
DM.MDCCode,
DT.MonthName,
DLE.MajorProd,
dp.parindicator

UNION

Select Component = 'IPAdmType',
System = DLE.System,
HCEMkt = DLE.HCEMkt,
HCEProd = DLE.HCEProd,
[Select/Choice] = DA.Access,
FinArr = DFA.FinancialArrangement,
MktSeg = DMS.MarketSegment,
RRInd = DLE.MajorProd,
Service = DAT.AdmTypeCode,
[Month] = DT.MonthName,
Allowed = Sum(F.CmpAllow),
Net = Sum(F.CmpNet),
[Units/Admits] = Sum(F.CmpAdm),
[Visits/Days] = Sum(F.CmpDays),
ELG = sum(F.CMPELG),
Parindid = dp.parindicator,
Withhold = 0
From dbo.vwFactIPClaim F
inner join dbo.vwDimLegalEntity DLE
on F.LegalEntityID = DLE.LegalEntityID
inner join dbo.tbDimAccess DA
on F.AccessID = DA.AccessID
inner join dbo.tbDimFinArr DFA
on F.FinArrID = DFA.FinArrID
inner join dbo.tbDimMktSeg DMS
on F.MktSegID = DMS.MktSegID
inner join dbo.tbDimAdmType DAT
on F.AdmTypeID = DAT.AdmTypeID
inner join dbo.tbDimTime DT
on F.TimeID = DT.TimeID
inner join dbo.tbDimParInd DP
on f.Parindid = dp.Parindid
Where DLE.MajorProd in ('POS/EPO', 'PPO/INDEM') and
DAT.AdmTypeCode <> 'MHCDIP'
AND DT.MonthName Between '200204' AND '200404'
Group By DLE.System,
DLE.HCEMkt,
DLE.HCEProd,
DA.Access,
DFA.FinancialArrangement,
DMS.MarketSegment,
DAT.AdmTypeCode,
DT.MonthName,
DLE.MajorProd,
dp.parindicator

******************************************************
Query that takes only about 2 minutes to execute:

Select Component = 'IPMDC',
System = DLE.System,
HCEMkt = DLE.HCEMkt,
HCEProd = DLE.HCEProd,

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-18 : 12:15:38

Yeah,

Saw this on dbforums....

Where I asked what the query plan showed beteen the 2

So, what does it show....

And I don't like the between...

If your thinking it's the IN try putting the list in to a table variable and join to it...

but I still can't believe it's the IN

and what about the DDL and the Indexes?

Brett

8-)

JNN
Starting Member

13 Posts

Posted - 2004-06-18 : 13:14:14

Did you follow that post? I did post the query plans and other details as requested... but here it is again.

The tables have indexes on fields used in the WHERE clause, as well as the ones referenced in INNER JOINs. Removing Between or replacing IN with OR doesn't make any difference in the execution time - still takes more than an hour to execute. Two of the responder agreed that the problem is with using IN predicate due to following test:

When I ran the SLOW query with a single SELECT statement with WHERE condition = 'condition' I got 19599 rows in 16 seconds. When I ran the same SLOW query with a single SELECT statement with WHERE condition IN ('POS/EPO', 'DummyValue'), I got 19599 rows in 15 seconds! Note that DummyValue is just that - value that won't be found in the source table of the query. But if run the SLOW query with a single SELECT statement with WHERE condition IN ('POS/EPO', 'PPO/INDEM'), it runs for 31 minutes and 31 seconds. So, once again, using IN becomes the issue when there are two real values supplied that will be found in the source table of the query.

Query Stats for SLOW Query with IN predicate:

Trace:

Text Event Class Duration CPU Reads Writes

SET STATISTICS PROFILE ON SQL:StmtCompleted 0 0 0 0 0
SET STATISTICS PROFILE ON SQL:StmtCompleted 0 0 0 0 0
set STATISTICS IO ON SQL:StmtCompleted 0 0 0 0 0
set STATISTICS TIME ON SQL:StmtCompleted 0 0 0 0 0
Select Component = 'IPMDC',... SQL:StmtCompleted 114 34 69 0 0
SET STATISTICS PROFILE OFF SQL:StmtCompleted 0 0 0 0 0
set noexec off set parseonly off SQL:StmtCompleted 0 0 0 0 0
select IS_SRVROLEMEMBER ('sysadmin') SQL:StmtCompleted 0 0 0 0 0
SET STATISTICS PROFILE ON SQL:StmtCompleted 0 0 0 0 0
Select Component = 'IPMDC',... SQL:StmtCompleted 36 159 69 0 0
SET STATISTICS PROFILE OFF SQL:StmtCompleted 0 0 0 0 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Statistics:

Counter Value Average
Application Profile Statistics
Timer resolution (milliseconds) 0 0
Number of INSERT, UPDATE, DELETE statements 0 0
Rows effected by INSERT, UPDATE, DELETE statements 0 0
Number of SELECT statements 2 2
Rows effected by SELECT statements 76121 76121
Number of user transactions 7 7
Average fetch time 0 0
Cumulative fetch time 0 0
Number of fetches 0 0
Number of open statement handles 0 0
Max number of opened statement handles 0 0
Cumulative number of statement handles 0 0

Network Statistics
Number of server roundtrips 1 1
Number of TDS packets sent 1 1
Number of TDS packets received 3853 3853
Number of bytes sent 64 64
Number of bytes received 1.38624e+007 1.38624e+007

Time Statistics
Cumulative client processing time 467 467
Cumulative wait time on server replies 3.99454e+006 3.99454e+006
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Execution Stats

Execution Time: 1 hour, 6 minutes, and 35 seconds
Rows Returned: 75998
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Execution Plan

Nested Loops/Inner Join (tbDimLegalEntity): 2%
tbIPFactClaim Clustered Index Seek cost: 17%
Nested Loops/Left Outer Join Cost (tbDimLegalEntity, tbDimTime, tbFactIPClaim): 6%
Nested Loops/Inner Join (tbDimLegalEntity): 7%
tbIPFactClaim Clustered Index Seek cost: 17%

Nested Loops/Inner Join (tbDimLegalEntity): 2%
tbIPFactClaim Clustered Index Seek cost: 17%
Nested Loops/Left Outer Join Cost (tbDimLegalEntity, tbDimTime, tbFactIPClaim): 6%
Nested Loops/Inner Join (tbDimLegalEntity): 7%
tbIPFactClaim Clustered Index Seek cost: 17%
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources