title: SQL Server 2008 Stored Procedure Performance issue
body: <p>Hi I have a Stored Procedure</p>
  
  <pre><code>     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 &lt;&gt; '1899-12-30 00:00:00.000'
               AND DATEDIFF(dd, GETDATE(),PR.OffMarketDate) &gt; 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
  </code></pre>
  
  <p>On my dev server I have data of 63k in each table</p>
  
  <p>so this procedure took about 30 seconds to return result.</p>
  
  <p>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,</p>
  
  <p>can this be a reason.</p>
  
  <p>if so what can be done, I have all required indexes on tables.</p>
  
  <p>any help would be greatly appreciated.</p>
  
  <p>thanks</p>
  
  <p>Execution Plan
  <a href="http://www.sendspace.com/file/hk8fao" rel="nofollow">http://www.sendspace.com/file/hk8fao</a></p>
  
  <h1>Major Leakage</h1>
  
  <pre><code>OUTER APPLY [dbo].[udf_EP_tbfGetFormularyStatus](@patientid,
                                                                LL.ProductID,
                                                                @pbmid) AS fs
  </code></pre>