Alternative to use cursors in SQL Server stored procedure

Question

It's not like that I am having trouble executing my cursors which are enclosed in a stored procedure. But I want to find more efficient way to achieve the same.

Here it goes.

Stored procedure : RawFeed.sql (runs every 5 minutes)

Set @GetATM = Cursor For
        Select DeviceCode,ReceivedOn
        From RawStatusFeed
        Where CRWR=2 AND Processed=0
        Order By ReceivedOn Desc
    Open @GetATM
    Fetch Next
    From @GetATM Into @ATM,@ReceivedOn
    While @@FETCH_STATUS = 0
        Begin
            Set @RawFeed=@ATM+' '+Convert(VarChar,@ReceivedOn,121)+' '+'002307'+' '+@ATM+' : Card Reader/Writer - FAULTY '
            Exec usp_pushRawDataAndProcess 1,@RawFeed
            Fetch Next
            From @GetATM Into @ATM,@ReceivedOn
        End
Set @GetATM = Cursor For
        Select DeviceCode,ReceivedOn
        From RawStatusFeed
        Where CRWR=0 AND Processed=0
        Order By ReceivedOn Desc
    Open @GetATM
    Fetch Next
    From @GetATM Into @ATM,@ReceivedOn
    While @@FETCH_STATUS = 0
        Begin
            Set @RawFeed=@ATM+' '+Convert(Varchar,@ReceivedOn,121)+' '+'002222'+' '+@ATM+' : Card Reader/Writer - OK '
            Exec usp_pushRawDataAndProcess 1,@RawFeed
            Fetch Next
            From @GetATM Into @ATM,@ReceivedOn
        End

Likewise I have 10 more SET statements which differ on WHERE condition parameter & string enclosed in @RawFeed variable.

For each row I get I execute another stored procedure on that particular row.

My question is

Is there any better way to achieve the same without using cursors?

Variable @RawFeed Contains following string which is input to usp_pushRawDataAndProcess stored procedure. now this will divide whole string and do some operation like INSERT,UPDATE,DELETE on some tables.

WE JUST CAN NOT PROCESS MORE THAN 1 STRING IN usp_pushRawDataAndProcess

NMAAO226 2012-09-22 16:10:06.123 002073 NMAAO226 : Journal Printer - OK 
WMUAO485 2012-09-22 16:10:06.123 002222 WMUAO485 : Card Reader/Writer - OK

The answer rather depends on what `usp_pushRawDataAndProcess` does. What does it do? — Jodrell, Sep 26 '12 at 11:12
usp_pushRawDataAndProcess gets a long string it divide that string and depending on value present in that sub string it INSERT,UPDATE,DELETE records in few tables in DB. — , Sep 26 '12 at 11:14
I suspect the answer depends on what happens in usp_pushRawDataAndProcess, and whether you could modify that for your specific purposes to process multiple entries rather than just 1 at a time. Meanwhile, you might get some perfomance improvements (and reduce the code a bit) if you use the cursor just once, including CRWR in your select, and doing some CASE statement based on the value of CRWR. But from where I'm sitting, I don't see a non-iterative solution. — Vinny Roe, Sep 26 '12 at 11:18
SQL SERVER is 2008. Like CRWR There are Many Code With Diff. Values How can i use CASE there ? and usp_pushRawDataAndProcess Takes One String at a time only.It divides that string and process it. — , Sep 26 '12 at 11:25
For one, if you need a cursor, [use better cursor options than the defaults (e.g. `LOCAL FAST_FORWARD`)](http://www.sqlperformance.com/2012/09/t-sql-queries/cursor-options). — Aaron Bertrand, Sep 26 '12 at 12:11
Does this answer your question? [Is there a way to loop through a table variable in TSQL without using a cursor?](https://stackoverflow.com/questions/61967/is-there-a-way-to-loop-through-a-table-variable-in-tsql-without-using-a-cursor) — Michael Freidgeim, Dec 11 '19 at 07:06

Jodrell · Accepted Answer · 2012-09-27T07:45:06.440

SQL Server, like other relational databases, is desgined to, and is pretty good at, working on sets of data.

Databases are not good at procedural code where all the opportunities for optimization are obscured from the query processing engine.

Using RawStatusFeed to store some proprietry request string and then processing a list of those one by one, is going to be ineffiencnt for database code. This might make the inserts very fast for the client, and this might be very important, but it comes at a cost.

If you break the request string down on insert, or better still, before insert via a specialised SP call, then you can store the required changes in some intermediate relational model, rather than a list of strings. Then, every so often, you can process all the changes at once with one call to a stored procedure. Admittedly, it would probably make sense for that stored procedure to contain several query statements. However, with the right indexes and statistics the query processing engine will able to make an efficient execution plan for this new stored procedure.

The exact details of how this should be achieved depend on the exact details of the RawStatusFeed table and the implementation of usp_pushRawDataAndProcess. Although this seems like a rewrite, I don't imagine the DeviceCode column is that complicated.

So, the short answer is certainly yes but, I'd need to know what usp_pushRawDataAndProcess does in detail.

The signature of the usp_pushRawDataAndProcess SP is acting as a bottle neck.

If you can't change usp_pushRawDataAndProcess and and won't create a set based alternative then you are stuck with the bottle neck.

So, rather than removing the bottle neck you could take another tack. Why not make more concurrent instances of the bottle neck to feed the data through.

If you are using SQL Server 2005 or above you could use some CLR to perform numerous instances of usp_pushRawDataAndProcess in parallel.

Here is a link to a project I used before to do something similar.

@SagarDumbre, the only edit I see says "WE JUST CAN NOT PROCESS MORE THAN 1 STRING", with that restriction in place I fear you will just be polishing a turd but, I've extended my answer with another alernative. — Jodrell, Sep 27 '12 at 07:49

James Drinkard · Answer 2 · 2013-08-21T15:23:11.797

I had always disliked cursors because of their slow performance. However, I found I didn't fully understand the different types of cursors and that in certain instances, cursors are a viable solution.

When you have a business problem that can only be solved by processing one row at a time, then a cursor is appropriate.

So to improve performance with the cursor, change the type of cursor you are using. Something I didn't know was, if you don't specify which type of cursor you are declaring, you get the Dynamic Optimistic type by default, which is the one that is the slowest for performance because it's doing lots of work under the hood. However, by declaring your cursor as a different type, say a static cursor, it has very good performance.

See these articles for a fuller explanation:

The Truth About Cursors: Part I

The Truth About Cursors: Part II

The Truth About Cursors: Part III

I think the biggest con against cursors is performance, however, not laying out a task in a set based approach would probably rank second. Third would be readability and layout of the tasks as they usually don't have a lot of helpful comments.

The best alternative to a cursor I've found is to rework the logic to take a set based approach.

SQL Server is optimized to run the set based approach. You write the query to return a result set of data, like a join on tables for example, but the SQL Server execution engine determines which join to use: Merge Join, Nested Loop Join, or Hash Join. SQL Server determines the best possible joining algorithm based upon the participating columns, data volume, indexing structure, and the set of values in the participating columns. So it generally the best approach in performance over the procedural cursor approach.

Here is an article on Cursors and how to avoid them. It also discusses the alternatives to cursors.

I'm HTH on Stackoverflow when I can. It's bailed me out of some tough coding problems in the past and thank you for the upvote! — James Drinkard, Oct 30 '13 at 13:46
It helped me out yesterday (the static cursor). I even forwarded an article to a colleague of mine. He was scripting out what a static cursor does by default (creating a table in TempDB and loop though the set). — Ruud van de Beeten, Oct 31 '13 at 10:53

score 0 · Answer 3 · answered Sep 26 '12 at 11:11

0

Alernates for CURSOR in SQL server

1.While loop
2.Recursive CTE

answered Sep 26 '12 at 11:11

AnandPhadke

13,160
5
26
33

4

Replacing a RBAR cursor approach with a RBAR while loop approach that simulates a cursor is [unlikely to help](http://bradsruminations.blogspot.co.uk/2010/05/truth-about-cursors-part-1.html) – Martin Smith Sep 26 '12 at 11:18
1

Agreed with @Martin. Also see https://sqlblog.org/blogs/aaron_bertrand/archive/2012/01/26/the-fallacy-that-a-while-loop-isn-t-a-cursor.aspx – Aaron Bertrand Sep 26 '12 at 12:08

score 0 · Answer 4 · answered Nov 27 '18 at 12:21

0

Alernates for CURSOR in SQL server
1. Use temp table. create any column ID as identity column.
2. Use while loop to perform the operation.

answered Nov 27 '18 at 12:21

Alternative to use cursors in SQL Server stored procedure

4 Answers4