Simple Cursor taking close to 4 minutes how can I fasten this up

Question

Simple Cursor taking close to 4 minutes how can I fasten this up

kalagarlas

Grasshopper

Points: 15
More actions
September 16, 2008 at 3:31 pm

#192841

Hello Everyone,
This is the first time I am trying to use cursors, I am trying to write a simple cursor so that I perform the task on each row, and it is taking very long to process that close to 4 minutes, I dont know if it is normal with cursor or if I am doing anything wrong. Any help or comments would really be appreciated.
Let me try to explain what I am trying to do, I have 2 tables called tbUserIds and another tbPermissions.
Columns
tbUserIds: OldUserId, NewUserId
tbPermissions: ApplicationName, ProjectNumber, UserId, GroupName
For each row in tbUserIds I have to check if the OldUserId has permissions to any applications and see if the NewUserId also have the same permissions if not update the table with NewUserId and permissions.
here is what I wrote to get all the rows from permissions table for oldUserId and check if the table has same permissions with NewUserId if not update the table with correct permissions
DECLARE @OldUserId varchar(32), @NewUserID varchar(32), @ApplicationName varchar(32), @ProjectNumber varchar(32), @GroupName varchar(32), @user-id varchar(32)
DECLARE myCursor2 CURSOR FOR
SELECT AB.SystemName, AB.ContractNumber, AB.GroupName, AK.UserID FROM tbPermissions AK
INNER JOIN tbPermissions AB ON AK.SystemName = AB.SystemName
AND AK.ContractNumber = AB.ContractNumber
AND AK.GroupName <> AB.GroupName
AND AB.Domain = 'mydomain'
AND AK.UserID = 'newid'
AND AB.UserID = 'oldid' --- this whole thing is not returning any rows
OPEN myCursor2
FETCH NEXT FROM myCursor2 INTO @SystemName, @ContractNumber, @GroupName, @user-id
WHILE @@FETCH_STATUS = 0 --- it is not going into this loop either which is correct as there are no rows returned
BEGIN
UPDATE tbPermissions SET GroupName = @GroupName WHERE SystemName =
@SystemName AND ContractNumber = @ContractNumber
AND UserID = @user-id
FETCH NEXT FROM myCursor2 INTO @SystemName, @ContractNumber, @GroupName, @user-id
END
CLOSE myCursor2
DEALLOCATE myCursor2
Even thought my select statement is not returned any rows it is taking very long time to process this statement, am I doing something that is really horrible? Any help or comment on fixing this would be really helpful.
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Welcome to the hell we call...cursors.

Cursors will torpedo your performance faster than damn near anything else I can think of. Since SQL Server is built to apply changes in sets of rows (many rows at once), anything forcing it to do it one row at a time will destroy its ability to perform. Unless there REALLY is no choice, cursors are to be avoided.

In your case - there's no reason you need to use a cursor. You should be able to do this all in one single update.

UPDATE tbPermissions

SET GroupName = @GroupName

from tbPermissions

inner join

(SELECT AB.SystemName, AB.ContractNumber, AB.GroupName, AK.UserID FROM tbPermissions AK

INNER JOIN tbPermissions AB ON AK.SystemName = AB.SystemName

AND AK.ContractNumber = AB.ContractNumber

AND AK.GroupName <> AB.GroupName

AND AB.Domain = 'mydomain'

AND AK.UserID = 'newid'

AND AB.UserID = 'oldid' ) as DerivedTable

on tbPermissions .SystemName = DerivedTable.SystemName AND

tbPermissions .ContractNumber = DerivedTable.ContractNumber AND

tbPermissions .UserID = DerivedTable.UserID

This should likely run quite a bit faster. If it's still a bit sluggish - consider trying to dump the derived table query into a temp table and using that in the update. If that's STILL a problem, then we might need to look at the indexing.

----------------------------------------------------------------------------------
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?

kalagarlas Grasshopper Points: 15 More actions · Answer 2

Thank you Matt. I will give it a try and keep you posted how it worked.

Thanks,

Kalagarla

kalagarlas Grasshopper Points: 15 More actions · Answer 3

Hey Matt, Thank you very much again. It worked wonderful and my processing time came down tremendously. Thanks a lot.

--Kalagarla

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Thanks for the feedback!

Just remember - they're EVIL!!!! (cursors) 😀

----------------------------------------------------------------------------------
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?

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

Sounds like you've successfully made a believer out of someone, Matt. 🙂 Now, all we have to do is convert the other bazillion folks who think there is any grace to be found in RBAR. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)