October 7, 2008 at 3:29 pm
I have table of raw data (1 million rows) in the following format
CREATE TABLE [TKCSDB].[dbo].[Z_VP_RACCRUALDETAIL] (
[PERSONNUM] nvarchar(20) ,
[ACCRUALCODENAME] nvarchar(20) ,
[EFFECTIVEDATE] datetime,
[AMOUNT] decimal(16,6)
)
I have the following final output table. PersonNum and Division are already filled and I am trying to calculate the amounts. PersonNum is unique. There are 50,000 rows in it.
CREATE TABLE [TKCSDB].[dbo].[ZALL_RACCRUALDETAIL] (
[DIVISION] nvarchar(4) NOT NULL,
[PERSONNUM] nvarchar(15) NOT NULL,
[AMOUNT1] decimal(16,6),
[AMOUNT2] decimal(16,6),
[AMOUNTa1] decimal(16,6),
[AMOUNTa2] decimal(16,6),
[AMOUNTa3] decimal(16,6),
[AMOUNT3] decimal(16,6),
[AMOUNT4] decimal(16,6),
[AMOUNT5] decimal(16,6),
[AMOUNT6] decimal(16,6),
[AMOUNT7] decimal(16,6),
[AMOUNT8] decimal(16,6),
[AMOUNT9] decimal(16,6),
[RecalcDATE] datetime,
[SOURCEFLAG] nvarchar(1)
)
Its taking long time (68 hours) when I execute the following stored procedure (there are total of 9 procs: 1 each for an amount). Is there a way I can improve the procedure? Appreciate your suggestions.
CREATE PROCEDURE usp_z_Test1
as
DECLARE @colSSN nvarchar(10)
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select PERSONNUM
From ZALL__RACCRUALDETAIL
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColSSN
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @ColSSN
update ZALL_RACCRUALDETAIL
set AMOUNT1 =
(
SELECT AMOUNT/3600
FROM Z_VP_RACCRUALDETAIL
WHERE
(PERSONNUM = @ColSSN)
AND ((ACCRUALCODENAME = 'Vacation') )
AND
EFFECTIVEDATE =
(
select max(EFFECTIVEDATE) FROM Z_VP_RACCRUALDETAIL
WHERE (PERSONNUM = @ColSSN )
AND ((ACCRUALCODENAME = 'Vacation') )
)
)
where PERSONNUM = @ColSSN
FETCH NEXT FROM @MyCursor
INTO @ColSSN
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
October 7, 2008 at 3:42 pm
If you want to improve performance - ditch the cursor altogether. The overhead this has to be generating (with redundant calls against the SAME table over and over again) is killing your server and its performance.
Try this on for size (note - this is not TESTED, since you don't have any test data to supply. Test this carefully).
;with VacationCTE as (
select PersonNum,
effectivedate,
amount/3600 VacaAmt,
ROW_NUMBER() over (partion by PersonNum Order by EffectiveDate DESC) RN
from Z_VP_RACCRUALDETAIL)
update ZALL_RACCRUALDETAIL
set amount1=VacationCTE.VacaAmt
from ZALL_RACCRUALDETAIL inner join VacationCTE
on ZALL_RACCRUALDETAIL.personNum=VacationCTE.personNum
where VacationCTE.RN=1
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 8, 2008 at 9:02 am
Thanks So much Matt. The query worked with excellent performance. 🙂
Krish.
October 8, 2008 at 9:21 am
Great - glad it helped!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply