For those who say cursors are evil

Question

For those who say cursors are evil

Viewing 3 posts - 16 through 17 (of 17 total)

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

philcart SSC-Forever Points: 47794 More actions · Answer 1

Consider this,

DECLARE @id int

SELECT TOP 1 @id = id 
FROM sysobjects 
WHERE xtype='U'
ORDER BY id

PRINT @id

WHILE @id IS NOT NULL
BEGIN
    --do stuff

    SET @id = (SELECT TOP 1 id FROM sysobjects WHERE xtype='U' AND id > @id)

    PRINT @id

END

PRINT 'Finished'

Nice little loop through the user tables in sysobjects, no cursor, no temp table, minimal resource overhead.

--------------------
Colt 45 - the original point and click interface

Kenneth Lee Ten Centuries Points: 1293 More actions · Answer 2

Nice little loop through the user tables in sysobjects, no cursor, no temp table, minimal resource overhead.

1. It isn't a "result set" solution which is the biggest complaint I've seen against cursors.

2. You are depending on the clustered index for sysobjects to be id for the loop to find all User tables.

3. The creation of a temp table reduces the search overhead because sysobjects can contain several thousand records when you are only interested in a few hundred.

4. It is still going to take longer than a cursor solution.

5. Why SET the variable in the loop instead of SELECTing it like your initialization? Nevermind, answered my own question, doesn't get set to null in select statement.

philcart SSC-Forever Points: 47794 More actions · Answer 3

1. It isn't a "result set" solution which is the biggest complaint I've seen against cursors.

That's right, I wasn't presenting it as a "result set" solution, I was presenting it as a solution to the problem that doesn't have the overheard of cursors and temporary objects.

2. You are depending on the clustered index for sysobjects to be id for the loop to find all User tables.

3. The creation of a temp table reduces the search overhead because sysobjects can contain several thousand records when you are only interested in a few hundred.

The technique is more of what I was trying to illustrate.

4. It is still going to take longer than a cursor solution.

Actually no it doesn't, but then again this would be relative to the environment that it's executed in. In a previous contract I replaced a cursor based solution with this technique and cut processing time in half.

--------------------
Colt 45 - the original point and click interface