Using IN predicate in SQL Server Query

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)

Using IN predicate in SQL Server Query

Author

Topic

JNN
Starting Member

13 Posts

Posted - 2004-06-11 : 18:59:30

Please follow the thread in the following link and provide your feedback if possible:

http://www.aspmessageboard.com/forum/databases.asp?M=705298&T=705298&F=21&P=1

Please note that the thread that shows Execution Plans for the FAST and SLOW Query are reversed, meaning the Execution Plan for FAST Query in the post is actually for the SLOW Query and vice versa.

Thanks.

JN

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-11 : 22:20:48

Took a look at your thread. Do us a favor and post the queries and query plan here. It looks like the performance diffences have less to do with using IN or UNION ALL and more to do with joining to another table (tbDimFinArr) in one query and not joining to it in the other.

The costs shown in the query plan say nothing about rowcounts or I/O.
Run

SET STATISTICS PROFILE ON
set STATISTICS IO ON
set STATISTICS TIME ON

select from the views again, and show us the output.

JNN
Starting Member

13 Posts

Posted - 2004-06-12 : 00:20:39

Well, I'll have to post the Statistics you asked for on Monday, since it's the weekend. So look for it on Monday.

Just a note, I removed the extra join (for FinArr) from the SLOW query and ran it again with the IN predicate, but the same number and type of joins as the FAST running query. It still took 1 hour, 5 minutes and 36 seconds! I don't think FinArr is the major cost factor for this query. DimFinArr only has about 20 records, anyway!

Russell M
Starting Member

2 Posts

Posted - 2004-06-12 : 06:17:34

Something important they might not have read over there...

The main difference that seems to be happening with using "IN" vs not using it, is that when she uses "IN", the execution plan shows hitting the same "Clustered index", 4 times, each at a cost of 17%, where as not using "IN" plan shows a hash/match instead of the index...

Also to make note on, She says that all the data is deleted quarterly, and new data reentered again, this is all repeated quarterly...

Now this is where i said I could be wrong,
But is it not a bad idea to use a clustered index on a table where lots of data is being deleted and re-entered all the time?

Thought this was important so wanted to be sure the guru's over here saw this information as well.

JNN
Starting Member

13 Posts

Posted - 2004-06-12 : 12:51:38

Followings are the queries:

SLOW 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

******************************************************
FAST Query that takes under 2 minutes to execute:

Select Component = 'IPMDC',
System = DLE.System,
HCEMkt = DLE.HCEMkt,
HCEProd = DLE.HCEProd,
[Select/Choice] = DA.Access,
Other = CASE IndivFlagID When 2 Then 'Indiv' Else 'Other Comm' End,
MktSeg = DMS.MarketSegment,
Service = DM.MDCCode,
MonthName = 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.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 = 'HMO/HMO+' and
DM.MDCCode <> 'MHCDIP'
AND DT.MonthName Between '200204' AND '200403'
Group By DLE.System,
DLE.HCEMkt,
DLE.HCEProd,
DA.Access,
DMS.MarketSegment,
DM.MDCCode,
DT.MonthName,
IndivFlagID,
dp.parindicator

UNION

Select Component = 'IPAdmType',
System = DLE.System,
HCEMkt = DLE.HCEMkt,
HCEProd = DLE.HCEProd,
[Select/Choice] = DA.Access,
Other = CASE IndivFlagID When 2 Then 'Indiv' Else 'Other Comm' End,
MktSeg = DMS.MarketSegment,
Service = DAT.AdmTypeCode,
MonthName = 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.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 = 'HMO/HMO+' and
DAT.AdmTypeCode <> 'MHCDIP'
AND DT.MonthName Between '200204' AND '200403'
Group By DLE.System,
DLE.HCEMkt,
DLE.HCEProd,
DA.Access,
DMS.MarketSegment,
DAT.AdmTypeCode,
DT.MonthName,
IndivFlagID,
dp.parindicator

JNN
Starting Member

13 Posts

Posted - 2004-06-14 : 13:05:46

Followings are Query stats, trace, and execution plans for the same query, but with two different predicate use in WHERE clause. One uses IN predicate and the other doesn't. The difference is that the SELECT statement *with* IN predicate takes more than one hour to execute while the SELECT statement *without* IN predicate takes slightly over one minute! Note that both queries hit the same table, which has one primary key/clustered index made up by several fields. I posted the queries over the weekend so you can take a look at that in another post within this thread.

Please analyze the following details and post your feedback. Thanks.

Query *without* 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 35 62 180 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 17 218 252 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 61 108 252 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 21 165 85 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 204 31 2 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 190 233 195 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 2 6 180 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 76265 76265
Number of user transactions 8 8
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 5 5
Number of TDS packets sent 5 5
Number of TDS packets received 3557 3557
Number of bytes sent 10312 10312
Number of bytes received 1.35534e+007 1.35534e+007

Time Statistics
Cumulative client processing time 6070 6070
Cumulative wait time on server replies 1.10827e+009 1.10827e+009
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Execution Stats

Execution Time: 1 minutes and 27 seconds
Rows Returned: 75998
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Execution Plan

tbIPFactClaim Clustered Index Seek cost: 4%
Hash Match/Inner Join Cost (tbDimLegalEntity): 2%
Hash Match/Right Outer Join Cost (tbDimLegalEntity, tbDimTime, tbDimAdmyType, tbFactIPClaim): 3%
Hash Match/Right Outer Join Cost (tbDimLegalEntity, tbDimTime, tbFactIPClaim): 2%
Nested Loops/Left Outer Join Cost (tbDimLegalEntity, tbDimTime, tbFactIPClaim): 1%
Hash Match/Inner Join Cost (tbDimLegalEntity): 2%
Hash Match/Inner Join Cost (tbDimFinArr, tbFactIPClaim): 2%
tbIPFactClaim Clustered Index Seek cost: 6%

tbIPFactClaim Clustered Index Seek cost: 4%
Hash Match/Inner Join Cost (tbDimLegalEntity): 2%
Hash Match/Right Outer Join Cost (tbDimLegalEntity, tbDimTime, tbDimAdmyType, tbFactIPClaim): 3%
Hash Match/Right Outer Join Cost (tbDimLegalEntity, tbDimTime, tbFactIPClaim): 2%
Nested Loops/Left Outer Join Cost (tbDimLegalEntity, tbDimTime, tbFactIPClaim): 1%
Hash Match/Inner Join Cost (tbDimLegalEntity): 2%
Hash Match/Inner Join Cost (tbDimFinArr, tbFactIPClaim): 2%
tbIPFactClaim Clustered Index Seek cost: 6%

tbIPFactClaim Clustered Index Seek cost: 4%
Hash Match/Inner Join Cost (tbDimLegalEntity): 2%
Hash Match/Right Outer Join Cost (tbDimLegalEntity, tbDimTime, tbFactIPClaim): 2%
Nested Loops/Left Outer Join Cost (tbDimLegalEntity, tbDimTime, tbFactIPClaim): 1%
Hash Match/Inner Join Cost (tbDimLegalEntity): 2%
Hash Match/Inner Join Cost (tbDimFinArr, tbFactIPClaim): 2%
tbIPFactClaim Clustered Index Seek cost: 6%

tbIPFactClaim Clustered Index Seek cost: 4%
Hash Match/Inner Join Cost (tbDimLegalEntity): 2%
Hash Match/Right Outer Join Cost (tbDimLegalEntity, tbDimTime, tbDimAdmyType, tbFactIPClaim): 3%
Hash Match/Right Outer Join Cost (tbDimLegalEntity, tbDimTime, tbFactIPClaim): 2%
Nested Loops/Left Outer Join Cost (tbDimLegalEntity, tbDimTime, tbFactIPClaim): 1%
Hash Match/Inner Join Cost (tbDimLegalEntity): 2%
Hash Match/Inner Join Cost (tbDimFinArr, tbFactIPClaim): 2%
tbIPFactClaim Clustered Index Seek cost: 6%
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query *with* IN operator

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%
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

JNN
Starting Member

13 Posts

Posted - 2004-06-14 : 13:55:37

This is for Bill Wilkinson from 4guysfromrolla.com... I ran the test you requested last Friday and here are the results:

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.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-14 : 14:05:47

Drop and recreate the indexes on that table. See if that helps. Run UPDATE STATISTICS for the table first.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

JNN
Starting Member

13 Posts

Posted - 2004-06-14 : 14:16:27

I already did that! Dropped all indexes and recreated them. Also, updated the statistics prior to recreating indexes. Made no difference in execution time.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-14 : 15:49:30

I don't know about everyone else here but what you are showing is
not enough information for me to analyze the problem, and the
output you have is not what I expect to see if you have run:


SET STATISTICS IO ON
SET STATISTICS PROFILE ON 

For example:

	Use Northwind
	SELECT * FROM [Quarterly Orders]
	...
	Table 'Customers'. Scan count 408, logical reads 827, physical reads 0, read-ahead reads 0.
	Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.
	Rows        Executes    StmtText
	----------- ----------- -----------------------------------
	86          1           SELECT * FROM [Quarterly Orders]   
	86          1             |--Sort(DISTINCT ORDER BY:([Customers].[CustomerID] ASC))
	408         1                  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Orders].[CustomerID])) 
	408         1                       |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]), WHERE:([Orders].[OrderDate]>='Jan  1 1997 12:00AM' AND [Orders].[OrderDate]<='Dec 31 1997 12:00AM'))
	408         408                     |--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers]), SEEK:([Customers].[CustomerID]=[Orders].[CustomerID]) ORDERED FORWARD)

The information above is what the SET statements should generate on
SQL Server 2000.

It shows the logical and physical IO, the query plan, indexes used,
row counts, table scans, index scans, index seeks etc.

At least that is what I would need to investigate the slower version
without making random recommendations.

I'll make some random recommendations anyway, but I'm not sure you
want a faster solution as much as an explanation for the slow
performance.

1) Try changing

Where DLE.MajorProd in ('POS/EPO', 'PPO/INDEM') and DAT.AdmTypeCode <> 'MHCDIP'
to
Where (DLE.MajorProd in = 'POS/EPO' or DLE.MajorProd in = 'PPO/INDEM') and DAT.AdmTypeCode <> 'MHCDIP'

and
Where DLE.MajorProd in ('POS/EPO', 'PPO/INDEM') and DM.MDCCode <> 'MHCDIP'
to
Where (DLE.MajorProd in = 'POS/EPO' or DLE.MajorProd in = 'PPO/INDEM') and DM.MDCCode <> 'MHCDIP'

SQL Should do this itself but I have seen cases where it did not
when joining many tables or using indexes with poor selectivity.
With literals it will usually do it correctly.

3) Try reversing the oders of 'POS/EPO' and 'PPO/INDEM'.

SQL stops processing rows as soon as the condition is true and
it tests the first condition listed first.

2) Change
SELECT ...
Where DLE.MajorProd in ('POS/EPO', 'PPO/INDEM')
...
UNION
...
Where DLE.MajorProd in ('POS/EPO', 'PPO/INDEM')

to

SElECT ...
Where DLE.MajorProd = 'POS/EPO'
...
UNION
...
Where DLE.MajorProd = 'POS/EPO'

UNION ALL

Where DLE.MajorProd = 'PPO/INDEM'
...
UNION
...
Where DLE.MajorProd = 'PPO/INDEM'

One condition may bennifit from using an index while the other does not.
Seperating the queries into 2 UNIONS will let SQL use the appropriate indexes for the queries seperatly.

JNN
Starting Member

13 Posts

Posted - 2004-06-14 : 16:11:52

I did try OR before posting here, but it made no difference in execution time for the SLOW query - IN and OR both took the same amount to time returning results with the SLOW query.

Yes, I also knew (and that's how my query is currently) that if I devided the conditions found in IN predicate into 2 SELECT statements for each condition in each set of SELECT query (total of 4 SELECT statements) with UNION/UNION ALL then the result returns in 1 minutes and 20 seconds so I am aware that getting rid of IN predicate results in LESS execution time. I just wasn't convinced that it was only IN that was costing me all that extra time, hence the post here!

How does the followings look for the stats you requested for:

FAST Query Stats *without* IN Predicate

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 4386 ms, elapsed time = 4386 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(75998 row(s) affected)

Table 'tbDimParInd'. Scan count 110086, logical reads 220172, physical reads 0, read-ahead reads 0.
Table 'tbDimFinArr'. Scan count 110090, logical reads 220180, physical reads 0, read-ahead reads 0.
Table 'tbDimAccess'. Scan count 110086, logical reads 220172, physical reads 0, read-ahead reads 0.
Table 'tbDimAdmType'. Scan count 58065, logical reads 116126, physical reads 0, read-ahead reads 0.
Table 'tbDimMktSeg'. Scan count 116122, logical reads 232244, physical reads 0, read-ahead reads 0.
Table 'tbDimTime'. Scan count 60982, logical reads 121958, physical reads 0, read-ahead reads 0.
Table 'tbDimLegalEntity'. Scan count 16, logical reads 592, physical reads 0, read-ahead reads 0.
Table 'tbAdjFactorsRing'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0.
Table 'tbFactIPClaim'. Scan count 2600, logical reads 52260, physical reads 0, read-ahead reads 68.
Table 'tbAdjFactorsBurton'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0.
Table 'tbAdjFactorsRissman'. Scan count 4, logical reads 120, physical reads 0, read-ahead reads 0.
Table 'tbDimMDC'. Scan count 58061, logical reads 116122, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 44422 ms, elapsed time = 66084 ms.

SQL Server Execution Times:
CPU time = 44422 ms, elapsed time = 66499 ms.

SQL Server Execution Times:
CPU time = 44422 ms, elapsed time = 66510 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SLOW Query Stats *with* IN Predicate

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 2563 ms, elapsed time = 2569 ms.

(75998 row(s) affected)

Table 'tbDimLegalEntity'. Scan count 1246180, logical reads 2507144, physical reads 0, read-ahead reads 0.
Table 'tbDimMktSeg'. Scan count 270420, logical reads 540840, physical reads 0, read-ahead reads 0.
Table 'tbDimTime'. Scan count 1455333, logical reads 2910666, physical reads 0, read-ahead reads 0.
Table 'tbDimAdmType'. Scan count 731431, logical reads 1462862, physical reads 0, read-ahead reads 0.
Table 'tbDimAccess'. Scan count 488650, logical reads 977300, physical reads 0, read-ahead reads 0.
Table 'tbDimParInd'. Scan count 488650, logical reads 977300, physical reads 0, read-ahead reads 0.
Table 'tbAdjFactorsRissman'. Scan count 487106, logical reads 14613180, physical reads 0, read-ahead reads 0.
Table 'tbAdjFactorsBurton'. Scan count 488650, logical reads 977300, physical reads 0, read-ahead reads 0.
Table 'tbAdjFactorsRing'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'tbFactIPClaim'. Scan count 2596, logical reads 30904, physical reads 0, read-ahead reads 0.
Table 'tbDimFinArr'. Scan count 14, logical reads 28, physical reads 0, read-ahead reads 0.
Table 'tbDimMDC'. Scan count 140155, logical reads 280310, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 3881343 ms, elapsed time = 3913996 ms.

SQL Server Execution Times:
CPU time = 3881375 ms, elapsed time = 3916266 ms.

SQL Server Execution Times:
CPU time = 3881375 ms, elapsed time = 3916269 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-14 : 16:38:47

Like kselvia, I'm not sure if it is a 'speed this up' or 'why is it slow', but here are my thoughts...

Use:
inner join (Select * From dbo.vwDimLegalEntity Where MajorProd in ('POS/EPO', 'PPO/INDEM')) DLE
instead of:
inner join dbo.vwDimLegalEntity DLE

Also, your slow and fast queries have two different sets of parameters for the monthname condition?!?

Don't know if that will help at all but its my shot.

Corey

JNN
Starting Member

13 Posts

Posted - 2004-06-14 : 16:49:31

I have speeded up the execution time, but wanted to know if using IN predicate in a SELECT statement could cost so much extra - more than an hour in execution time!

I needed to add SET SHOWPLAN_ALL ON To see the execution plans/Index use during the query execution, which you hadn't mentioned in your post, but I found it and followings are the plans for both FAST and SLOW query:

The forum isn't allowing me to post the text of execution plan!! I don't know wsy, could it be because it's 270 rows?!

Thanks.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-14 : 16:52:17

Still no query plans, but my initial question about the join to tbDimFinArr in the slow query still seems relevant.
That table is obviously referenced in the vwDimLegalEntity view (which we have not seen) since it occurs in both IO stats.
Adding it as a join in the slow query has changed the index used, both in the view and the query,
causing the view itself to crater, performance wise.

JNN
Starting Member

13 Posts

Posted - 2004-06-14 : 16:58:47

Here is the vwDimLegalEntity:

CREATE VIEW dbo.vwDimLegalEntity
AS
SELECT *
FROM tbDimLegalEntity
WHERE Active = 1

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-14 : 17:05:57

Sorry, didn't notice the other view vwFactIPClaim. Does that view reference tbDimFinArr?

Also, I recognize that changing the IN to IN ('POS/EPO', 'DummyValue') seems to isolate the problem to the IN predicate.

I'm just trying to understand what SQL Server is doing.

JNN
Starting Member

13 Posts

Posted - 2004-06-14 : 17:19:47

vwFactIPClaim gets data from vw_FactIPClaim_RingBurtonRissman:

CREATE VIEW dbo.vwFactIPClaim
AS
SELECT *
FROM vw_FactIPClaim_RingBurtonRissman
Where TimeID <= 64
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

vw_Fact_IPClaim_RingBurtonRissman gets data from vwBusFactIPClaim

CREATE VIEW dbo.vw_FactIPClaim_RingBurtonRissman
AS
Select LegalEntityID,
AccessID,
EligibilityID,
SubGroupID,
FinArrID,
CountyID,
IndivFlagID,
MktSegID,
UBHFlagID,
SurchargeID,
AdmTypeID,
MDCID,
ParIndID,
TimeID,
UnCmpAllow,
UnCmpNet,
UnCmpAdm,
UnCmpDays,
UnCmpElg,
CmpAllow,
CmpNet,
CmpAdm,
CmpDays,
CMPELG,
DolAdj,
DayAdj,
AdmAdj,
ReconAdj,
FredFactor = 1,
BurtonAdj = 1,
RissmanDollarAdj = 1,
RissmanAdmitVisitAdj = 1 ,
RissmanDaysProcAdj = 1
From dbo.vwBusFactIPClaim
Where TimeID <= 12
UNION
SELECT F.LegalEntityID,
F.AccessID,
F.EligibilityID,
F.SubGroupID,
F.FinArrID,
F.CountyID,
F.IndivFlagID,
F.MktSegID,
F.UBHFlagID,
F.SurchargeID,
F.AdmTypeID,
F.MDCID,
F.ParIndID,
F.TimeID,
F.UnCmpAllow,
F.UnCmpNet,
F.UnCmpAdm,
F.UnCmpDays,
F.UnCmpElg,
CmpAllow = (F.CmpAllow * isnull(x.FredFactor ,1)*isnull( y.BurtonAdj,1) * isnull(z.RissmanDollarAdj,1)),
CmpNet = (F.CmpNet * isnull(x.FredFactor ,1)* isnull(y.BurtonAdj,1) * isnull(z.RissmanDollarAdj,1)),
CmpAdm = (F.CmpAdm *isnull( x.FredFactor,1) * isnull(y.BurtonAdj,1) * isnull(z.RissmanAdmitVisitAdj,1)),
CmpDays = (F.CmpDays *isnull( x.FredFactor ,1)* isnull(y.BurtonAdj,1) * isnull(z.RissmanDaysProcAdj,1)),
CMPELG = (F.CMPELG * isnull(x.FredFactor,1) * isnull(y.BurtonAdj,1)* isnull(z.RissmanDollarAdj,1)),
F.DolAdj,
F.DayAdj,
F.AdmAdj,
F.ReconAdj,
x.FredFactor,
y.BurtonAdj,
z.RissmanDollarAdj,
z.RissmanAdmitVisitAdj,
z.RissmanDaysProcAdj
FROM dbo.vwBusFactIPClaim F
left outer join
(
Select Distinct DLE.LegalEntityID,
DT.TimeID,
FredFactor = F.Reconciliation_Adjustment_Factor
From dbo.tbAdjFactorsRing F
inner join dbo.vwDimLegalEntity DLE
on F.Source = DLE.System and
F.IHA_Product = DLE.MajorProd and
F.IHA_Market = DLE.HCEMkt
inner join dbo.tbDimTime DT
on F.DOS = DT.MonthName
Where TOS = 'INPAT'
) x
on x.LegalEntityID = F.LegalEntityID and
x.TimeID = F.TimeID
left outer join
(
Select Distinct DLE.LegalEntityID,
DT.TimeID,
F.BurtonAdj
From dbo.tbAdjFactorsBurton F
inner join dbo.vwDimLegalEntity DLE
on F.System = DLE.System and
F.HCEProd = DLE.HCEProd
inner join dbo.tbDimTime DT
on F.Month = DT.MonthName
Where F.Service = 'Inpatient'
) y
on y.LegalEntityID = F.LegalEntityID and
y.TimeID = F.TimeID
left outer join
(
Select Distinct DLE.LegalEntityID,
DT.TimeID,
DAT.AdmTypeID,
RissmanDollarAdj = DollarAdj,
RissmanAdmitVisitAdj = AdmitVisitAdj,
RissmanDaysProcAdj = DaysProcAdj
From dbo.tbAdjFactorsRissman F
inner join dbo.vwDimLegalEntity DLE
on F.Source = DLE.System and
F.IHA_Product = DLE.MajorProd
inner join dbo.tbDimTime DT
on F.DOS = DT.MonthName
inner join dbo.tbDimAdmType DAT
on F.Service = DAT.AdmTypeCode
) z
on z.LegalEntityID = F.LegalEntityID and
z.TimeID = F.TimeID and
z.AdmTypeID = F.AdmTypeID
Where F.TimeID > 12
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

vwBusFactIPClaim gets data from tbFactIPClaim

CREATE VIEW dbo.vwBusFactIPClaim
AS
SELECT F.LegalEntityID,
F.AccessID,
F.EligibilityID,
F.SubGroupID,
F.FinArrID,
F.CountyID,
F.IndivFlagID,
F.MktSegID,
F.UBHFlagID,
F.SurchargeID,
F.AdmTypeID,
F.MDCID,
F.ParIndID,
F.TimeID,
F.UnCmpAllow,
F.UnCmpNet,
F.UnCmpAdm,
F.UnCmpDays,
F.UnCmpElg,
F.CmpAllow,
F.CmpNet,
F.CmpAdm,
F.CmpDays,
F.DolAdj,
F.DayAdj,
F.AdmAdj,
F.ReconAdj,
F.CmpElg
FROM dbo.tbFactIPClaim F
inner join dbo.vwDimLegalEntity DLE
on F.LegalEntityID = DLE.LegalEntityID
inner join dbo.tbDimFinArr DFA
on F.FinArrID = DFA.FinArrID
Where DLE.Customer = 'UHC Commercial' and
DFA.FinancialArrangement = 'Insured'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources