To Use a Cursor or to Not Use a Cursor

[Jim Lewis]

I need to loop through a set of records to build a string. I can do
this without using a cursor by inserting the records into a temporary
table with an identity column. Count the number of records in the
temporary table and loop though the table selecting the values and
building the string where the identity column = the loop number.

Is this more or less efficient than just using a cursor? If so why is
it more or less efficient?

Please explain in detail

Thank You,
Jim Lewis

[Simon Hayes]

j_*********@hotmail.com (Jim Lewis) wrote in message news:<78*************************@posting.google.c om>...

I need to loop through a set of records to build a string. I can do
this without using a cursor by inserting the records into a temporary
table with an identity column. Count the number of records in the
temporary table and loop though the table selecting the values and
building the string where the identity column = the loop number.

Is this more or less efficient than just using a cursor? If so why is
it more or less efficient?

Please explain in detail

Thank You,
Jim Lewis

Without sample tables, data and code, it's not possible to say for
sure, however, as a general rule, a cursor operation is slower than
other approaches. You should be able to test both operations using
Query Analyzer, with Show Execution Plan turned on. The plan will show
what percentage of the batch was taken up by each operation, so if a
loop is 25% and a cursor is 75%, then the loop is more efficient.

If you do this, you need to ensure that caching isn't distorting your
results, so you should execute DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS before testing each option. Something like this:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
exec dbo.ProcUsingLoop

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
exec dbo.ProcUsingCursor

Simon

[John Bandettini]

Jim

In most cases if you can use another method rather than using a cursor
with SQL Server use it. Cursors in SQL Server use a lot of resources
and are in nearly all cases are slower than the alternatives.

I did have a very good paper on why not to use cursors, but
unfortunately I can't find it. A good way to see the difference is to
write it both ways and use QA to see the execution plan and profiler
to capture what it actually does.

Good luck

John

[Jim Lewis]

I started writing code to determine which was the more efficient
approach and I encountered something interesting

CREATE TABLE TestLoop (
TestLoopID int IDENTITY (1, 1) NOT NULL ,
FirstName varchar(50) NULL)

INSERT INTO TestLoop (FirstName) VALUES ('Tom')
INSERT INTO TestLoop (FirstName) VALUES ('Dick')
INSERT INTO TestLoop (FirstName) VALUES ('Harry')

DECLARE @OutPut as Vachar(8000)
SET @OutPut = ''

SELECT @OutPut = @OutPut + FirstName FROM TestLoop

--Output Tom, Dick, Harry,

I do not know that T-SQL would flatten all the values into the string.
There is no need for looping at all! How and why does SQL Server do
this?

[Gert-Jan Strik]

<snip>

SELECT @OutPut = @OutPut + FirstName FROM TestLoop

--Output Tom, Dick, Harry,

I do not know that T-SQL would flatten all the values into the string.
There is no need for looping at all! How and why does SQL Server do
this?

Because this proprietary syntax is allowed. However, there are several
disadvantages/problems with it. For example, you have no control over
the order in which the strings are concatenated. Often it is a
requirement to concatenate the strings in sorted order.

Because of these problems IMO it is better to use a solution that is
reliable. A solution that will also still work when you apply a service
pack or when you port the code to a new SQL-Server version. In this case
this could be by using a loop or a cursor.

Hope this helps,
Gert-Jan

[Erland Sommarskog]

Jim Lewis (j_*********@hotmail.com) writes:

I started writing code to determine which was the more efficient
approach and I encountered something interesting

CREATE TABLE TestLoop (
TestLoopID int IDENTITY (1, 1) NOT NULL ,
FirstName varchar(50) NULL)

INSERT INTO TestLoop (FirstName) VALUES ('Tom')
INSERT INTO TestLoop (FirstName) VALUES ('Dick')
INSERT INTO TestLoop (FirstName) VALUES ('Harry')

DECLARE @OutPut as Vachar(8000)
SET @OutPut = ''

SELECT @OutPut = @OutPut + FirstName FROM TestLoop

--Output Tom, Dick, Harry,

I do not know that T-SQL would flatten all the values into the string.
There is no need for looping at all! How and why does SQL Server do
this?

By pure chance, it seems. I tried to explain this in my previous
article. You can to do this, but the result of this kind of statement
is not defined. So for robust code, yes, you need to loop.

See also http://support.microsoft.com/default.aspx?scid=287515, and
pay particular attention to the first sentence in the CAUSE section.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ross Presser]

j_*********@hotmail.com (Jim Lewis) wrote in
news:78**************************@posting.google.c om:

I started writing code to determine which was the more efficient
approach and I encountered something interesting

CREATE TABLE TestLoop (
TestLoopID int IDENTITY (1, 1) NOT NULL ,
FirstName varchar(50) NULL)

INSERT INTO TestLoop (FirstName) VALUES ('Tom')
INSERT INTO TestLoop (FirstName) VALUES ('Dick')
INSERT INTO TestLoop (FirstName) VALUES ('Harry')

DECLARE @OutPut as Vachar(8000)
SET @OutPut = ''

SELECT @OutPut = @OutPut + FirstName FROM TestLoop

From what you've written so far, I don't think ANYTHING would be *output*.
If you added

SELECT @OutPut

as a command after the above, I would expect this result

TomDickHarry
--Output Tom, Dick, Harry,

I do not know that T-SQL would flatten all the values into the string.
There is no need for looping at all! How and why does SQL Server do
this?

Next, the truth is that the result is undefined because there is no
telling what order the optimizer will choose to return the results in.
E.g., if you happened to have created an index on FirstName, the optimizer
might very well decide that the index was the quickest way to get the
field values - and since you did not specify an ORDER BY Clause, you might
well get this output:

DickHarryTom

However, in controlled situations, this sort of shenanigans will generally
function as required. A great many cursors can be eliminated with
cleverness of this sort - but don't tell Mama.

--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th
Centurary Europe. Understand it, and you can travel to places you never
heard of and still understand some people." -- Alex K. Angelopoulos