CODEWITHSUNDEEP
sql
Greens
Greens

Reputation: 3057

Fastest way to loop thru a SQL Query

What is the fastest way to loop thru a Query in T-SQL . 1) Cursors or 2) Temp tables with Key added or any thing else.

Upvotes: 0

Views: 5059

Answers (9)

KuldipMCA
KuldipMCA

Reputation: 3149

Cursor is not good avoid cursor and use while loop in place of cursor Temp table with key added is the best way to use looping.

i have to manipulate more than 1000000 rows in the table and for cursor take 2 min because of complex logic. but when convert cursor in to while loop it will take 25 seconds only. so that's big diffrence in performace.

Upvotes: 0

Nikki9696
Nikki9696

Reputation: 6348

I don't think you need a cursor for that (your comment about concat) if I understand what you're going for. Here's one of mine that grabs all the phone numbers for a contact and plops them in a field and returns it.

DECLARE @numbers VARCHAR(255)

SELECT @numbers = COALESCE(@numbers + ' | ','') + PHONE_NUMB FROM my_table (NOLOCK)
WHERE CONTACT_ID=@contact_id   RETURN @numbers

Upvotes: 0

richardtallent
richardtallent

Reputation: 35363

Here's a shortcut to get a comma-delimited string of a single field from a query that returns a number of rows. Pretty quick compared to the alternatives of cursors, etc., and it can be part of a subquery (i.e., get some things, and in one column, the ids of all the things related to each thing in some other table):

SELECT
    COALESCE(
      REPLACE(
        REPLACE(
          REPLACE(
        (SELECT MyField AS 'c' FROM [mytable] FOR XML PATH('')),'</c><c>',','), 
          '<c>',''),
        '</c>',''),
      '')
   AS MyFieldCSV

Caveat: it won't play nice if your column contains characters that FOR XML PATH will escape.

Upvotes: 0

GalacticCowboy
GalacticCowboy

Reputation: 11759

For your stated goal, something like this is actually a better bet - avoids the "looping" issue entirely.

declare @table table
(
    ID int
)

insert into @table select 1 union select 2 union select 3 union select 4 union select 5

declare @concat varchar(256)

-- Add comma if it is not the first item in the list
select @concat = isnull(@concat + ', ', '') + ltrim(rtrim(str(ID))) from @table order by ID desc

-- or do whatever you want with the concatenated value now...
print @concat

Upvotes: 3

Jim Evans
Jim Evans

Reputation: 6505

Depending on the size of your result set - Table variables are in memory and require no disk read, can be treated just like a table (set operations) and are very fast until result set gets to large for memory (which then requires swap file writes).

Upvotes: 0

SolutionYogi
SolutionYogi

Reputation: 32233

Do you want to loop through query output inside stored procedure OR from C# code?

Generally speaking, you should avoid looping through query output one row at a time. SQL is meant for set based operations so see if you can solve your problem using set based approach.

Upvotes: 0

Scott Ivey
Scott Ivey

Reputation: 41558

The fastest way to "loop" thru a query is to just not do it. In SQL, you should be thinking set-based instead of loop-based. You should probably evaluate your query, ask why you need to loop, and look for ways to do it as a set.

With that said, using the FAST_FORWARD option on your cursors will help speed things along.

Upvotes: 5

northpole
northpole

Reputation: 10346

Cursors are usually resource hogs especially as your table size grows. So if your table size is small I would be okay with recommending a cursor, however, a larger table would probably do better with an external or temporary table.

Upvotes: 0

Spencer Ruport
Spencer Ruport

Reputation: 35107

Depends on what you're trying to do. Some tasks are better suited for cursors, some for temp tables. That's why they both exist.

Upvotes: 1

Related Questions