• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

Alternative to cursors in sql server

How to avoid cursors in SQL Server with methods to loop over records?
0
vikalgupta
Asked:
vikalgupta
  • 7
  • 6
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you really need to loop over individual records, you can insert the relevant data into a table variable, and process from there. this is usually a faster way, which avoids long lasting locks on the original table, and still/hence runs faster.

so, the first question is: do you REALLY need to handle individual records?
yes => some "basic sql" to start with:
  declare @t table ( key int , rnum int identity)
  declare @key int

  insert into @t (key) select keyfield from yourtable where ....

  while @@rowcount > 0
  begin
     select top 1 @key = key from @t order by rnum
     if @@rowcount > 0 
     begin
         --- here starts the handling of the "record" with the @key information
         print @key
         ---- end of handling @key record
     end
     delete @t where key = @key
  end

Open in new window

0
 
vikalguptaAuthor Commented:
Any other alternative apart from this?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, there are surely other "code" methods to solve this in general, but the definitive answer depends largely on "what exactly are you trying to do here"...
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and for the record: the technique I showed runs most often 5x - 10x faster and more stable than the cursor method ...
0
 
vikalguptaAuthor Commented:
I am looking to process the records based on some ordering with utilizing the SQL resources at a minimum.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
"process" => what is that actually doing? emailing? updating? ...

"with utilizing the SQL resources at a minimum. "
then , you might need to query for all the needed data, and handle the data in a processing application, away from the sql resources.

however, if the "process" is again something in the sql database, transporting the data over the network may be more overhead than what you are "winning" on the other side.

testing is here the key to answer correctly, and to test, one must know what you are doing.
0
 
vikalguptaAuthor Commented:
My process is insertion to db after processing individual record.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you still write "processing".

the "INSERT" as such could be done without looping, so what is that "processing" about?
0
 
vikalguptaAuthor Commented:
Processing here refers to some intermediate calculation and then do the insertion.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SQL can also do calculations ...

so, you process could do something like this example:
INSERT INTO final_table ( col1, col2 ... )
  SELECT col1,  ( col3 + col2 )  * 344 ....
    FROM source_table

Open in new window


so, what is that "intermediate calculation"?
0
 
vikalguptaAuthor Commented:
Intermediate calculation here refers to:

1)insert and then
2)update the corresponding record in some other table based on some key.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can solve that by using the INSERT + OUTPUT clause, using the OUTPUT INTO @table for the UPDATE in a second step...
technical reference and examples:
http://msdn.microsoft.com/en-us/library/ms177564.aspx
http://msdn.microsoft.com/en-us/library/ms174335.aspx
http://blog.sqlauthority.com/2007/10/01/sql-server-2005-output-clause-example-and-explanation-with-insert-update-delete/
0
 
vikalguptaAuthor Commented:
Thanks
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now