Cursor or String

Question

Cursor or String

Rohit @ SQL

SSC Eights!

Points: 938
More actions
May 25, 2004 at 10:12 pm

#107887

Hi to all,
i have one question to ask
if i select records from one table in a cursor and process each one by one
or i select records in string
declare @info varchar(2000)
Select @info=isnull(@info+'|'+F1+','+F2,F1+','+F2)
From Temp
in this query , is field separator and | is row separator
now using the string @info and sql's string functions i fetch the records and fields and do processing as in the case of cursor
now. i want to know which method is more costly and why
Thanks in advance

Rohit

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

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

David.Poole SSC Guru Points: 76039 More actions · Answer 1

It is not really clear from your code what you are trying to do.

The settings of your SQL Server will affect how NULL values are interpretted so be careful, otherwise your ISNULL function won't work.

In general I look for ways to avoid using cursors because SQL Server works best with sets of data rather than at a row level. I believe that ORACLE is better geared up to row by row work and even has an @@rowid value.

LinkedIn Profile

Calvin Lawson SSChampion Points: 11030 More actions · Answer 2

Here's an example of how to loop through a table and put all the values in a row into a string. definitely do not use a cursor:

create function Column_CSV (@TableName sysname)

returns varchar(8000)

as

begin

declare @ColList varchar(8000)

SELECT @ColList = isnull(@ColList + ', ', '') + c.name

FROM sysobjects o

JOIN syscolumns c On o.ID = c.ID

WHERE o.name = @TableName

ORDER BY c.colOrder

return @ColList

END

Signature is NULL

Rohit @ SQL SSC Eights! Points: 938 More actions · Answer 3

Thanks Calvin and David for your responses

As Calvin you told that there is one method of fetching all the rows from one table

Now i just want to know that if i use this csv string in any SP or Function and use substring and other functions to parse the values of rows and cols in the string then this would be efficient method then using cursor

and if i have 1000 or more records then i can hold the values in text or ntext type variable then which method (string or cursor) would be efficient

Thanks & Regards

Rohit

Calvin Lawson SSChampion Points: 11030 More actions · Answer 4

Working with Text data is a whole 'nother ball game. Updating and parsing text data types uses a bunch of special functions, so check BOL for more data.

Personally, I use "While" loops instead of fast forward cursors. I've found that they perform better, especially on larger recordsets. And the code is more concise and clearer.

if object_ID('tempdb..#Dest') is not null drop table #Dest

create table #Dest (Value text)

Insert #Dest Values ('')

declare @Cnt varchar(25),

@DataLength int,

@TextPTR varbinary(16)

select @Cnt = 0

select @TextPTR = TEXTPTR(Value)

from #Dest

--Creating Test Data

While @Cnt < 1000

BEGIN

select @DataLength = DataLength(Value)

from #Dest

UPDATETEXT #Dest.Value @TextPTR @DataLength NULL @Cnt

select @Cnt = cast(@Cnt as int) + 1

END

select DataLength(Value), * from #Dest

Signature is NULL

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

Rohit,

The setbased logic you used in your example will almost always beat Cursors and WHILE loops. Do you need any reason other than speed? Ok, how about resource intensity, duration of locks, and number of locks on the table and idexes?

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