Need alternative for below query

Question

Post reply

Need alternative for below query

akash_singh

Ten Centuries

Points: 1089
More actions
September 11, 2015 at 1:43 am

#320522

Hi,
Can anyone please advise/give alternative of below query. It is taking very long to execute.
SELECT b.MeetingID,b.FundID ,b.DisplayClientSiteYN,b.VoteDetailPlacementID,b.MeetingListPlacementID,b.SearchPagePlacementID,LTRIM(STUFF(
(
SELECT ' || ' + a.FundFootnoteText
FROM ( SELECT DISTINCT mi.MeetingID,mi.FundID,mi.FundName + ' ** ' + vfn.FootnoteText As 'FundFootnoteText'
FROM #MeetingInformation mi
INNER JOIN dbo.VDSFundFootnote vfn ON vfn.FundID = mi.FundID
) a
WHERE a.MeetingID = b.MeetingID
FOR XML PATH('')
), 2, 2, '')) AS FundFootnoteText
,LTRIM(STUFF(
(
SELECT ' || ' + a.FundFootnoteSymbol
FROM ( SELECT DISTINCT mi.MeetingID,mi.FundID,mi.FundName + ' ** ' + vfn.FootnoteSymbol As 'FundFootnoteSymbol'
FROM #MeetingInformation mi
INNER JOIN dbo.VDSFundFootnote vfn ON vfn.FundID = mi.FundID
) a
Where a.MeetingID = b.MeetingID
For Xml Path('')
), 2, 2, '')) As FundFootnoteSymbol
FROM (
SELECT DISTINCT a.MeetingID,a.FundID,a.FundName,vfn.FootnoteText ,vfn.FootnoteSymbol,vfn.DisplayClientSiteYN,vfn.VoteDetailPlacementID,vfn.MeetingListPlacementID,vfn.SearchPagePlacementID
FROM #MeetingInformation a
INNER JOIN dbo.VDSFundFootnote vfn ON vfn.FundID = a.FundID
) b

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 1

Quick question, can you post the DDL (create table), sample data as an insert statement and the desired output?

😎

akash_singh Ten Centuries Points: 1089 More actions · Answer 2

I cannot, due to some restrictions. Can you please suggest. FootNotetext and symbol are varchar columns and all ids columns are of integer type. We are trying to bring in a comma separated format.

Thanks,

Akash

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Can you at least post the execution plan? It's very, very hard to offer meaningful performance suggestions with just a query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

akash_singh Ten Centuries Points: 1089 More actions · Answer 4

akash_singh

Ten Centuries

Points: 1089

September 11, 2015 at 3:20 am

#1825912

Here is the execution plan for your reference.

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 5

akash_singh (9/11/2015)
Here is the execution plan for your reference.

The image posted does not help much, the only thing one can suggest from this is to add covering indexes to the tables where there is table scan (and key lookup).

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please post the execution plan, not a picture of the plan. A picture is missing most of the useful information.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Will1922 SSCertifiable Points: 7189 More actions · Answer 7

Any of those table scans could be the issue. Are all tables indexed? I second the suggestion about having covering indexes.

Live to Throw
Throw to Live
Will Summers