Cursor or the While loop?
-
I have a problem which is the best to implement by declaring a cursor or a while loop, in terms of optimization purposes. Thanks to whatever responses have been made to this post of mine. -
Can you expand a bit on what your problem is? Many people here try to avoid cursors as much as possible, for performance reasons, but sometimes you just have to use them.
Depending on your problem, if you can implement WHILE logic while retaining set-based (rather than record-by-record based (as in a cursor)) operations, you will probably end up with better performance.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Actually I coded already the declare cursor and here's the code:
DECLARE cursor TotalCursor FOR SELECT OptioneeKey, OfferingPeriodKey
FROM WT_Total_Purchases
OPEN TotalCursor
FETCH NEXT FROM TotalCursor
INTO @intOptioneekey, @intOfferingPeriod
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dtmMinDate = (SELECT min(create_dt) FROM WT_ESPP_FACT
WHERE optioneekey = @intOptioneekey
AND offeringperiodkey = @intOfferingPeriod
AND eventkey IS NULL)
SET @dtmMaxDate = (SELECT max(create_dt) FROM WT_ESPP_FACT
WHERE optioneekey = @intOptioneekey
AND offeringperiodkey = @intOfferingPeriod
AND eventkey IS NULL)
UPDATE WT_Total_Purchases
SET MinDate = @dtmMinDate,
MaxDate = @dtmMaxDate
WHERE optioneekey = @intOptioneekey
AND offeringperiodkey = @intOfferingPeriod
FETCH NEXT FROM TotalCursor
INTO @intOptioneekey, @intOfferingPeriod
END
CLOSE TotalCursor
DEALLOCATE TotalCursor
Thanks Phil!...
-
Hi Maria, had a quick look at this and I think it can be re-coded, without needing a cursor and using fewer lines of code. However, no time at the moment - deadlines etc. Will respond again later if no one else gets here first.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi. Here's one I got off of eggheadcafe.com a while back and have used variations of it many times. Personally I avoid cursors as much as possible because SQL Server is a set based environment and was not really designed to use them well. Ken Henderson (Guru's Guide to Transact SQL), among many others, concurs with this.
Here's the code:
/***************** Begin code **********************/
/* SQL Server Cursorless Cursor */
/* http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=529 */
declare @RowCnt int
declare @MaxRows int
declare @Email nvarchar(255)
select @RowCnt = 1
declare @Import table
(
rownum int IDENTITY (1, 1) Primary key NOT NULL ,
Email nvarchar(255)
)
insert into @Import (Email) values ('blah@blah.com')
insert into @Import (Email) values ('blahblah@blah.com')
select @MaxRows=count(*) from @Import
while @RowCnt <= @MaxRows
begin
select @rc=0
select @Email = Email
from @Import
where rownum = @RowCnt
print @Email
Select @RowCnt = @RowCnt + 1
end
/******************* end code ************/
G. Milner
-
Hi Maria
Something like this gets rid of the cursor and does everything in far fewer statements:
update wtp
set MinDate = (SELECT min(wtf.create_dt) FROM WT_ESPP_FACT wtf
WHERE wtf.optioneekey = wtp.Optioneekey
AND wtf.offeringperiodkey = wtp.OfferingPeriod
AND wtf.eventkey IS NULL),
MaxDate = (SELECT max(wtf2.create_dt) FROM WT_ESPP_FACT wtf2
WHERE wtf2.optioneekey = wtp.Optioneekey
AND wtf2.offeringperiodkey = wtp.OfferingPeriod
AND wtf2.eventkey IS NULL)
from wt_total_purchases wtp
Haven't tested the syntax, but should work OK - famous last words
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
What do you mean by this code?:
MaxDate = (SELECT max(wtf2.create_dt) FROM WT_ESPP_FACT wtf2
WHERE wtf2.optioneekey = wtp.Optioneekey
AND wtf2.offeringperiodkey = wtp.OfferingPeriod
AND wtf2.eventkey IS NULL)
from wt_total_purchases wtp
Anyway thanks phil!!!...
This is a great help. -
I think this should be wrote like this:
Update wtp.MaxDate = (SELECT max(wtf2.create_dt) FROM WT_ESPP_FACT wtf2 JOIN from wt_total_purchases wtp
WHERE wtf2.optioneekey = wtp.Optioneekey
AND wtf2.offeringperiodkey = wtp.OfferingPeriod
AND wtf2.eventkey IS NULL)
-
Noooo...it still wrong...
Update wtp.MaxDate = (SELECT max(wtf2.create_dt) FROM WT_ESPP_FACT wtf2 JOIN wt_total_purchases wtp1
ON wtf2.optioneekey = wtp1.Optioneekey
AND wtf2.offeringperiodkey = wtp1.OfferingPeriod
WHERE wtf2.eventkey IS NULL) FROM wt_total_purchases wtp
-
What about this....
UPDATE wt_total_purchases
SET MaxDate = (SELECT MAX(wtf.create_dt)
FROM WT_ESPP_FACT wtf
WHERE wtf.optioneekey = wtp.Optioneekey
AND wtf.offeringperiodkey = wtp.OfferingPeriod
AND wtf.eventkey IS NULL),
MinDate = (SELECT MIN(wtf2.create_dt)
FROM WT_ESPP_FACT wtf2
WHERE wtf2.optioneekey = wtp.Optioneekey
AND wtf2.offeringperiodkey = wtp.OfferingPeriod
AND wtf2.eventkey IS NULL)
FROM wt_total_purchases wtp
Regards,Anders Dæmroen
epsilon.no -
Slight modification from table variable example:
declare @Import table
(
rownum int IDENTITY (1, 1) Primary key NOT NULL ,
Email nvarchar(255)
)
insert into
@Import
(Email)
values
insert into
@Import
(Email)
values
declare
@RowCnt int,
@MaxRows int,
@Email nvarchar(255)
--this way allows reuse of the table if you need to clear it and use it again
--it also allows for the case when your table variable population is null
select
@RowCnt = isnull(min(rownum),0),
@MaxRows = isnull(max(rownum),-1)
while @RowCnt <= @MaxRows
begin
select
@Email = Email,
--incrementing the row counter here compresses the code
@RowCnt = @RowCnt + 1
from
@Import
where
rownum = @RowCnt
print @Email
end
/******************* end code ************/
-
Thanks Anders!!!...that would be a great help
-
Just another option
UPDATE
T1
SET
MinDate = T2.MinVal,
MaxDate = T2.MaxVal
FROM
WT_Total_Purchases T1
INNER JOIN
(
SELECT
optioneekey,
offeringperiodkey,
min(create_dt) MinVal,
max(create_dt) MaxVal
FROM
WT_ESPP_FACT
WHERE
eventkey IS NULL
GROUP BY
optioneekey,
offeringperiodkey
 
T2ON
T1.optioneekey = T2.optioneekey AND
T1.offeringperiodkey = T2.offeringperiodkey
-
You guys are doing a great job looking at set based options, well done. As far as the core question of which is better, while loop or cursor, I'd be interested to hear more thoughts. It's easy to dismiss cursors as evil, but they have their place (Im not disagreeing with trying to stay set based by any means). If - big if - you need a loop, Im not sure that using a while instead of a cursor is "better". Depending on how it's written it's the equivalent of a dynamic cursor, hardly what we want to use in most cases.
-
Which is better is not a real point Andy. What matters is for this situation which works best. You still have to test each. All should be considered alternatives and all have their merrits.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply