Post reply

below script with STUFF function executes 2014 but not in 2019 version

  • July 17, 2023 at 9:46 am

    #4237127

    Select distinct LabOrderUnique,

    STUFF( (SELECT DISTINCT '^' + lotr1.TestName

    FROM [Ncs_conv_IntergyMM_Labordertestresult] AS lotr1

    WHERE lotr1.LabOrderUnique = Lotr.LabOrderUnique FOR XML PATH('')), 1, 1, '') as TestName

    from [dbo].[Ncs_conv_IntergyMM_Labordertestresult] Lotr

     

    Above script i am able to execute in Sql server 2014 in 1 min but not able to execute in 2019 version 6hrs also. but same count of data. Please help me do i need to change any settings

  • July 17, 2023 at 4:15 pm

    #4237378

    Same indexes on both?

    Same MAXDOP on both?

    Same Cost Threshold For Parallelism?

    Same hardware?

    There's a shedload of things that could be causing the 2019 code to be running slower and the list above is just a scratch in the surface of those possibilities.

    And, no... this isn't the "STUFF" method.  STUFF is only being used to get rid of the leading delimiter that's created by the real workhorse in this method and that's the FOR XML PATH.

    Also, I suggest converting the 2019 version to use STRING_AGG() instead of the FOR XML PATH method.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 17, 2023 at 8:44 pm

    #4237535

    Try this:

    SELECT DISTINCT Lotr.LabOrderUnique,
        STUFF( (SELECT DISTINCT CAST('^' AS varchar(max)) + CAST(lotr1.TestName AS varchar(max))
            FROM dbo.Ncs_conv_IntergyMM_Labordertestresult AS lotr1
            WHERE lotr1.LabOrderUnique = Lotr.LabOrderUnique FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),
        1, 1, '') as TestNames
    FROM dbo.Ncs_conv_IntergyMM_Labordertestresult Lotr

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 3 posts - 1 through 2 (of 2 total)

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