Post reply

Need alternative for below query

  • 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

  • September 11, 2015 at 1:57 am

    #1825900

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

    😎

  • September 11, 2015 at 2:29 am

    #1825905

    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

  • September 11, 2015 at 3:00 am

    #1825910

    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

  • September 11, 2015 at 3:20 am

    #1825912

    Here is the execution plan for your reference.

  • September 11, 2015 at 3:38 am

    #1825913

    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).

    😎

  • September 11, 2015 at 4:04 am

    #1825916

    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

  • September 11, 2015 at 10:25 am

    #1826071

    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

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

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