Prestatieprobleem met SQL Server 2008 opgeslagen procedure

Hallo, ik heb een opgeslagen procedure

     ALTER PROCEDURE [dbo].[usp_EP_GetTherapeuticalALternates]
    (
      @NDCNumber CHAR(11) ,
      @patientid INT ,
      @pbmid INT
    )
AS 
    BEGIN
        TRUNCATE TABLE TempTherapeuticAlt
        INSERT  INTO TempTherapeuticAlt
                SELECT  --PR.ProductID AS MedicationID ,
                        NULL AS MedicationID ,
                        PR.ePrescribingName AS MedicationName ,
                        U.Strength AS MedicationStrength ,
                        FRM.FormName AS MedicationForm ,
                        PR.DEAClassificationID AS DEASchedule ,
                        NULL AS NDCNumber
        --INTO    #myTemp
                FROM    DatabaseTwo.dbo.Product PR
                        JOIN ( SELECT   MP.MarketedProductID
                               FROM     DatabaseTwo.dbo.Therapeutic_Concept_Tree_Specific_Product TCTSP
                                        JOIN DatabaseTwo.dbo.Marketed_Product MP ON MP.SpecificProductID = TCTSP.SpecificProductID
                                        JOIN ( SELECT   TCTSP.TherapeuticConceptTreeID
                                               FROM     DatabaseTwo.dbo.Marketed_Product MP
                                                        JOIN DatabaseTwo.dbo.Therapeutic_Concept_Tree_Specific_Product TCTSP ON MP.SpecificProductID = TCTSP.SpecificProductID
                                                        JOIN ( SELECT
                                                              PR.MarketedProductID
                                                              FROM
                                                              DatabaseTwo.dbo.Package PA
                                                              JOIN DatabaseTwo.dbo.Product PR ON PA.ProductID = PR.ProductID
                                                              WHERE
                                                              PA.NDC11 = @NDCNumber
                                                             ) PAPA ON MP.MarketedProductID = PAPA.MarketedProductID
                                             ) xxx ON TCTSP.TherapeuticConceptTreeID = xxx.TherapeuticConceptTreeID
                             ) MPI ON PR.MarketedProductID = MPI.MarketedProductID
                        JOIN ( SELECT   P.ProductID ,
                                        O.Strength ,
                                        O.Unit
                               FROM     DatabaseTwo.dbo.Product AS P
                                        INNER JOIN DatabaseTwo.dbo.Marketed_Product
                                        AS M ON P.MarketedProductID = M.MarketedProductID
                                        INNER JOIN DatabaseTwo.dbo.Specific_Product
                                        AS S ON M.SpecificProductID = S.SpecificProductID
                                        LEFT OUTER JOIN DatabaseTwo.dbo.OrderableName_Combined
                                        AS O ON S.SpecificProductID = O.SpecificProductID
                               GROUP BY P.ProductID ,
                                        O.Strength ,
                                        O.Unit
                             ) U ON PR.ProductID = U.ProductID
                        JOIN ( SELECT   PA.ProductID ,
                                        S.ScriptFormID ,
                                        F.Code AS NCPDPScriptFormCode ,
                                        S.FormName
                               FROM     DatabaseTwo.dbo.Package AS PA
                                        INNER JOIN DatabaseTwo.dbo.Script_Form
                                        AS S ON PA.NCPDPScriptFormCode = S.NCPDPScriptFormCode
                                        INNER JOIN DatabaseTwo.dbo.FormCode AS F ON S.FormName = F.FormName
                               GROUP BY PA.ProductID ,
                                        S.ScriptFormID ,
                                        F.Code ,
                                        S.FormName
                             ) FRM ON PR.ProductID = FRM.ProductID
                            WHERE   
                            ( PR.OffMarketDate IS NULL )
        OR ( PR.OffMarketDate = '' )
        OR (PR.OffMarketDate = '1899-12-30 00:00:00.000')
        OR ( PR.OffMarketDate <> '1899-12-30 00:00:00.000'
             AND DATEDIFF(dd, GETDATE(),PR.OffMarketDate) > 0
           )
                GROUP BY PR.ePrescribingName ,
                        U.Strength ,
                        FRM.FormName ,
                        PR.DEAClassificationID
               -- ORDER BY pr.ePrescribingName

        SELECT  LL.ProductID AS MedicationID ,
                temp.MedicationName ,
                temp.MedicationStrength ,
                temp.MedicationForm ,
                temp.DEASchedule ,
                temp.NDCNumber ,
                fs.[ReturnFormulary] AS FormularyStatus ,
                copay.CopaTier ,
                copay.FirstCopayTerm ,
                copay.FlatCopayAmount ,
                copay.PercentageCopay ,
                copay.PharmacyType,
                dbo.udf_EP_GetBrandGeneric(LL.ProductID) AS BrandGeneric
        FROM    TempTherapeuticAlt temp
                OUTER APPLY ( SELECT TOP 1
                                        ProductID
                              FROM      DatabaseTwo.dbo.Product
                              WHERE     ePrescribingName = temp.MedicationName
                            ) AS LL
                OUTER APPLY [dbo].[udf_EP_tbfGetFormularyStatus](@patientid,
                                                              LL.ProductID,
                                                              @pbmid) AS fs
                OUTER APPLY ( SELECT TOP 1
                                        *
                              FROM      udf_EP_CopayDetails(LL.ProductID,
                                                            @PBMID,
                                                            fs.ReturnFormulary)
                            ) copay
        --ORDER BY LL.ProductID
        TRUNCATE TABLE TempTherapeuticAlt
    END

Op mijn dev-server heb ik gegevens van 63k in elke tabel

dus deze procedure duurde ongeveer 30 seconden om het resultaat te retourneren.

On my Production server, it is timing out, or taking >1 minute. I am wondering my production server tables are full with 1400 millions of records,

kan dit een reden zijn.

zo ja, wat kan worden gedaan, ik heb alle vereiste indexen op tabellen.

elke hulp zou zeer gewaardeerd worden.

bedankt

Execution Plan http://www.sendspace.com/file/hk8fao

Grote lekkage

OUTER APPLY [dbo].[udf_EP_tbfGetFormularyStatus](@patientid,
                                                              LL.ProductID,
                                                              @pbmid) AS fs
1

2 antwoord

Enkele strategieën die kunnen helpen:

  1. Verwijder de eerste ORDER BY-instructie, die moordenaar bij complexe query's zou niet nodig moeten zijn.

  2. Gebruik CTE's om de query op te splitsen in kleinere stukjes die afzonderlijk kunnen worden geadresseerd.

  3. Verlaag de nesting in de eerste set JOINs

  4. Pak de tweede en derde reeks joins (de GROUPED-varianten) uit en plaats deze in een tijdelijke geïndexeerde tabel voordat u alles samenvoegt en groepeert.

  5. U hebt de definitie voor function1 of function2 niet opgenomen - aangepaste functies zijn vaak een plaats waar prestatieproblemen zich kunnen verbergen.

Zonder het uitvoeringsplan te zien, is het moeilijk om te zien waar de specifieke problemen kunnen zijn.

4
toegevoegd
Ik heb de downloadlink van het uitvoeringsplan toegevoegd, misschien helpt dat
toegevoegd de auteur HaBo, de bron
Ik merkte dat het probleem is om een ​​functie aan te roepen, tbfGetFOrmularyStatus() weet niet zeker of dit de beste manier is om udf in sp aan te roepen
toegevoegd de auteur HaBo, de bron

U hebt een query die gegevens uit 4 of 5 tabellen selecteert, sommige zelfs meerdere keren. Het is heel moeilijk om te zeggen hoe je kunt verbeteren zonder een diepe analyse van wat je probeert te bereiken en wat tafelstructuur eigenlijk is.

Gegevensgrootte is zeker een probleem; Ik denk dat het vrij duidelijk is dat hoe meer gegevens moeten worden verwerkt, hoe langer de vraag zal duren. Enkele algemene adviezen ... Voer de query rechtstreeks uit en controleer het uitvoeringsplan. Het kan knelpunten aan het licht brengen. Controleer vervolgens of de statistieken up-to-date zijn. Bekijk ook uw tabellen, partitionering kan in sommige gevallen veel helpen. Bovendien kunt u proberen tabellen te wijzigen en een geclusterde index te maken die niet op PK staat (zoals het standaard is, tenzij anders aangegeven), maar op andere kolom [s], zodat uw zoekopdracht kan profiteren van een bepaalde fysieke volgorde van records. Opmerking: doe het alleen als u absoluut zeker weet wat u doet.

Probeer tenslotte uw zoekopdracht te refactoren. Ik heb het gevoel dat er een betere manier is om de gewenste resultaten te krijgen (sorry, zonder begrip van de tabelstructuur en verwachte resultaten kan ik geen exacte oplossing vertellen, maar meerdere joins van dezelfde tabellen en veel afgeleide tabellen zien er niet goed uit voor mij )

3
toegevoegd