Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 increase the perfomnce of a fnction of 10mt durtn

Author  Topic 

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 below

USE [gl_gems_reconciliation]
GO
/****** Object: UserDefinedFunction [dbo].[fn_rec_gl_exception] Script Date: 08/06/2013 13:41:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
*/

CREATE FUNCTION [dbo].[fn_rec_gl_exception](
@in_start_date datetime
,@rec_type d_reconciliation_type

) RETURNS TABLE
AS
RETURN(
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
)
GO
EXEC sys.sp_addextendedproperty @name=N'version', @value=20130125 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_rec_gl_exception'
GO



USE [gl_gems_reconciliation]
GO
/****** Object: UserDefinedFunction [dbo].[fn_rec_gl_balance_exclusion] Script Date: 08/06/2013 13:45:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE FUNCTION [dbo].[fn_rec_gl_balance_exclusion](
@in_start_date datetime
,@in_reporting_type d_reconciliation_type

) RETURNS TABLE
AS
RETURN (
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

)



GO

EXEC sys.sp_addextendedproperty @name=N'version', @value=20130225 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'fn_rec_gl_balance_exclusion'
GO


Thanks
Sulfikkar BS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 23:58:43
You may be better off explaining what it doesnt giving some sample data and output from it. Otherwise I doubt whether people will go through all the posted code and try to find the logic. Its quite hard to understand logic without comments etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-06 : 16:50:57
If you make the end_date a terminus date (9999-12-31) instead of NULL, then your predicates will be sargable. Given what you've posted, I'd guess you are table scanning on every table.
Go to Top of Page

sivadss2007
Starting Member

18 Posts

Posted - 2013-08-28 : 10:26:01
You try using 'option(recomplie)'

P.Siva
Go to Top of Page
   

- Advertisement -