optimizing the query in a stored proc

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 2005 Forums

Transact-SQL (2005)

optimizing the query in a stored proc

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-23 : 16:23:51

Hi,

My stored proc takes 6+ mins. I executed the code block by block and found that the below query takes time.

The piece below from the query takes a while as the table has billions of records. Any alternate suggestions on optimization?

INNER JOIN
ResponseLeg rsl
ON
rs1.MISResponseID = res.MISResponseID
and rs1.MISInquiryID = iql.MISInquiryID
and rs1.LegSequence = iql.LegSequence

SELECT
InquiryDate = inq.InquiryDate,
InquiryID = inq.SystemInquiryID,
Client = cli.Company_Name,
CPLevel = MAX(trd.CPLevel),
DealerCount = COUNT(DISTINCT res.CPPivotalCompanyID),
ResponseCount = COUNT(DISTINCT CASE WHEN rsl.CPLevel IS NOT NULL THEN res.CPPivotalCompanyID END),
--ExecutedDealer = MAX(CASE WHEN rsl.State = 'T' THEN REPLACE(dlr.Short_Name_1, '_US', '') END),
--ExecutedPrice = MAX(CASE WHEN rsl.State = 'T' THEN rsl.Price END),
--ExecutedLevel = MAX(CASE WHEN rsl.State = 'T' THEN rsl.CPLevel END),
--CoverLevel = MAX(CASE WHEN rsl.State = 'C' THEN rsl.CPLevel END),
--CoverDealer = MAX(CASE WHEN rsl.State = 'C' THEN REPLACE(dlr.Short_Name_1, '_US', '') END),
TradeYear = trd.TradeYear,
TradeMonthName = trd.TradeMonthName,
TradeMonth = trd.TradeMonth
--INTO
--#Response
FROM
#Trade trd
INNER JOIN
Inquiry inq
ON
trd.MISInquiryID = inq.MISInquiryID
INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID
INNER JOIN
Response res
ON
res.MISInquiryID = iql.MISInquiryID
INNER JOIN
ResponseLeg rsl
ON
rs1.MISResponseID = res.MISResponseID
and rs1.MISInquiryID = iql.MISInquiryID
and rs1.LegSequence = iql.LegSequence
INNER JOIN
pivotal..Company dlr
ON
res.CPPivotalCompanyID = dlr.Company_Id
INNER JOIN
pivotal..Company cli
ON
trd.ClientID = cli.Company_Id
WHERE
inq.IsVolume = 1
and iql.IsVolume = 1
and InquiryType = 'F'
GROUP BY
trd.TradeYear,
trd.TradeMonth,
trd.TradeMonthName,
cli.Company_Name,
inq.InquiryDate,
inq.SystemInquiryID

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-23 : 16:37:29

What indexes you have on ResponseLeg? Also you need to have index on temp table.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-23 : 17:09:07

MISResponseLegID is the primary key with Clustered index.
All remaining columns LegSequence, SystemID and SystemInquiryID are nonunique and nonclustered indexes

Index on temp table? I will try that

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 17:14:58

Post Query Plan please.

This doesn't look right:


INNER JOIN
ResponseLeg	 rsl
ON
rs1.MISResponseID	 = res.MISResponseID
and	rs1.MISInquiryID = iql.MISInquiryID
and	rs1.LegSequence	 = iql.LegSequence

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-24 : 07:54:21

Yes the above join is the one that looks incorrect.
Question, what do you mean by Query plan?

Is this a comman I should give like showplan? Please elaborate

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-02-24 : 09:17:16

you missed kirstens point. the aliases seem wrong/inconsistent.
rsl (rs-ell) v rs1 (rs-one)

See the FAQ's on this site re generating "explain plans"

Kristen
Test

22859 Posts

Posted - 2010-02-24 : 11:34:07

" Question, what do you mean by Query plan?"


SHOWPLAN_TEXT ON
GO

... *** YOUR QUERY HERE *** ...

SET SHOWPLAN_TEXT OFF
GO

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-24 : 15:41:38

This is what I got

|--Table Insert(OBJECT:([#Response]), SET:([#Response].[InquiryDate] = [MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate],[#Response].[InquiryID] = [MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID],[#Response].[Client] = [pivotal].[dbo].[CompanyMaster].[Company_Name],[#Response].[CPLevel] = [Expr1022],[#Response].[DealerCount] = [Expr1023],[#Response].[ResponseCount] = [Expr1024],[#Response].[ExecutedDealer] = [Expr1025],[#Response].[ExecutedPrice] = [Expr1026],[#Response].[ExecutedLevel] = [Expr1027],[#Response].[CoverLevel] = [Expr1028],[#Response].[CoverDealer] = [Expr1029],[#Response].[TradeYear] = [tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear],[#Response].[TradeMonthName] = [tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName],[#Response].[TradeMonth] = [tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([trd].[TradeYear]=[tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear], [trd].[TradeMonth]=[tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth], [trd].[TradeMonthName]=[tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name]=[pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate]=[MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate], [inq].[SystemInquiryID]=[MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
|--Hash Match(Inner Join, HASH:([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID])=([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID]), RESIDUAL:([tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear] = [tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear] AND [tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth] = [tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth] AND [tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName] = [tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName] AND [pivotal].[dbo].[CompanyMaster].[Company_Name] = [pivotal].[dbo].[CompanyMaster].[Company_Name] AND [MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate] = [MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate] AND [MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID] = [MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID])=([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID]), RESIDUAL:([tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear] = [tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear] AND [tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth] = [tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth] AND [tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName] = [tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName] AND [pivotal].[dbo].[CompanyMaster].[Company_Name] = [pivotal].[dbo].[CompanyMaster].[Company_Name] AND [MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate] = [MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate] AND [MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID] = [MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
| |--Compute Scalar(DEFINE:([trd].[TradeYear]=[tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear], [trd].[TradeMonth]=[tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth], [trd].[TradeMonthName]=[tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name]=[pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate]=[MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate], [inq].[SystemInquiryID]=[MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
| | |--Compute Scalar(DEFINE:([Expr1023]=CONVERT_IMPLICIT(int,[Expr1110],0)))
| | |--Stream Aggregate(GROUP BY:([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID]) DEFINE:([Expr1110]=Count(*)))
| | |--Sort(DISTINCT ORDER BY:([trd].[TradeYear] ASC, [trd].[TradeMonth] ASC, [trd].[TradeMonthName] ASC, [pivotal].[dbo].[CompanyMaster].[Company_Name] ASC, [inq].[InquiryDate] ASC, [inq].[SystemInquiryID] ASC, [res].[CPPivotalCompanyID] ASC))
| | |--Table Spool
| | |--Compute Scalar(DEFINE:([Expr1038]=CASE WHEN [MISTrading].[dbo].[ResponseLeg].[CPLevel] as [rsl].[CPLevel] IS NOT NULL THEN [MISTrading].[dbo].[Response].[CPPivotalCompanyID] as [res].[CPPivotalCompanyID] ELSE NULL END))
| | |--Parallelism(Gather Streams)
| | |--Hash Match(Inner Join, HASH:([pivotal].[dbo].[CompanyMaster].[Company_Id])=([res].[CPPivotalCompanyID]), RESIDUAL:([MISTrading].[dbo].[Response].[CPPivotalCompanyID] as [res].[CPPivotalCompanyID]=[pivotal].[dbo].[CompanyMaster].[Company_Id]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([pivotal].[dbo].[CompanyMaster].[Company_Id]))
| | | |--Table Scan(OBJECT:([pivotal].[dbo].[CompanyMaster]), WHERE:(CONVERT_IMPLICIT(numeric(2,2),isnull([pivotal].[dbo].[CompanyMaster].[MA_CommissionTS],'0'),0)<>(0.25)))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([res].[CPPivotalCompanyID]))
| | |--Hash Match(Inner Join, HASH:([inq].[MISInquiryID])=([rsl].[MISInquiryID]))
| | |--Hash Match(Inner Join, HASH:([inq].[MISInquiryID])=([inq].[MISInquiryID]))
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([inq].[MISInquiryID]))
| | | | |--Index Scan(OBJECT:([MISTrading].[dbo].[Inquiry].[IX_Inquiry_3] AS [inq]), WHERE:([MISTrading].[dbo].[Inquiry].[IsVolume] as [inq].[IsVolume]=(1) AND [MISTrading].[dbo].[Inquiry].[InquiryType] as [inq].[InquiryType]='F'))
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([inq].[MISInquiryID]))
| | | |--Index Scan(OBJECT:([MISTrading].[dbo].[Inquiry].[IX_Inquiry_2] AS [inq]))
| | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([res].[MISInquiryID])=([rsl].[MISInquiryID]), RESIDUAL:([MISTrading].[dbo].[ResponseLeg].[MISInquiryID] as [rsl].[MISInquiryID]=[MISTrading].[dbo].[Response].[MISInquiryID] as [res].[MISInquiryID] AND [MISTrading].[dbo].[Response].[MISResponseID] as [res].[MISResponseID]=[MISTrading].[dbo].[ResponseLeg].[MISResponseID] as [rsl].[MISResponseID]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([res].[MISInquiryID]), ORDER BY:([res].[MISInquiryID] ASC))
| | | |--Index Scan(OBJECT:([MISTrading].[dbo].[Response].[IX_Response_4] AS [res]), ORDERED FORWARD)
| | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([rsl].[MISInquiryID])=([iql].[MISInquiryID]), RESIDUAL:([MISTrading].[dbo].[InquiryLeg].[MISInquiryID] as [iql].[MISInquiryID]=[MISTrading].[dbo].[ResponseLeg].[MISInquiryID] as [rsl].[MISInquiryID] AND [MISTrading].[dbo].[InquiryLeg].[LegSequence] as [iql].[LegSequence]=[MISTrading].[dbo].[ResponseLeg].[LegSequence] as [rsl].[LegSequence]))
| | |--Compute Scalar(DEFINE:([Expr1030]=CASE WHEN [MISTrading].[dbo].[ResponseLeg].[State] as [rsl].[State]='T' THEN [MISTrading].[dbo].[ResponseLeg].[Price] as [rsl].[Price] ELSE NULL END, [Expr1031]=CASE WHEN [MISTrading].[dbo].[ResponseLeg].[State] as [rsl].[State]='T' THEN [MISTrading].[dbo].[ResponseLeg].[CPLevel] as [rsl].[CPLevel] ELSE NULL END, [Expr1032]=CASE WHEN [MISTrading].[dbo].[ResponseLeg].[State] as [rsl].[State]='C' THEN [MISTrading].[dbo].[ResponseLeg].[CPLevel] as [rsl].[CPLevel] ELSE NULL END))
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([rsl].[MISInquiryID]), ORDER BY:([trd].[MISInquiryID] ASC))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([rsl].[MISResponseLegID], [Expr1109]) WITH ORDERED PREFETCH)
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([trd].[MISInquiryID], [Expr1108]) WITH ORDERED PREFETCH)
| | | | |--Sort(ORDER BY:([trd].[MISInquiryID] ASC))
| | | | | |--Hash Match(Inner Join, HASH:([pivotal].[dbo].[CompanyMaster].[Company_Id])=([trd].[ClientID]), RESIDUAL:([tempdb].[dbo].[#Trade].[ClientID] as [trd].[ClientID]=[pivotal].[dbo].[CompanyMaster].[Company_Id]))
| | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([pivotal].[dbo].[CompanyMaster].[Company_Id]))
| | | | | | |--Table Scan(OBJECT:([pivotal].[dbo].[CompanyMaster]), WHERE:(CONVERT_IMPLICIT(numeric(2,2),isnull([pivotal].[dbo].[CompanyMaster].[MA_CommissionTS],'0'),0)<>(0.25)))
| | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([trd].[ClientID]))
| | | | | |--Table Scan(OBJECT:([tempdb].[dbo].[#Trade] AS [trd]))
| | | | |--Index Seek(OBJECT:([MISTrading].[dbo].[ResponseLeg].[IX_ResponseLeg_1] AS [rsl]), SEEK:([rsl].[MISInquiryID]=[tempdb].[dbo].[#Trade].[MISInquiryID] as [trd].[MISInquiryID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([MISTrading].[dbo].[ResponseLeg].[PK_ResponseLeg] AS [rsl]), SEEK:([rsl].[MISResponseLegID]=[MISTrading].[dbo].[ResponseLeg].[MISResponseLegID] as [rsl].[MISResponseLegID]) LOOKUP ORDERED FORWARD)
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([iql].[MISInquiryID]), ORDER BY:([iql].[MISInquiryID] ASC))
| | |--Index Scan(OBJECT:([MISTrading].[dbo].[InquiryLeg].[IX_InquiryLeg_3] AS [iql]), WHERE:([MISTrading].[dbo].[InquiryLeg].[IsVolume] as [iql].[IsVolume]=(1)) ORDERED FORWARD)
| |--Compute Scalar(DEFINE:([trd].[TradeYear]=[tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear], [trd].[TradeMonth]=[tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth], [trd].[TradeMonthName]=[tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name]=[pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate]=[MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate], [inq].[SystemInquiryID]=[MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
| |--Compute Scalar(DEFINE:([Expr1024]=CONVERT_IMPLICIT(int,[Expr1111],0)))
| |--Stream Aggregate(GROUP BY:([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID]) DEFINE:([Expr1111]=COUNT([Expr1038])))
| |--Sort(DISTINCT ORDER BY:([trd].[TradeYear] ASC, [trd].[TradeMonth] ASC, [trd].[TradeMonthName] ASC, [pivotal].[dbo].[CompanyMaster].[Company_Name] ASC, [inq].[InquiryDate] ASC, [inq].[SystemInquiryID] ASC, [Expr1038] ASC))
| |--Table Spool
|--Compute Scalar(DEFINE:([trd].[TradeYear]=[tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear], [trd].[TradeMonth]=[tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth], [trd].[TradeMonthName]=[tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name]=[pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate]=[MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate], [inq].[SystemInquiryID]=[MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
|--Stream Aggregate(GROUP BY:([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID]) DEFINE:([Expr1022]=MAX([tempdb].[dbo].[#Trade].[CPLevel] as [trd].[CPLevel]), [Expr1025]=MAX(CASE WHEN [MISTrading].[dbo].[ResponseLeg].[State] as [rsl].[State]='T' THEN replace([pivotal].[dbo].[CompanyMaster].[Short_Name_1],'_US','') ELSE NULL END), [Expr1026]=MAX([Expr1030]), [Expr1027]=MAX([Expr1031]), [Expr1028]=MAX([Expr1032]), [Expr1029]=MAX(CASE WHEN [MISTrading].[dbo].[ResponseLeg].[State] as [rsl].[State]='C' THEN replace([pivotal].[dbo].[CompanyMaster].[Short_Name_1],'_US','') ELSE NULL END)))
|--Sort(ORDER BY:([trd].[TradeYear] ASC, [trd].[TradeMonth] ASC, [trd].[TradeMonthName] ASC, [pivotal].[dbo].[CompanyMaster].[Company_Name] ASC, [inq].[InquiryDate] ASC, [inq].[SystemInquiryID] ASC))
|--Table Spool

Kristen
Test

22859 Posts

Posted - 2010-02-24 : 23:40:43

The table pivotal.dbo.CompanyMaster is not in the query above; presumably it is a VIEW, need to see the DDL for that please

Bit concerning that CompanyMaster is subject of a Table Scan for MA_CommissionTS (presumably that is in the View), rather than just being able to do a Clustered Index PK on res.CPPivotalCompanyID and trd.ClientID? (This has to happen twice, once each for Company and Client). Maybe there are very few rows in CompanyMaster table though.

Table Scan(OBJECT:([pivotal].[dbo].[CompanyMaster]), WHERE:(CONVERT_IMPLICIT(numeric(2,2),isnull([pivotal].[dbo].[CompanyMaster].[MA_CommissionTS],'0'),0)<>(0.25)))

#TRADE may benefit from two indexes: MISInquiryID and ClientID

Index IX_Inquiry_3 on MISTrading.dbo.InquiryLeg is scanned for IsVolume = 1 - it might be helpful if there was a composite index on MISTrading.dbo.InquiryLeg for MISInquiryID, IsVolume, LegSequence (to cover the JOIN conditions, and later JOIN to ResponseLeg)

Index Scan(OBJECT:([MISTrading].[dbo].[InquiryLeg].[IX_InquiryLeg_3] AS [iql]),
WHERE:([MISTrading].[dbo].[InquiryLeg].[IsVolume] as [iql].[IsVolume]=(1)) ORDERED FORWARD)

Similarly Index IX_Inquiry_3 on MISTrading.dbo.Inquiry which is scanned for IsVolume = 1 AND InquiryType = 'F' - it might be helpful if there was a composite index on MISTrading.dbo.Inquiry for MISInquiryID, IsVolume, InquiryType

Index Scan(OBJECT:([MISTrading].[dbo].[Inquiry].[IX_Inquiry_3] AS [inq]),
WHERE:([MISTrading].[dbo].[Inquiry].[IsVolume] as [inq].[IsVolume]=(1)
AND [MISTrading].[dbo].[Inquiry].[InquiryType] as [inq].[InquiryType]='F'))

Index Scan(OBJECT:([MISTrading].[dbo].[Inquiry].[IX_Inquiry_2] AS [inq]))

It would help to see the DDL for Inquiry.IX_Inquiry_2 and Inquiry.IX_Inquiry_3 to see why the parallel tasks:

Hash Match(Inner Join, HASH:([inq].[MISInquiryID])=([inq].[MISInquiryID]))
Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([inq].[MISInquiryID]))
Index Scan(OBJECT:([MISTrading].[dbo].[Inquiry].[IX_Inquiry_3] AS [inq]),
WHERE:([MISTrading].[dbo].[Inquiry].[IsVolume] as [inq].[IsVolume]=(1) AND [MISTrading].[dbo].[Inquiry].[InquiryType] as [inq].[InquiryType]='F'))
Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([inq].[MISInquiryID]))
Index Scan(OBJECT:([MISTrading].[dbo].[Inquiry].[IX_Inquiry_2] AS [inq]))

Index IX_ResponseLeg_1 on ResponseLeg is used in a SEEK to match #Trade.MISInquiryID

Index Seek(OBJECT:([MISTrading].[dbo].[ResponseLeg].[IX_ResponseLeg_1] AS [rsl]),
SEEK:([rsl].[MISInquiryID]=[tempdb].[dbo].[#Trade].[MISInquiryID] as [trd].[MISInquiryID]) ORDERED FORWARD)

appears to only be used to match MISInquiryID. A compound index on MISInquiryID, MISResponseID, LegSequence might cover the JOIN better - although the Merge Join may be doing that:

Merge Join(Inner Join, MANY-TO-MANY MERGE:([res].[MISInquiryID])=([rsl].[MISInquiryID]),
RESIDUAL:([MISTrading].[dbo].[ResponseLeg].[MISInquiryID] as [rsl].[MISInquiryID]=[MISTrading].[dbo].[Response].[MISInquiryID] as [res].[MISInquiryID]
AND [MISTrading].[dbo].[Response].[MISResponseID] as [res].[MISResponseID]=[MISTrading].[dbo].[ResponseLeg].[MISResponseID] as [rsl].[MISResponseID]))

??

Appending CPLevel to the index would cover the SELECT and might avoid the LOOKUP:

Clustered Index Seek(OBJECT:([MISTrading].[dbo].[ResponseLeg].[PK_ResponseLeg] AS [rsl]),
SEEK:([rsl].[MISResponseLegID]=[MISTrading].[dbo].[ResponseLeg].[MISResponseLegID] as [rsl].[MISResponseLegID]) LOOKUP ORDERED FORWARD)

[Response] is being Scanned, need to see the DDL for IX_Response_4

Index Scan(OBJECT:([MISTrading].[dbo].[Response].[IX_Response_4] AS [res]), ORDERED FORWARD)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources