Cursor
-
I need to execute a large cursor (500000 rows). Would it pay or me to split the cursor in the loop of 5000 rows and execute them then?
Inside cursor I execute 2 functions for each record.
-
I think it would pay more to rewrite and omit the use of a cursor for that many rows.
Post your suggested cursor here and I am sure someone will give you a better way.
------------------------------
The Users are always right - when I'm not wrong! -
DECLARE memcor CURSOR GLOBAL FORWARD_ONLY STATIC FOR
SELECT DISTINCT UserID,StatusID,HomeOrgID,Convert(datetime,CancelDate)
From tblmembership
WHERE Mon = @month
and Year = @Year
-- Selects around 500000 rows
OPEN memcor
FETCH NEXT FROM memcor
INTO @userID,@StatusID,@HomeClub,@CanDate
Print 'Cursor Rows'
Print @@CURSOR_ROWS
WHILE @@FETCH_STATUS = 0
BEGIN
Set @Date = Getdate()
-->Update first 30 days usage
--> If statment to prohibit calculations on people who has F30
If exists(Select UserID from tblMembership Where CompletedFlag = 1 and UserID = @user-id)
Begin
Update tblMembership
--back update with function results
Set UsageF30 = dbo.fn_Usage (30,@UserID,GetDate())
From dbo.tblMembership m
Where UserID = @user-id
and Mon = @month
and Year = @Year
End
Else
Begin
If (Select UsageF30 From dbo.tblMembership m Where UserID = @user-id and Mon = @PRmonth and Year = @PRYear)is not null
Begin
Update tblMembership
Set UsageF30 = m1.UsageF30
From dbo.tblMembership m inner join dbo.tblMembership_his m1
on m.UseriD = m1.UseriD
Where m.UserID = @user-id
and m1.Mon = @Prmonth
and m1.Year = @PrYear
End
Else
begin
--back update with function results
Update tblMembership
Set UsageF30 = dbo.fn_Usage (30,@UserID,GetDate())
From dbo.tblMembership m
Where UserID = @user-id
and Mon = @month
and Year = @Year
End
End
-->Update last 60 days usage
--back update with function results (Function is needed for all users)
Update tblMembership
Set UsageL60 = dbo.fn_Usage (60,@UserID,GetDate())
From dbo.tblMembership m
Where UserID = @user-id
and Mon = @month
and Year = @Year
-->Primary club
IF (Select Usagel60 From tblMembership Where useriD = @UseriD and Mon = @Month And Year = @Year)<> 0
BEGIN
--back update with function results
UPDATE tblMembership
Set PrimaryOrgiD = dbo.fn_PrimaryClub (@UserID,@StatusID,@Month,@Year,@CanDate)
Where UseriD = @UseriD
and Mon = @month
and Year = @Year
END
Else
Begin
UPDATE tblMembership
Set PrimaryOrgiD = @HomeClub
Where UserID = @user-id
and Mon = @month
and Year = @Year
End
Set @Result = Datediff(ss,@date,Getdate())
IF @Result <> 0
Begin
Print @user-id
Print @Result
Print '******************************************'
End
FETCH NEXT FROM memcor
INTO @userID,@StatusID,@HomeClub,@CanDate
End
CLOSE memcor
DEALLOCATE memcor
-
Hi,
I havent gone through the Script you have forwarded. but i normally do a diffrent method whenever i work with large data .
use a table variable
insert the cursor declaration output into this table
Loop Start
take the top 1 row to do the data processing. (instead of fetch next)
delete the top row after the usage (this will release one row from memory and at loop start will return the new row.)
Loop End
I hope this may help you
Jeswanth
--------------------------------
-
Without testing this I cannot say if my code below will be bug free but the first three queries simply don't need a cursor.
Your cursor simply selects all users for a particular month and year in the membership table.
Query one runs on each user provided their completed membership flag is set so the first query can be written as
Update tblMembership
Set UsageF30 = dbo.fn_Usage (30,@UserID,GetDate())
From dbo.tblMembership m
Where Mon = @month
and Year = @Year
and CompletedFlag = 1
Query two is what to do if the membership flag isn't set so simply add the last three conditions to the where clause and remove the @userid condition.
Update tblMembership
Set UsageF30 = m1.UsageF30
From dbo.tblMembership m inner join dbo.tblMembership_his m1
on m.UseriD = m1.UseriD
Where m.UserID = @user-id
and m1.Mon = @Prmonth
and m1.Year = @PrYear
and m.mon = @month
and m.year = @year
and m.completedflag<>1
Query three I think you can do with either a derived table
--back update with function results
Update tblMembership
Set UsageF30 = dbo.fn_Usage (30,@UserID,GetDate())
From dbo.tblMembership m left join (
Select UserId From dbo.tblMembership m Where Mon = @PRmonth and Year = @PRYear
) AS DT ON m.userid = DT.userid
where Mon = @month
and Year = @Year
and dt.userid is null
OR using an IN clause in your WHERE
--back update with function results
Update tblMembership
Set UsageF30 = dbo.fn_Usage (30,@UserID,GetDate())
From dbo.tblMembership Where Mon = @month
and Year = @Year
and userid not in (Select UserId From dbo.tblMembership m Where Mon = @PRmonth and Year = @PRYear )
You get the picture?
SQL Server works best on sets of data therefore all effort should be taken to eliminate cursors.
I gather that ORACLE is better geared to cope with cursors.
-
Thanks guys. I appreciate the effort. Will try both ways suggested.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply