Query tunning - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Query tunning

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-08 : 09:01:31

Dear All,
I am running the below stored proc which is taking long time nearly 15 Hours of time
Can you pls help me how to tune the Stored proc or any issue with this stored procedure ??

create PROC [DBO].[USP_LOAD_DAILY_FINANCIAL_SNAPSHOT_NEW]
AS
BEGIN
SET NOCOUNT OFF

DECLARE @CUR_POSTING_DAY VARCHAR(12)
SET @CUR_POSTING_DAY = (SELECT MAX(CURRENTPERIOD) FROM GROUP_PERIOD_STATUS)
CREATE TABLE #CONFIG
(
CONFIGTYPE VARCHAR(50),
CONFIGVALUE TINYINT,
CONFIGFACTOR INT
)

INSERT INTO #CONFIG(CONFIGTYPE,CONFIGVALUE,CONFIGFACTOR)
SELECT 'CHARGES',1,1 UNION
SELECT 'PAYMENTS',2,1 UNION
SELECT 'REFUNDS',6,1

TRUNCATE TABLE OWA_DAILY_FINANCIAL_SNAPSHOT_SSC_LOAD

INSERT OWA_DAILY_FINANCIAL_SNAPSHOT_SSC_LOAD
(PROVIDER_SK
,GROUP_NUMBER
,DIVISION_NUMBER
,BILLINGAREA_NUMBER
,CATEGORY
,MONTH1
,MONTH2
,MONTH3
,MONTH4
,MONTH5
,MONTH6
,MONTH7
,REPORT_DATE
)
SELECT P.PROVIDER_SK
,P.GROUP_NUMBER
,P.DIVISION_NUMBER
,P.BILLINGAREA_NUMBER
,C.CONFIGTYPE CATEGORY
,CASE WHEN F.POSTING_PERIOD = G.CURRENTPERIOD THEN F.AMOUNT ELSE NULL END MONTH1
,CASE WHEN F.POSTING_PERIOD = DBO.POSTING_PERIOD(G.CURRENTPERIOD, -1) THEN F.AMOUNT ELSE NULL END MONTH2
,CASE WHEN F.POSTING_PERIOD = DBO.POSTING_PERIOD(G.CURRENTPERIOD, -2) THEN F.AMOUNT ELSE NULL END MONTH3
,CASE WHEN F.POSTING_PERIOD = DBO.POSTING_PERIOD(G.CURRENTPERIOD, -3) THEN F.AMOUNT ELSE NULL END MONTH4
,CASE WHEN F.POSTING_PERIOD = DBO.POSTING_PERIOD(G.CURRENTPERIOD, -4) THEN F.AMOUNT ELSE NULL END MONTH5
,CASE WHEN F.POSTING_PERIOD = DBO.POSTING_PERIOD(G.CURRENTPERIOD, -5) THEN F.AMOUNT ELSE NULL END MONTH6
,CASE WHEN F.POSTING_PERIOD = DBO.POSTING_PERIOD(G.CURRENTPERIOD, -6) THEN F.AMOUNT ELSE NULL END MONTH7
,@CUR_POSTING_DAY REPORT_DATE
FROM FACT_PAY_CODE F
INNER JOIN GROUP_PERIOD_STATUS G
ON F.GROUP_NUMBER = G.GROUPNUMBER
AND F.POSTING_PERIOD <= G.CURRENTPERIOD
AND F.POSTING_PERIOD >= DBO.POSTING_PERIOD(G.CURRENTPERIOD, -6)
INNER JOIN PROVIDER_DIMENSION P
ON P.GROUP_NUMBER = F.GROUP_NUMBER
INNER JOIN #CONFIG C
ON F.CALCULATED_PAYMENT_CAT_ID = C.CONFIGVALUE

DROP TABLE #CONFIG
END
SET NOCOUNT ON

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-08 : 09:39:30

What does the DBO.POSTING_PERIOD function do?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-08 : 10:08:01

The DBO.POSTING_PERIOD function returns the Data in the format 200910 means yeardate depending on the number provided ,for -2 it will return last 2 months data i.e 200910 for -3 -> it returns last 3 month old i.e .. 200909.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-08 : 10:31:03

My first try would be to factor that function out somehow, since the function will be being called several times for every row you insert.

Can you create some kind of temporary (or permanent) periods table and join to that instead? I would have a stab myself, but I've got flu today and can't think straight!

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 10:34:01

I'm not sure if this will help, but try replacing the function with this...
so...DBO.POSTING_PERIOD(G.CURRENTPERIOD, -6) will be

cast(convert(varchar(6),dateadd(month,-6,G.CURRENTPERIOD),112) as int)

I guess it should do the same thing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources