Avoid functions returning tables
In queries which returns large data set don’t use table return type function in main query join part. It contribute in increasing of query execution time. The solution is to make the temporary table from the function and created the index on joining fields with main query. This improve the significant time of main query execution. This was experienced while optimizing MBAFC procedure SP_dayend_adhoc_direct_debit. That reduce 28 minutes in procedure execution. See below about changes in query. Further the temp table was global temporary table and its output was used in multiple procedure where it was required which further help to improve the performance.
=====
Before
=====
In main query join with view
LEFT JOIN (
SELECT CONTRACT_ID, SUM(GROSS_AMT) AS ACC_OVERDUE_INT
FROM DBO.FN_DE_COS_OVERDUE(NULL,’D’,NULL)
WHERE AMT_DSC = ‘Accrued’
GROUP BY CONTRACT_ID
)  ACC_AMT ON ACC_AMT.CONTRACT_ID = C.CONTRACT_ID
=====
After
=====
CREATE TABLE ##OVERDUE_INTEREST
(
contract_id      INT
, external_contract_nbr VARCHAR(100)
, agreement_seq    INT
, business_partner_id INT
,  rental_id      INT
, cmp_receivable_amt  DECIMAL(18,5)
,  od_interest_rte   DECIMAL(18,5)
, amt_dsc       VARCHAR(50)
,  accrued_od_amt    DECIMAL(18,5)
, tax_amt       DECIMAL(18,5)
, gross_amt     DECIMAL(18,5)
,  from_dte      DATETIME
,  to_dte        DATETIME
, date_diff     INT
, overdue_seq     INT
)
CREATE INDEX IDX_GLOBAL_OD_INT ON ##OVERDUE_INTEREST(CONTRACT_ID, RENTAL_ID, BUSINESS_PARTNER_ID)
INSERT INTO ##OVERDUE_INTEREST SELECT * FROM DBO.FN_DE_COS_OVERDUE(NULL, ‘D’, NULL)
LEFT JOIN (
SELECT CONTRACT_ID, SUM(GROSS_AMT) AS ACC_OVERDUE_INT
FROM ##OVERDUE_INTEREST
WHERE AMT_DSC = ‘Accrued’
GROUP BY CONTRACT_ID
)  ACC_AMT ON ACC_AMT.CONTRACT_ID = C.CONTRACT_ID
This post was viewed (29) times.
Fasting incentive for Muneeb Kayani Performance Targets and Indicators