Cursor substitution strategy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor substitution strategy

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-11-25 : 05:52:02

Hey fellas, long time no see! I'm experiencing some locking problems (mostly pagelocks) again in my database and I'm positive that a cursored procedure is causing it. Cursors are bad, no doubt about that but I *have* to do the processing on a row-by-row basis so I have to change my strategy... right now it looks like this:

BEGIN TRAN
DECLARE @myCur CURSOR = (SELECT ID FROM table WHERE tblDate < GETDATE())
FETCH NEXT...
WHILE (@@FETCH...
BEGIN 
  --> Do all my row-by-row-stuff
  FETCH NEXT...
END
COMMIT TRAN

Now the new strategy is a little different...I just need your advice here if this is a good way to do things or if there are more efficient ways:

DECLARE @tmpdate datetime, @ID int
SET @tmpDate = '1900-01-01 00:00:00'
WHILE @tmpDate < GETDATE()
  BEGIN
    BEGIN TRAN
    SET @ID = (SELECT TOP 1 ID FROM table WITH (ROWLOCK) WHERE tblDate > @tmpDate ORDER BY tblDate)
    --> Do row by row stuff
    COMMIT TRAN
  END

Is this a good strategy? Are there ways I can make it better somehow? Indexes and such will of course be tuned appropriately...

EDIT: @tmpDate will naturally have to be updated with the tblDate from that row

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-25 : 06:06:00

welcome back

it would be easier if you told us what is row by row stuff.... i strongly believe it can be done without the loop

that is the more efficient way.

but the set up looks ok to me. maybe it would be a good thing if you put an additional bit column
in the table that has a value 1 if processing and 0 if not. so when you want to mess with the row you must check the bit column which will eliminate locks. no?

Go with the flow & have fun! Else fight the flow

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-11-25 : 07:14:58

Well, what I made here is for illustration purposes only...the "row-by-row stuff" is basically 4 updates and 6 other stored procedures that need to be run for every row in the select. Making it all happen set-based is just not an option...when it comes to the bit-column I have that allready so that is not a problem.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-25 : 08:47:16

>>Cursors are bad, no doubt about that but I *have* to do the processing on a row-by-row basis...

Why?

The most substantial performance gains will come from restructuring the "row-by-row stuff" which is currently performing singleton processing.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-11-25 : 09:03:39

quote:
...the "row-by-row stuff" is basically 4 updates and 6 other stored procedures...

It would take me months to get something working if I was to do this set-based...not even sure it's possible

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-25 : 10:41:39

Lumbago -- doing what you have shown will not give you any performance increase at all. it's still the basic problem -- calling stored proc over and over for each row in a table.

the stored procs you call should operate directly on the table you are iterating over, and should do all their work on all rows at once. Why don't you show us more details, pick a procedure that somewhat small and show us what it is doing.

if the procedure is sending an email or calling an external process, then indeed it will need to do one thing at a time; but if it is updating, deleting or inserting rows in tables, they should be rewriten to do everything at once.

consider this:

Proc1: accepts a @MinDate and @MaxDate parameters; iterates tblData for each row in that date range, gets a @RecID, and calls Proc2, passing in @RecID.

Proc2: Accepts a @RecID and does *something* with it

Those two stored procedures should be consolidated into 1 stored procedure:

New Proc: Accepts a @MinDate and @MaxDate and does something using all rows in tblData within that date range.

Not sure if that was clear or not, but it is the overall idea.

- Jeff

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-11-25 : 11:18:05

Don't get me wrong, I truly appreciate your efforts but you are not answering my question. Please, just for the sake of discussion let's pretend I'm sending an email to a bunch of people...

I belive the last one will perform alot better because the transaction is placed within the loop instead of outside and also because it only selects one single row at a time, not locking every row that is going to be processed at a later time. Is this right or wrong, why, and is there a better way to do an operation like this row-by-row?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-25 : 11:27:31

yes it will because it will lock one row at the time.

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-25 : 14:24:26

There is a logical difference between these:
( One can commit some of the processed rows and the other commits all or none of the processed rows )

WHILE rows_left_to_process
BEGIN
	BEGIN TRANSACTION
		-- process the row
	COMMIT
END

BEGIN TRANSACTION
	WHILE rows_left_to_process
	BEGIN
		-- process the row
	END
COMMIT

If you are doing complex processing that seems easier to handle with a cursor:
You should consider using temporary tables for the processing (set based, step by step).
When all the data is processed in the temporary tables, commit the data to the regular tables.

Of course, if you are only processing a small number of rows then it doesn't matter that much.

rockmoose

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-11-25 : 16:33:56

Yes you are right...there is a logical difference between the two. This is actually a business decision, each row needs to be processed individually and if another instance of this procedure takes the "next" row in "table" that's fine. I have optimized this procedure to the best of my ability now, but I promise that I will give it some serious thought trying to make it even more set-based.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources