slow Stored Procedure

TRUNCATE TABLE dbo.CDS_ts_ERMD_Sales_Details

-- Set a rolling start of ten years from the bigging of current year
DECLARE      @RollingStartDate datetime
SET            @RollingStartDate = DATEADD(yy, -6, dbo.fn_StartDate(getdate()))


INSERT INTO dbo.CDS_ts_ERMD_Sales_Details
SELECT             profdet.ERM_PN,
                  profdet.ERM_PN_Name,
                  profdet.[ERM_PN_Customer/Prospect_Status],
                  profdet.ERM_Primary_Officer_user_role_id,

                  so.opportunity_id,
                  so.sales_opportunity_name,
                  so.campaign_id,
                  camp.campaign_name,
                  deal.deal_id,
                  prodfam.product_family_name AS 'Sales_Effort_Name',
                  prod.deal_product_id,
                  proditm.product_family_id,
                  proditm.product_family_name,
                  prod.product_id,
                  proditm.product_item_name,
                  prod.action_id,
                  actn.action_name,
                  prod.dispose_date,
                  prod.sales_cycle_stage_id,
                  prod.disposition_status_flag,
                  disprsn.disposition_reason_desc,
                  awrdcomp.awarded_company_name,
                  prod.product_locked_flag,
                  prod.override_modified_by,
                  prod.override_modified_date,

                  prod.to_clas_profile_id,      -- profile product is for and which is used in CLAS
                  prod.product_in_clas,

-- get all PIA details for each product
            CONVERT(Bit ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,1,'Daily')) AS'pia_2nd_Lien?',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,24,'Daily')) AS'pia_Account_Number',
            CONVERT(Numeric (19,6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,59,'Daily')) AS'pia_Actual_Interest_Rate',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,18,'Daily')) AS'pia_Adj_Variable_Contribution',

            /********************************************************/
            /* Use Actual_Interest_Rate from ePF. It accounts for       */
            /* all ePF logic. If NULL then use what has been typed      */
            /* into ERM.                                                                  */
            /********************************************************/
            CONVERT(numeric(9,5), ISNULL(dbo.fn_ERMPIAValue(prod.deal_product_id, prod.action_id, 59, 'Daily'),dbo.fn_ERMPIAValue(prod.deal_product_id, prod.action_id, 20, 'Daily'))) AS 'pia_All_In_Rate',

            CONVERT(numeric(9,5), dbo.fn_ERMPIAValue(prod.deal_product_id, prod.action_id, 20, 'Daily')) AS 'pia_All_In_Rate_In_ERM',
            
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,27,'Daily')) AS'pia_Assets',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,77,'Daily')) AS'pia_Auto_Line_Type',
            CONVERT(Bit ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,43,'Daily')) AS'pia_Auto_Renewable',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,28,'Daily')) AS'pia_Avg_Balance',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,16,'Daily')) AS'pia_Avg_Deposits',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,15,'Daily')) AS'pia_Avg_New_Outstandings',
            
            -- Adding Commercial Division verions of Outstandings for analysis purposes
            -- Adjusts for short Term, negative values, Construction Loans and Term Loans
            CONVERT(Numeric (19, 6), [Commercial_Segment_Reporting].dbo.fn_CSRProductSaleTotalDaily (prod.deal_product_id,1,0,999)) AS 'pia_Avg_New_Outstandings_IC',
                        
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,17,'Daily')) AS'pia_Avg_Sweep_Balance',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,19,'Daily')) AS'pia_Base_Rate',
            CONVERT(Int ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,3,'Daily')) AS'pia_Bonus_Rate_Period_-_Days',
            CONVERT(Numeric (19,6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,4,'Daily')) AS'pia_Bonus_Rate',
            
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,58,'Daily')) AS'pia_Capital_Adjustment',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,45,'Daily')) AS'pia_Commercial_CD_Type',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,61,'Daily')) AS'pia_Cost_of_Funds_Used',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,62,'Daily')) AS'pia_Cost_of_Funds',

            CONVERT(Numeric (19,6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,21,'Daily')) AS'pia_Deposit_Rate',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,49,'Daily')) AS'pia_Directed_Investment_Type',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,52,'Daily')) AS'pia_Earnings_On_Equity',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,57,'Daily')) AS'pia_Economic_Capital',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,64,'Daily')) AS'pia_Economic_Net_Profit',
            CONVERT(Numeric (19,6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,65,'Daily')) AS'pia_Economic_ROE',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,72,'Daily')) AS'pia_Economic_VC',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,79,'Daily')) AS'pia_ELDP/Energy_Smart_Loan',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,63,'Daily')) AS'pia_EOE_Rate_Used',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,37,'Daily')) AS'pia_Est_Volume',
            
            -- For Lease Draws set Existing Commitment field to $0 and use in Commitment field
            CASE WHEN prod.action_id = 12
                        THEN 0
                        ELSE CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,11,'Daily'))
                   END AS'pia_Existing_Commitment',
            
            CONVERT(Bit ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,41,'Daily')) AS'pia_Express',
            CONVERT(Int ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,25,'Daily')) AS'pia_Facility_Term_-_Months',
            CONVERT(Int ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,34,'Daily')) AS'pia_First_Amortization_-_Months',

            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,32,'Daily')) AS'pia_First_Amortization',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,87,'Daily')) AS'pia_Flexline_Classification',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,76,'Daily')) AS'pia_Floor_Plan_Type',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,39,'Daily')) AS'pia_Govt_Obligation_Type',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,40,'Daily')) AS'pia_Guidance_Line_Type',
            CONVERT(Bit ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,101,'Daily')) AS'pia_High_Risk_Leveraged',      
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,54,'Daily')) AS'pia_Indirect_Variable_Expense',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,48,'Daily')) AS'pia_Interest_Rate_Management_Type',
            CONVERT(Bit ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,86,'Daily')) AS'pia_Interim_Interest_Funding',
            CONVERT(Numeric (19,6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,22,'Daily')) AS'pia_Investment_Rate',
            CONVERT(Int ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,26,'Daily')) AS'pia_Investment_Term',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,30,'Daily')) AS'pia_LGD',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,42,'Daily')) AS'pia_Line_Classification',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,89,'Daily')) AS'pia_Liquidity_Premium_Type',            
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,66,'Daily')) AS'pia_Liquidity_Premium',

            CONVERT(Int ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,29,'Daily')) AS'pia_Loan_Grade',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,53,'Daily')) AS'pia_Loan_Loss',
            
            /********************************************************/
            /* Caclualte real margin.  What is returned by ePF is      */
            /* really just the Spread from ERM.                                    */
            /* (Actual_Interest_Rate) - (Cost of Funds Used)-(LP)      */
            /* If its NULL then leave it NULL.                                    */
            /********************************************************/
            CONVERT(Numeric (19,6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,59,'Daily')) -
            CONVERT(Numeric (19,6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,61,'Daily')) -
            CONVERT(Numeric (19,6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,66,'Daily'))
            AS'pia_Margin',
            
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,50,'Daily')) AS'pia_Net_Interest_Income',
            
            -- For Lease Draws use the Existing Commitment field
            CASE WHEN prod.action_id = 12
                        THEN CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,11,'Daily'))
                        ELSE CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,10,'Daily'))
                   END AS'pia_New_Commitment',
            
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,51,'Daily')) AS'pia_Non_Interest_Income',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,93,'Daily')) AS'pia_Override_Note',
            CONVERT(Bit ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,36,'Daily')) AS'pia_Owner_Occupied',
            CONVERT(Int ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,31,'Daily')) AS'pia_PD',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,82,'Daily')) AS'pia_Rate_Type',
            CONVERT(Bit ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,38,'Daily')) AS'pia_Recourse',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,56,'Daily')) AS'pia_Regulatory_Capital',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,70,'Daily')) AS'pia_Regulatory_Earnings_on_Equity',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,71,'Daily')) AS'pia_Regulatory_Return_on_Equity',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,46,'Daily')) AS'pia_Retail_CD_Type',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,47,'Daily')) AS'pia_Retirement_CD_Type',
            CONVERT(Int ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,35,'Daily')) AS'pia_Second_Amortization_-_Months',      
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,33,'Daily')) AS'pia_Second_Amortization',
            CONVERT(Varchar (35) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,94,'Daily')) AS'pia_Source_of_Funds_1',
            CONVERT(Varchar (35) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,98,'Daily')) AS'pia_Source_of_Funds_2',
            CONVERT(Varchar (35) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,99,'Daily')) AS'pia_Source_of_Funds_3',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,95,'Daily')) AS'pia_Source_of_Funds_Assets_1',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,96,'Daily')) AS'pia_Source_of_Funds_Assets_2',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,97,'Daily')) AS'pia_Source_of_Funds_Assets_3',

            /********************************************************/
            /* If is a variable rate loan, then use the Spread            */
            /* otherwise determine the Spread as:                              */
            /* - (Actual_Interest_Rate) - (Cost of Funds Used)            */
            /* Actual_Interest_Rate accounts for all ePF logic. If      */
            /* its NULL then use what has been typed into ERM.            */
            /* - For Prime - (neg prime) then multiply by -1            */
            /********************************************************/            
            CASE WHEN dbo.fn_ERMPIAValue(prod.deal_product_id, prod.action_id, 82, 'Daily') = 'Variable'
                        THEN CONVERT(numeric(9,5), dbo.fn_ERMPIAValue(prod.deal_product_id, prod.action_id, 23, 'Daily'))
                        ELSE ISNULL(CONVERT(numeric(9,5), dbo.fn_ERMPIAValue(prod.deal_product_id, prod.action_id, 59, 'Daily')) - (CONVERT(numeric(9,5), dbo.fn_ERMPIAValue(prod.deal_product_id, prod.action_id, 61, 'Daily'))), CONVERT(numeric(9,5), dbo.fn_ERMPIAValue(prod.deal_product_id, prod.action_id, 23, 'Daily')))
            END *
                        CASE WHEN CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,19,'Daily')) LIKE '%prime -'
                                    THEN -1
                                    ELSE 1
                        END -- end case statement
            AS'pia_Spread_over_Base_Rate',
            
            CONVERT(numeric(9,5), dbo.fn_ERMPIAValue(prod.deal_product_id, prod.action_id, 23, 'Daily')) AS 'pia_Spread_over_Base_Rate_In_ERM',

            CONVERT(Numeric (19,6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,74,'Daily')) AS'pia_Tax_Rate_Used',
            CONVERT(Numeric (19, 6) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,75,'Daily')) AS'pia_Total_Revenue',
            CONVERT(Varchar (100) ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,2,'Daily')) AS'pia_True_Lease_Classification',
            CONVERT(Int ,dbo.fn_ERMPIAValue(prod.deal_product_id,prod.action_id,55,'Daily')) AS'pia_VC_Status',


            /********************************************************************/
            /* Call funtion which gets the specific fee for the given product.      */
            /* Only specific fees will be retrieved and as more are needed more      */
            /* will need to be added.                                                                  */
            /********************************************************************/

            dbo.fn_ERMProductFee(prod.deal_product_id, 1, 'Daily') AS 'Fee_Referral to 3rd Party_1',
            dbo.fn_ERMProductFee(prod.deal_product_id, 2, 'Daily') AS 'Fee_Cancelled Commitment_2',
            dbo.fn_ERMProductFee(prod.deal_product_id, 3, 'Daily') AS 'Fee_Commitment Fee_3',
            dbo.fn_ERMProductFee(prod.deal_product_id, 4, 'Daily') AS 'Fee_Covenant Modification Forbearance_4',
            dbo.fn_ERMProductFee(prod.deal_product_id, 5, 'Daily') AS 'Fee_Mortgage Assignment_5',
            dbo.fn_ERMProductFee(prod.deal_product_id, 6, 'Daily') AS 'Fee_Pre-Payment Penalty_6',
            dbo.fn_ERMProductFee(prod.deal_product_id, 7, 'Daily') AS 'Fee_Trustee Reconveyance_7',
            dbo.fn_ERMProductFee(prod.deal_product_id, 8, 'Daily') AS 'Fee_Asset Based Monitoring_8',
            dbo.fn_ERMProductFee(prod.deal_product_id, 9, 'Daily') AS 'Fee_Formula Based Monitoring_9',
            dbo.fn_ERMProductFee(prod.deal_product_id, 10, 'Daily') AS 'Fee_Special Asset Monitoring_10',
            dbo.fn_ERMProductFee(prod.deal_product_id, 11, 'Daily') AS 'Fee_Product Fee Deposits_11',
            dbo.fn_ERMProductFee(prod.deal_product_id, 12, 'Daily') AS 'Fee_Foreign Exchange (FX)_12',
            dbo.fn_ERMProductFee(prod.deal_product_id, 13, 'Daily') AS 'Fee_Product Fee (ATM)_13',
            dbo.fn_ERMProductFee(prod.deal_product_id, 14, 'Daily') AS 'Fee_Annual Fee (MTIG)_14',
            dbo.fn_ERMProductFee(prod.deal_product_id, 15, 'Daily') AS 'Fee_Placement Fee_15',
            dbo.fn_ERMProductFee(prod.deal_product_id, 16, 'Daily') AS 'Fee_Annual Fee (Trade Finance)_16',
            dbo.fn_ERMProductFee(prod.deal_product_id, 17, 'Daily') AS 'Fee_Merchant Srvcs VCM_17',
            dbo.fn_ERMProductFee(prod.deal_product_id, 18, 'Daily') AS 'Fee_Product Fee (TMA)_18',
            dbo.fn_ERMProductFee(prod.deal_product_id, 19, 'Daily') AS 'Fee_Product Fee (Generic)_19',
            dbo.fn_ERMProductFee(prod.deal_product_id, 20, 'Daily') AS 'Fee_Set Up Fees_20',
            dbo.fn_ERMProductFee(prod.deal_product_id, 22, 'Daily') AS 'Fee_Swap Fee_22',
            dbo.fn_ERMProductFee(prod.deal_product_id, 23, 'Daily') AS 'Fee_Remarketing Fee_23',
            dbo.fn_ERMProductFee(prod.deal_product_id, 24, 'Daily') AS 'Fee_Trustee Fee_24',
            dbo.fn_ERMProductFee(prod.deal_product_id, 25, 'Daily') AS 'Fee_Administrative Fee_25',
            dbo.fn_ERMProductFee(prod.deal_product_id, 26, 'Daily') AS 'Fee_Underwriting Fee_26',
            dbo.fn_ERMProductFee(prod.deal_product_id, 27, 'Daily') AS 'Fee_Corporate Advisory_27',
            dbo.fn_ERMProductFee(prod.deal_product_id, 28, 'Daily') AS 'Fee_Capital Raise_28',
            dbo.fn_ERMProductFee(prod.deal_product_id, 29, 'Daily') AS 'Fee_M&A Fee_29',
            dbo.fn_ERMProductFee(prod.deal_product_id, 30, 'Daily') AS 'Fee_Syndication/Participation Fee_30',
            dbo.fn_ERMProductFee(prod.deal_product_id, 31, 'Daily') AS 'Fee_BOLI Fee_31',
            dbo.fn_ERMProductFee(prod.deal_product_id, 32, 'Daily') AS 'Fee_Admin/Construction Monitoring_32',
            dbo.fn_ERMProductFee(prod.deal_product_id, 33, 'Daily') AS 'Fee_Annual Review of LOC - non rec_33',
            dbo.fn_ERMProductFee(prod.deal_product_id, 34, 'Daily') AS 'Fee_Application_34',
            dbo.fn_ERMProductFee(prod.deal_product_id, 35, 'Daily') AS 'Fee_Assignment of UCC Filings_35',
            dbo.fn_ERMProductFee(prod.deal_product_id, 36, 'Daily') AS 'Fee_Assumption of Morgage_36',
            dbo.fn_ERMProductFee(prod.deal_product_id, 37, 'Daily') AS 'Fee_Atorney Fees_37',
            dbo.fn_ERMProductFee(prod.deal_product_id, 38, 'Daily') AS 'Fee_Condo Unit/Lot Release_38',
            dbo.fn_ERMProductFee(prod.deal_product_id, 39, 'Daily') AS 'Fee_Copy of Amort. Schedule_39',
            dbo.fn_ERMProductFee(prod.deal_product_id, 40, 'Daily') AS 'Fee_Credit Reports_40',
            dbo.fn_ERMProductFee(prod.deal_product_id, 41, 'Daily') AS 'Fee_Deferred Principal_41',
            dbo.fn_ERMProductFee(prod.deal_product_id, 42, 'Daily') AS 'Fee_Discharge of Mrtg/Satisfaction_42',
            dbo.fn_ERMProductFee(prod.deal_product_id, 43, 'Daily') AS 'Fee_Discount_43',
            dbo.fn_ERMProductFee(prod.deal_product_id, 44, 'Daily') AS 'Fee_Doc. Prep (External)_44',
            dbo.fn_ERMProductFee(prod.deal_product_id, 45, 'Daily') AS 'Fee_Doc. Prep (Internal)_45',
            dbo.fn_ERMProductFee(prod.deal_product_id, 46, 'Daily') AS 'Fee_ELDP_46',
            dbo.fn_ERMProductFee(prod.deal_product_id, 47, 'Daily') AS 'Fee_Environmental Report_47',
            dbo.fn_ERMProductFee(prod.deal_product_id, 48, 'Daily') AS 'Fee_Escrow_48',
            dbo.fn_ERMProductFee(prod.deal_product_id, 49, 'Daily') AS 'Fee_External Appraisal_49',
            dbo.fn_ERMProductFee(prod.deal_product_id, 50, 'Daily') AS 'Fee_FARETS Fee (Tax Service)_50',
            dbo.fn_ERMProductFee(prod.deal_product_id, 51, 'Daily') AS 'Fee_Fee in Lieu of Comp. Balance_51',
            dbo.fn_ERMProductFee(prod.deal_product_id, 52, 'Daily') AS 'Fee_Flood Certification_52',
            dbo.fn_ERMProductFee(prod.deal_product_id, 53, 'Daily') AS 'Fee_Floor Plan Usage Fee_53',
            dbo.fn_ERMProductFee(prod.deal_product_id, 54, 'Daily') AS 'Fee_Formula Based Audit Fee_54',
            dbo.fn_ERMProductFee(prod.deal_product_id, 55, 'Daily') AS 'Fee_Formual Based Field Exam_55',
            dbo.fn_ERMProductFee(prod.deal_product_id, 56, 'Daily') AS 'Fee_Import - Acceptance_56',
            dbo.fn_ERMProductFee(prod.deal_product_id, 57, 'Daily') AS 'Fee_Import - Payment_57',
            dbo.fn_ERMProductFee(prod.deal_product_id, 58, 'Daily') AS 'Fee_Import - Amendment_58',
            dbo.fn_ERMProductFee(prod.deal_product_id, 59, 'Daily') AS 'Fee_Import - Issuance_59',
            dbo.fn_ERMProductFee(prod.deal_product_id, 60, 'Daily') AS 'Fee_Initial Review of LOC_60',
            dbo.fn_ERMProductFee(prod.deal_product_id, 61, 'Daily') AS 'Fee_Inspection - External_61',
            dbo.fn_ERMProductFee(prod.deal_product_id, 62, 'Daily') AS 'Fee_Inspection - Internal_62',
            dbo.fn_ERMProductFee(prod.deal_product_id, 63, 'Daily') AS 'Fee_Interest - Accrued_63',
            dbo.fn_ERMProductFee(prod.deal_product_id, 64, 'Daily') AS 'Fee_Interest - Interim_64',
            dbo.fn_ERMProductFee(prod.deal_product_id, 65, 'Daily') AS 'Fee_LIBOR Breakage_65',
            dbo.fn_ERMProductFee(prod.deal_product_id, 66, 'Daily') AS 'Fee_Modification Fee_66',
            dbo.fn_ERMProductFee(prod.deal_product_id, 67, 'Daily') AS 'Fee_Mortgage Recording_67',
            dbo.fn_ERMProductFee(prod.deal_product_id, 68, 'Daily') AS 'Fee_Mortgage Tax_68',
            dbo.fn_ERMProductFee(prod.deal_product_id, 69, 'Daily') AS 'Fee_NYSERDA_69',
            dbo.fn_ERMProductFee(prod.deal_product_id, 70, 'Daily') AS 'Fee_Origination_70',
            dbo.fn_ERMProductFee(prod.deal_product_id, 71, 'Daily') AS 'Fee_Partial Collateral Release_71',
            dbo.fn_ERMProductFee(prod.deal_product_id, 72, 'Daily') AS 'Fee_Payoff Quotes/Statement_72',
            dbo.fn_ERMProductFee(prod.deal_product_id, 73, 'Daily') AS 'Fee_Photo Copy/Fax/Mail Statements_73',
            dbo.fn_ERMProductFee(prod.deal_product_id, 74, 'Daily') AS 'Fee_Property Search/Stub Search_74',
            dbo.fn_ERMProductFee(prod.deal_product_id, 75, 'Daily') AS 'Fee_Proposal Letter_75',
            dbo.fn_ERMProductFee(prod.deal_product_id, 76, 'Daily') AS 'Fee_Rate Lock_76',
            dbo.fn_ERMProductFee(prod.deal_product_id, 77, 'Daily') AS 'Fee_Rate Lock Extension_77',
            dbo.fn_ERMProductFee(prod.deal_product_id, 78, 'Daily') AS 'Fee_Rate Lock Termination_78',
            dbo.fn_ERMProductFee(prod.deal_product_id, 79, 'Daily') AS 'Fee_SBA Guarantee_79',
            dbo.fn_ERMProductFee(prod.deal_product_id, 80, 'Daily') AS 'Fee_SBA Packaging_80',
            dbo.fn_ERMProductFee(prod.deal_product_id, 81, 'Daily') AS 'Fee_Standby - Amendment_81',
            dbo.fn_ERMProductFee(prod.deal_product_id, 82, 'Daily') AS 'Fee_Standby - Annual Commission_82',
            dbo.fn_ERMProductFee(prod.deal_product_id, 83, 'Daily') AS 'Fee_Standby - Application/Proposal_83',
            dbo.fn_ERMProductFee(prod.deal_product_id, 84, 'Daily') AS 'Fee_Standby - Conf. of M&T Issued_84',
            dbo.fn_ERMProductFee(prod.deal_product_id, 85, 'Daily') AS 'Fee_Standby - Issuance_85',
            dbo.fn_ERMProductFee(prod.deal_product_id, 86, 'Daily') AS 'Fee_Standby - Payment_86',
            dbo.fn_ERMProductFee(prod.deal_product_id, 87, 'Daily') AS 'Fee_Standby - Renewal/non-Renewal_87',
            dbo.fn_ERMProductFee(prod.deal_product_id, 88, 'Daily') AS 'Fee_Standby Issued by other bank_88',
            dbo.fn_ERMProductFee(prod.deal_product_id, 89, 'Daily') AS 'Fee_Title Insurance_89',
            dbo.fn_ERMProductFee(prod.deal_product_id, 90, 'Daily') AS 'Fee_Title Search_90',
            dbo.fn_ERMProductFee(prod.deal_product_id, 91, 'Daily') AS 'Fee_UCC Continuation_91',
            dbo.fn_ERMProductFee(prod.deal_product_id, 92, 'Daily') AS 'Fee_UCC Filing_92',
            dbo.fn_ERMProductFee(prod.deal_product_id, 93, 'Daily') AS 'Fee_UCC Search_93',
            dbo.fn_ERMProductFee(prod.deal_product_id, 94, 'Daily') AS 'Fee_Unused Fee_94',
            dbo.fn_ERMProductFee(prod.deal_product_id, 95, 'Daily') AS 'Fee_Usage Fee_95',
            dbo.fn_ERMProductFee(prod.deal_product_id, 96, 'Daily') AS 'Fee_Valuation Review/Underwriting_96',
            dbo.fn_ERMProductFee(prod.deal_product_id, 98, 'Daily') AS 'Fee_Vehicle Lien Rcrdng/Filing/Rls_98',
            dbo.fn_ERMProductFee(prod.deal_product_id, 99, 'Daily') AS 'Fee_Foreign Wires Fee_99',
            dbo.fn_ERMProductFee(prod.deal_product_id, 100, 'Daily') AS 'Fee_Foreign Draft Issuance Fees_100',
            dbo.fn_ERMProductFee(prod.deal_product_id, 101, 'Daily') AS 'Fee_FHA Multi Family Fees_101',
            dbo.fn_ERMProductFee(prod.deal_product_id, 102, 'Daily') AS 'Fee_Sec Market / Conduit Life Fees_102',
            dbo.fn_ERMProductFee(prod.deal_product_id, 103, 'Daily') AS 'Fee_Lease Service/Modification Fee_103',
            dbo.fn_ERMProductFee(prod.deal_product_id, 104, 'Daily') AS 'Fee_Merchant Annual Fee_104',
            dbo.fn_ERMProductFee(prod.deal_product_id, 105, 'Daily') AS 'Fee_BALOC_Annual_Fee_105',
            dbo.fn_ERMProductFee(prod.deal_product_id, 106, 'Daily') AS 'Fee_Lease_Admin_Fee_106',
            dbo.fn_ERMProductFeeTotal(prod.deal_product_id, 'Daily') AS 'Total_Fees'


FROM            ERM.dbo.DEAL_PRODUCT prod                                                                  -- Product table
            INNER JOIN
                  ERM.dbo.DEAL deal                                                                              -- Deal table
                  ON prod.deal_id = deal.deal_id
            INNER JOIN
                  ERM.dbo.PRODUCT_FAMILY prodfam                                                            -- Product Family associated with the deal NOT the product level
                  ON deal.product_family_id = prodfam.product_family_id      
            INNER JOIN
                  ERM.dbo.SALES_OPPORTUNITY so                                                            -- Sales Opportunity table
                  ON deal.opportunity_id = so.opportunity_id
            LEFT OUTER JOIN
                  ERM.dbo.CAMPAIGN camp                                                                        -- Campaign Lookup table
                  ON so.campaign_id = camp.campaign_id
            INNER JOIN
                  [Commercial_Data_Store].dbo.CDS_ts_ERMD_Profile_Details profdet            -- View of Profile Details - filters out test profiles
                  ON  so.profile_id = profdet.ERM_PN
            INNER JOIN
                  [Commercial_Data_Store].dbo.CDS_v_ERMD_Product_Item_Details proditm      -- Product item lookup table
                  ON prod.product_id = proditm.product_id
            INNER JOIN
                  ERM.dbo.DISPOSITION_REASON disprsn                                                      -- Disposition Reason
                  ON prod.disposition_reason_id = disprsn.disposition_reason_id
            INNER JOIN
                  ERM.dbo.AWARDED_COMPANY awrdcomp                                                      -- Company Awarded to
                  ON prod.awarded_company_id = awrdcomp.awarded_company_id
            INNER JOIN
                  ERM.dbo.ACTION_TYPE actn                                                                  -- Action lookup table
                  ON prod.action_id = actn.action_id


WHERE            prod.disposition_status_flag = 1            -- Only for products disposed (closed)
            AND      so.delete_flag = 0                                    -- No removed sales opportunities
            AND      deal.delete_flag = 0                              -- No removed deals (sales efforts)
            AND      prod.delete_flag = 0                              -- No removed products
            AND      prod.dispose_date >= @RollingStartDate      -- Only products which have been closed in past 5 years since Jan 1 of current year

END            -- End of Stored Procedure

the table does in fact have indexes, what is the easiest way to drop these and insert these back via sql ? To give a little more background this SP run time went from 30 minutes to 6 hours in the last couple of days. It seems to be imtermitten with no real probably cause ( but I am not DBA)

Can you run the select query alone on a new query window and let me know how long its gonna take it to return the data. seems like your actual query has to be tuned up

I am running it now.

The query itself is running slowly, regardless of the insert. We took the insert out.

notice that we are using a lot of functions, should we be using update statements? well we are calling 2 distinct functions 50 times for each record. Is there a more efficient way to deal with this?

>Is there a more efficient way to deal with this?
You can paste those functions here, we will try to optimize them.
user defined functions are slower as the execution plans are not stored in the memory, if you have alternative ways of doing it, you need to try them

DECLARE @ResultVal nvarchar(200)

SET @ResultVal =
      CASE WHEN @DB = 'Monthly' THEN
                        (SELECT            ISNULL(      (      SELECT      enum.pia_val
                                                            FROM      ERM_MONTHLY.dbo.PIA_ENUMURATION enum
                                                            WHERE      enum.attribute_enumuration_id = prod_pia.attribute_enumuration_id ), prod_pia.pia_val
                                                      )
                        FROM            ERM_MONTHLY.dbo.DEAL_PRODUCT_PIA prod_pia
      
                        WHERE            prod_pia.deal_product_id = @deal_product_id AND
                                          prod_pia.action_id = @action_id AND
                                          prod_pia.pia_id = @pia_id)
                                          
            WHEN @DB = 'Weekly' THEN
                        (SELECT            ISNULL(      (      SELECT      enum.pia_val
                                                            FROM      ERM_WEEKLY.dbo.PIA_ENUMURATION enum
                                                            WHERE      enum.attribute_enumuration_id = prod_pia.attribute_enumuration_id ), prod_pia.pia_val
                                                      )
                        FROM            ERM_WEEKLY.dbo.DEAL_PRODUCT_PIA prod_pia
      
                        WHERE            prod_pia.deal_product_id = @deal_product_id AND
                                          prod_pia.action_id = @action_id AND
                                          prod_pia.pia_id = @pia_id)

            WHEN @DB = 'Daily' THEN
                        (SELECT            ISNULL(      (      SELECT      enum.pia_val
                                                            FROM      ERM.dbo.PIA_ENUMURATION enum
                                                            WHERE      enum.attribute_enumuration_id = prod_pia.attribute_enumuration_id ), prod_pia.pia_val
                                                      )
                        FROM            ERM.dbo.DEAL_PRODUCT_PIA prod_pia
      
                        WHERE            prod_pia.deal_product_id = @deal_product_id AND
                                          prod_pia.action_id = @action_id AND
                                          prod_pia.pia_id = @pia_id)

DECLARE @ResultVal numeric(16,3)

SET @ResultVal =
      CASE WHEN @DB = 'Monthly'THEN
                        (      SELECT            fee.fee_amount

                              FROM            ERM_MONTHLY.dbo.DEAL_PRODUCT_SUBPRODUCT_FEE fee
      
                              WHERE            fee.deal_product_id = @deal_product_id AND
                                                fee.sub_product_id = @Fee_Id
                        )

            WHEN @DB = 'Weekly' THEN
                        (      SELECT            fee.fee_amount

                              FROM            ERM_WEEKLY.dbo.DEAL_PRODUCT_SUBPRODUCT_FEE fee
      
                              WHERE            fee.deal_product_id = @deal_product_id AND
                                                fee.sub_product_id = @Fee_Id
                        )
                        
            WHEN @DB = 'Daily' THEN
                        (      SELECT            fee.fee_amount

                              FROM            ERM.dbo.DEAL_PRODUCT_SUBPRODUCT_FEE fee
      
                              WHERE            fee.deal_product_id = @deal_product_id AND
                                                fee.sub_product_id = @Fee_Id
                        )
                                                
      END  -- End Case Statement

RETURN @ResultVal

both functions are posted here, yes we are in agreement that because the execution plans are not stored that this is a problem. It is a a little strange that this is just happening now when it was fine before . Maybe we hit a tipping point so to speak.

Maybe we hit a tipping point so to speak.
That is exactly what has happened.

Here is what you should do.
1. Make sure the stats are updated for all tables involved.
2. Defrag all indexes.
3. Make sure you have appropriate indexes on all the tables (if you are not sure we can elaborate)
4.  If you are still getting lousy performance, consider executing in batches.

I think a CROSS APPLY could help performance tremendously here.

I don't have time to write out every lookup value -- you'll have to add the others yourself -- but hopefully you can get the gist of my approach from below.

The same thing can (and in my view probably should) be done for the ERMProductFees:


SELECT      ...,
-- get all PIA details for each product
...
    ERMPIAValues.'pia_2nd_Lien?',
    ERMPIAValues.'pia_Account_Number',
    ERMPIAValues.'pia_Actual_Interest_Rate',
    ERMPIAValues.'pia_Adj_Variable_Contribution',
            /********************************************************/
            /* Use Actual_Interest_Rate from ePF. It accounts for       */
            /* all ePF logic. If NULL then use what has been typed      */
            /* into ERM.                                                                  */
            /********************************************************/
    ISNULL(ERMPIAValues.'pia_Actual_Interest_Rate', ERMPIAValues.'pia_All_In_Rate') AS 'pia_All_In_Rate',
    ERMPIAValues.'pia_All_In_Rate_Daily' AS 'pia_All_In_Rate_In_ERM',
    ...
FROM
INNER JOIN ...
...
CROSS APPLY (
    SELECT        
        MAX(CASE WHEN prod_pia.pia_id = 1 THEN CONVERT(bit, ISNULL(enum.pia_val, prod_pia.pia_val)) END)
            AS 'pia_2nd_Lien?',
        MAX(CASE WHEN prod_pia.pia_id = 18 THEN CONVERT(numeric (19, 6), ISNULL(enum.pia_val, prod_pia.pia_val)) END)
            AS 'pia_Adj_Variable_Contribution',
        MAX(CASE WHEN prod_pia.pia_id = 20 THEN CONVERT(numeric (9, 5), ISNULL(enum.pia_val, prod_pia.pia_val)) END)
            AS 'pia_All_In_Rate',
        MAX(CASE WHEN prod_pia.pia_id = 24 THEN CONVERT(varchar(100), ISNULL(enum.pia_val, prod_pia.pia_val)) END)
            AS 'pia_Account_Number',
        MAX(CASE WHEN prod_pia.pia_id = 27 THEN CONVERT(numeric (19, 6), ISNULL(enum.pia_val, prod_pia.pia_val)) END)
            AS 'pia_Assets',
        MAX(CASE WHEN prod_pia.pia_id = 59 THEN CONVERT(numeric(9,5), ISNULL(enum.pia_val, prod_pia.pia_val)) END)
            AS 'pia_Actual_Interest_Rate',
        MAX(CASE WHEN prod_pia.pia_id = 77 THEN CONVERT(varchar(100), ISNULL(enum.pia_val, prod_pia.pia_val)) END)
            AS 'pia_Auto_Line_Type'
    FROM ERM.dbo.DEAL_PRODUCT_PIA prod_pia
    LEFT OUTER JOIN ERM.dbo.PIA_ENUMURATION enum ON
        enum.attribute_enumuration_id = prod_pia.attribute_enumuration_id
    WHERE
        prod_pia.deal_product_id = prod.deal_product_id AND
        prod_pia.action_id = prod.action_id        
) AS ERMPIAValues