bssulfikkar
Starting Member
1 Post |
Posted - 2013-08-05 : 23:47:13
|
I have the below function,can anyone help me to improve the perfomance of the function.This functions another function which is also included as belowUSE [gl_gems_reconciliation]GO/****** Object: UserDefinedFunction [dbo].[fn_rec_gl_exception] Script Date: 08/06/2013 13:41:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGO*/CREATE FUNCTION [dbo].[fn_rec_gl_exception]( @in_start_date datetime ,@rec_type d_reconciliation_type ) RETURNS TABLEASRETURN( SELECT glException.gl_balance_id , glException.gl_balance_desc , rrc.risk_category_desc , glException.gl_acc_category_id , rgc.gl_category_desc as gl_acc_category_desc , glException.gl_prod_category_id , [rpc].gl_category_desc as gl_prod_category_desc , glException.balance , dept_i , aloc_dept_i FROM ( SELECT trgab.gl_balance_id , gl_balance_desc , risk_category_id , gl_acc_category_id , gl_prod_category_id , balance , dept_i , aloc_dept_i FROM dbo.fn_rec_gl_balance_after_exclusion (@in_start_date, @rec_type) trgab LEFT JOIN dbo.rec_gl_rec_group_mapping rGlRecGroupMap ON trgab.gl_balance_id = rGlRecGroupMap.gl_balance_id AND rGlRecGroupMap.start_date <= @in_start_date AND (rGlRecGroupMap.end_date IS NULL OR rGlRecGroupMap.end_date > @in_start_date) WHERE rGlRecGroupMap.gl_balance_id IS NULL ) glException LEFT JOIN dbo.rec_risk_category rrc ON glException.risk_category_id = rrc.risk_category_id AND rrc.start_date <= @in_start_date LEFT JOIN dbo.rec_gl_acc_category rgc ON glException.gl_acc_category_id = rgc.gl_acc_category_id AND rgc.start_date <= @in_start_date LEFT JOIN dbo.rec_gl_prod_category [rpc] ON glException.gl_prod_category_id = [rpc].gl_prod_category_id AND [rpc].start_date <= @in_start_date )GOEXEC sys.sp_addextendedproperty @name=N'version', @value=20130125 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_rec_gl_exception'GOUSE [gl_gems_reconciliation]GO/****** Object: UserDefinedFunction [dbo].[fn_rec_gl_balance_exclusion] Script Date: 08/06/2013 13:45:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOCREATE FUNCTION [dbo].[fn_rec_gl_balance_exclusion]( @in_start_date datetime ,@in_reporting_type d_reconciliation_type ) RETURNS TABLEASRETURN ( SELECT exl_ones.gl_balance_id, exl_ones.gl_balance_desc, ec.report_status, ec.exclusion_desc, ec.exclusion_reason, exl_ones.gl_acc_category_id, exl_ones.gl_category_desc as gl_acc_category_desc, exl_ones.gl_prod_category_id, exl_ones.gl_prod_category_desc, exl_ones.risk_category_desc, exl_ones.account_desc, exl_ones.product_desc, sum(exl_ones.balance) as balance, exl_ones.country, exl_ones.hfm_schedule_id, exl_ones.ttl_exclusion_code as exclusion_code, exl_ones.borrower_entity_id, exl_ones.balance_period_date, exl_ones.entity_id, exl_ones.dept_i, exl_ones.aloc_dept_i FROM ( SELECT gab.gl_balance_id, gab.gl_balance_desc, gab.account_id, gab.account_desc, gab.product_id, gab.product_desc, gab.entity_id, gab.combi_no, rc.risk_category_id, gab.gl_acc_category_id, gab.gl_prod_category_id, pgc.gl_category_desc as gl_prod_category_desc, gab.balance, gab.hfm_schedule_id, gc.gl_category_desc, rc.risk_category_desc, ttl_exclusion_code = CASE WHEN gab.exclusion_code IS NOT NULL THEN gab.exclusion_code WHEN rce.exclusion_code IS NOT NULL THEN rce.exclusion_code WHEN gce.exclusion_code IS NOT NULL THEN gce.exclusion_code WHEN gbe.exclusion_code IS NOT NULL THEN gbe.exclusion_code END, gab.country, gab.borrower_entity_id, gab.balance_period_date, gab.dept_i, gab.aloc_dept_i FROM ( SELECT gl_balance_id, gl_balance_desc, account_id, account_desc, product_id, product_desc, entity_id, combi_no, gl_acc_category_id, gl_prod_category_id, balance, hfm_schedule_id, NULL as exclusion_code, balance_period_date, country, borrower_entity_id, dept_i, aloc_dept_i FROM dbo.fn_rec_gl_asset_balances(@in_start_date, @in_reporting_type) UNION ALL SELECT gl_balance_id, gl_balance_desc, account_id, account_desc, product_id, product_desc, entity_id, combi_no, gl_acc_category_id, gl_prod_category_id, balance, hfm_schedule_id, exclusion_code, balance_period_date, country, borrower_entity_id, dept_i, aloc_dept_i FROM dbo.fn_rec_gl_asset_balances_entity_exclusion(@in_start_date, @in_reporting_type) ) gab LEFT JOIN dbo.rec_risk_category_mapping AS rcm ON rcm.gl_balance_id = gab.gl_balance_id AND rcm.start_date <= @in_start_date AND (rcm.end_date > @in_start_date OR rcm.end_date IS NULL) LEFT JOIN dbo.rec_risk_category as rc ON rc.risk_category_id = rcm.risk_category_id AND rc.start_date <= @in_start_date LEFT JOIN dbo.rec_risk_category_exclusion AS rce ON rce.risk_category_id = rc.risk_category_id AND rce.start_date <= @in_start_date AND (rce.end_date > @in_start_date OR rce.end_date IS NULL) LEFT JOIN dbo.rec_gl_acc_category AS gc ON gc.gl_acc_category_id = gab.gl_acc_category_id AND gc.start_date <= @in_start_date LEFT JOIN dbo.rec_gl_acc_category_exclusion AS gce ON gce.gl_acc_category_id = gc.gl_acc_category_id AND gce.start_date <= @in_start_date AND (gce.end_date > @in_start_date OR gce.end_date IS NULL) LEFT JOIN dbo.rec_gl_balance_exclusion AS gbe ON gbe.gl_balance_id = gab.gl_balance_id AND gbe.start_date <= @in_start_date AND (gbe.end_date > @in_start_date OR gbe.end_date IS NULL) LEFT JOIN dbo.rec_gl_prod_category AS pgc ON pgc.gl_prod_category_id = gab.gl_prod_category_id AND pgc.start_date <= @in_start_date WHERE ( gab.exclusion_code IS NOT NULL OR rce.risk_category_id IS NOT NULL OR gce.gl_acc_category_id IS NOT NULL OR gbe.exclusion_code IS NOT NULL) AND gab.balance_period_date = @in_start_date ) AS exl_ones JOIN rec_exclusion_code AS ec ON ec.exclusion_code = exl_ones.ttl_exclusion_code AND ec.start_date < = @in_start_date AND (ec.end_date IS NULL OR ec.end_date > @in_start_date) GROUP BY ec.report_status, ec.exclusion_desc, ec.exclusion_reason, exl_ones.gl_category_desc, exl_ones.gl_acc_category_id,exl_ones.gl_prod_category_id,exl_ones.gl_prod_category_desc, exl_ones.risk_category_desc, exl_ones.gl_balance_id,exl_ones.gl_balance_desc, exl_ones.account_desc, exl_ones.product_desc,exl_ones.country, exl_ones.hfm_schedule_id,exl_ones.ttl_exclusion_code, exl_ones.borrower_entity_id,exl_ones.balance_period_date,exl_ones.entity_id,exl_ones.dept_i, exl_ones.aloc_dept_i )GOEXEC sys.sp_addextendedproperty @name=N'version', @value=20130225 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_rec_gl_balance_exclusion'GOThanksSulfikkar BS |
|