cursors
-
I currently have a DTS Script Task that creates and opens a cursor that passes 4 variables into it, and modifies information on a table based on those variables. However, it takes forever to run and I'm not sure why.
My SQL Statement that declares the cursor sometimes only pulls back two different records so I'm not fetching much info into the cursor. Is there another way I can do this without using a cursor? (Passing certain records in one by one and updating tables based on those records)
The format is as follows
declare @glov_id varchar(10)
declare @ult_id varchar(10)
declare @diff decimal(10,2)
declare @pweek datetime
declare @emp varchar(6)
declare @Ttl_Hrs decimal(10,2)
declare @Ttl_ProHrs decimal(10,2)
DECLARE mod1 CURSOR
FOR SELECT EMP_NUM, PAY_WEEK, sum(Ult_ProHrs) as Ttl_ProHrs, sum(Hours) as Ttl_Hrs FROM TABLEA WHERE SUBMIT = 'Y' GROUP BY EMP_NUM, PAY_WEEK HAVING SUM(ULT_PROHRS) > 40
OPEN mod1
FETCH NEXT FROM mod1 into @emp, @pweek, @Ttl_ProHrs, @Ttl_Hrs
WHILE (@@FETCH_STATUS = 0)
BEGIN
declare @ult_id varchar(10)
declare @diff decimal(10,2)
declare @pweek datetime
declare @emp varchar(6)
declare @Ttl_Hrs decimal(10,2)
declare @Ttl_ProHrs decimal(10,2)
if (@Ttl_ProHrs <> 40)
BEGIN
Set @ult_id = (Select max(c_dktime_id) from TABLEA Where earn_code in ('0001D','0001I')and Emp_num = @emp and pay_week = @pweek and pull_date is null and submit = 'Y' )
END
If (@ult_id <> '' ) --contains work hours
BEGIN
iF (@Ttl_ProHrs > 40)
BEGIN
Set @diff = @Ttl_ProHrs - 40
update TABLEA
SET ULT_PROHRS = Ult_ProHrs - @diff
where ID= @ult_id and SEQ = Th'00'
END
Else
BEGIN
Set @diff = 40 - @Ttl_ProHrs
update TABLEA
SET ULT_PROHRS = Ult_ProHrs + @diff, ULT_ADJ = 'Y'
where ID = @ult_id and SEQ = '00'
END
END
--print 'not null'
END
FETCH NEXT FROM mod_pro_hrs into @emp, @pweek, @Ttl_ProHrs, @Ttl_Hrs
END -- end cursor
CLOSE mod_pro_hrs
DEALLOCATE mod_pro_hrs
Any help is appreciated!
Thanks, Jill
-
HI Jill
I think that I would find a different way to write that. And never, ever use a cursor in SQL. They take up way too must time and memory on the server. Very hard on the database also.
Sorry, but I am certain that is not what you wanted to hear. I wish that I had good news and an answer for you.
Andrew
-
Shoot...
Off the top of your head, do you know another way in SQL loop through a result set and perform operations?? The Cursor was the only thing I can think of to fetch each record one by one and do an update based on that result.
Thanks, Jill
-
Frist let's verify what you're trying to do here. I made notes after the issues.
declare @glov_id varchar(10)
declare @ult_id varchar(10)
declare @diff decimal(10,2)
declare @pweek datetime
declare @emp varchar(6)
declare @Ttl_Hrs decimal(10,2)
declare @Ttl_ProHrs decimal(10,2)
DECLARE mod1 CURSOR
FOR SELECT EMP_NUM, PAY_WEEK, sum(Ult_ProHrs) as Ttl_ProHrs, sum(Hours) as Ttl_Hrs FROM TABLEA WHERE SUBMIT = 'Y' GROUP BY EMP_NUM, PAY_WEEK HAVING SUM(ULT_PROHRS) > 40 -- This means only records that have more than 40 hours, should this have been <> or !=
OPEN mod1
FETCH NEXT FROM mod1 into @emp, @pweek, @Ttl_ProHrs, @Ttl_Hrs
WHILE (@@FETCH_STATUS = 0)
BEGIN
declare @ult_id varchar(10)
declare @diff decimal(10,2)
declare @pweek datetime
declare @emp varchar(6)
declare @Ttl_Hrs decimal(10,2)
declare @Ttl_ProHrs decimal(10,2)
if (@Ttl_ProHrs <> 40) -- This would not be needed becuase HAVING SUM(ULT_PROHRS) > 40 means all would have more than 40.
BEGIN
Set @ult_id = ( Select max(c_dktime_id) from TABLEA Where earn_code in ('0001D','0001I')and Emp_num = @emp and pay_week = @pweek and pull_date is null and submit = 'Y' )
END
If (@ult_id <> '' ) --contains work hours
BEGIN
iF (@Ttl_ProHrs > 40)
BEGIN
--Set @diff = @Ttl_ProHrs - 40 --Don't bother with this, set in calculation and save memory from object.
update TABLEA
SET ULT_PROHRS = Ult_ProHrs - (@Ttl_ProHrs - 40)
where ID= @ult_id and SEQ = Th'00' -- Not sure what the TH thing is about, can you correct please.
END
Else -- Again becuase of HAVING SUM(ULT_PROHRS) > 40 this would never occurr.
BEGIN
--Set @diff = 40 - @Ttl_ProHrs --Don't bother with this, set in calculation and save memory from object.
update TABLEA
SET ULT_PROHRS = Ult_ProHrs + (40 - @Ttl_ProHrs), ULT_ADJ = 'Y'
where ID = @ult_id and SEQ = '00'
END
END
FETCH NEXT FROM mod_pro_hrs into @emp, @pweek, @Ttl_ProHrs, @Ttl_Hrs
END -- end cursor
CLOSE mod_pro_hrs
DEALLOCATE mod_pro_hrs
-
My comments use the prefix, $
DECLARE mod1 CURSOR
FOR SELECT EMP_NUM, PAY_WEEK, sum(Ult_ProHrs) as Ttl_ProHrs, sum(Hours) as Ttl_Hrs FROM TABLEA WHERE SUBMIT = 'Y' GROUP BY EMP_NUM, PAY_WEEK HAVING SUM(ULT_PROHRS) <> 40 -- This means only records that have more than 40 hours, should this have been <> or != %Records not equal to 40-- typo when entering, sorry
OPEN mod1
FETCH NEXT FROM mod1 into @emp, @pweek, @Ttl_ProHrs, @Ttl_Hrs
WHILE (@@FETCH_STATUS = 0)
BEGIN
declare @ult_id varchar(10)
declare @diff decimal(10,2)
declare @pweek datetime
declare @emp varchar(6)
declare @Ttl_Hrs decimal(10,2)
declare @Ttl_ProHrs decimal(10,2)
if (@Ttl_ProHrs <> 40) -- This would not be needed becuase HAVING SUM $Correct, this wouldn't be needed (pretend it's not there!)
(ULT_PROHRS) > 40 means all would have more than 40. &Also not needed
BEGIN
Set @ult_id = ( Select max(c_dktime_id) from TABLEA Where earn_code in ('0001D','0001I')and Emp_num = @emp and pay_week = @pweek and pull_date is null and submit = 'Y' )
END
If (@ult_id <> '' ) --contains work hours
BEGIN
iF (@Ttl_ProHrs > 40)
BEGIN
--Set @diff = @Ttl_ProHrs - 40 --Don't bother with this, set in calculation and save memory from object.
update TABLEA
SET ULT_PROHRS = Ult_ProHrs - (@Ttl_ProHrs - 40)
where ID= @ult_id and SEQ = Th'00' -- Not sure what the TH thing is about, can you correct please. $ typo, should be '00'
END
Else -- Again becuase of HAVING SUM(ULT_PROHRS) > 40 this would never occurr. $Changed to <> 40, could occur
BEGIN
--Set @diff = 40 - @Ttl_ProHrs --Don't bother with this, set in calculation and save memory from object.
update TABLEA
SET ULT_PROHRS = Ult_ProHrs + (40 - @Ttl_ProHrs), ULT_ADJ = 'Y'
where ID = @ult_id and SEQ = '00'
END
END
FETCH NEXT FROM mod_pro_hrs into @emp, @pweek, @Ttl_ProHrs, @Ttl_Hrs
END -- end cursor
CLOSE mod_pro_hrs
DEALLOCATE mod_pro_hrs
Thanks for taking the time to help
-
I think this is the rough equalivilant to you cursor. I have not optimized too much so could be a better version of this that could be built. And it is possible the curosr is the best.
UPDATE
Z
SET
ULT_PROHRS = (CASE WHEN A.Ttl_ProHrs > 40 THEN Z.Ult_ProHrs - (A.Ttl_ProHrs - 40) ELSE Z.Ult_ProHrs + (40 - A.Ttl_ProHrs) END),
ULT_ADJ = (CASE WHEN A.Ttl_ProHrs > 40 THEN '' ELSE 'Y' END)
FROM
dbo.TABLEA Z
INNER JOIN
(
SELECT
EMP_NUM,
PAY_WEEK,
sum(Ult_ProHrs) as Ttl_ProHrs
FROM
dbo.TABLEA
WHERE
SUBMIT = 'Y'
GROUP BY
EMP_NUM,
PAY_WEEK
HAVING
SUM(ULT_PROHRS) != 40
 
AINNER JOIN
(
SELECT
EMP_NUM,
PAY_WEEK,
max(c_dktime_id) ult_id
FROM
dbo.TABLEA
WHERE
earn_code in ('0001D','0001I') and
pull_date is null and
submit = 'Y'
GROUP BY
EMP_NUM,
PAY_WEEK
 
BON
A.EMP_NUM = B.EMP_NUM AND
A.PAY_WEEK = B.PAY_WEEK
ON
Z.[ID] = B.ult_id
WHERE
Z.SEQ= '00'
However, your UPDATE however sturck me as odd as it looks like you are doing based on the max(c_dktime_id) and sequence instead of using a specific employee. You also don't have anything driving the specific pay week or employee.
-
I had a similar problem where using a cursor was my only option. My stored procedure took 9 min and I reduced that to 3-5 seconds.
What you might want to try, and it helped me out a ton, was to :
Index the tables you are quering (copy and paste your queries into query analyzer highlight one of them and hit CTRL + I to run the Index Tuning Wizzard) If you let it do its stuff and apply the suggested indexes it will help speed up things
I hope that helps you out a bit
-
Jill,
You're in very capable hands with Antares686, so to be honest, I haven't looked at your code. But I had to chime in with my 2 cents regarding cursors. So, here goes. Cursors are NOT necessarily evil. They are usually not a good idea when what you are doing can be handled by set based logic. And that is where they have gotten such a bad reputation.
Steve
-
Thanks for all your responses.
To Antares686: I'm sorry for the messy example, I just copied and pasted and made some modifications to get the general idea of the cursor, and it looks like I forgot a few steps. Every employee for a pay week has multiple IDs and Multiple Sequences for that ID and I want to update the maximum ID and first sequence '00' for the employee for that pay week, which is why the max(c_dktime_id) is in there.
Plus, my update statement should also include where pay_week = @pweek. I will look into your logic and see if it helps!
I will also look at the indexes, too. This table is used in several applications so I have that to consider, also.
Thanks again!!!
-
Just an FYI on adding the where clause for pay_week. Do it in both of the subqueries as well as the outside part of the UPDATE for best performance.
So something like this.
UPDATE
Z
SET
ULT_PROHRS = (CASE WHEN A.Ttl_ProHrs > 40 THEN Z.Ult_ProHrs - (A.Ttl_ProHrs - 40) ELSE Z.Ult_ProHrs + (40 - A.Ttl_ProHrs) END),
ULT_ADJ = (CASE WHEN A.Ttl_ProHrs > 40 THEN '' ELSE 'Y' END)
FROM
dbo.TABLEA Z
INNER JOIN
(
SELECT
EMP_NUM,
PAY_WEEK,
sum(Ult_ProHrs) as Ttl_ProHrs
FROM
dbo.TABLEA
WHERE
SUBMIT = 'Y' and
pay_week = @pweek
GROUP BY
EMP_NUM,
PAY_WEEK
HAVING
SUM(ULT_PROHRS) != 40
) A
INNER JOIN
(
SELECT
EMP_NUM,
PAY_WEEK,
max(c_dktime_id) ult_id
FROM
dbo.TABLEA
WHERE
earn_code in ('0001D','0001I') and
pull_date is null and
submit = 'Y' and
pay_week = @pweek
GROUP BY
EMP_NUM,
PAY_WEEK
) B
ON
A.EMP_NUM = B.EMP_NUM AND
A.PAY_WEEK = B.PAY_WEEK
ON
Z.[ID] = B.ult_id
WHERE
Z.SEQ= '00' and
Z.pay_week = @pweek
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply