CHROMIUM

Welcome to CHROMIUM's very own blog!

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 (21) times.

Leave a Reply

Your email address will not be published. Required fields are marked *