Row by Row Operations Help req

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

HI David,
You rightly said that doing row by row operations in different manner is just like using cursors, I am into the same situation where I need to do row by row processing.I had created a temp table and looped it.In fact, I had two temp tables and two loops i.e nesting of loops I am doing.To explore below is the structure of my Store Proc

Create temp table 1
Insert data into table 1
Create temp table 2
Insert data into table 2

Loop for rows for table 1 starts here
Loop for rows for table 1 starts here

some stuff doing operation in a table
Loop 1 ends
Loop 2 ends

Now my problem is that when number of rows increases in second table, it takes too long to give the output.I am not sure if you can provide me with some sort of solution to this, but I hope and I wish you would reply to this.

Thanks and Regards,
Nirav Vyas



Russell Fields wrote:

Nigel,The cursor question is the SQL equivalent of the GOTOs / NO GOTOs
17-Apr-07

Nigel,

The cursor question is the SQL equivalent of the GOTOs / NO GOTOs debates in
the 70's and 80's. There are blogs also for both sides of all of these the
argument.

From a technology point of view, cursors are more subject to the standard
coding problems of procedural code, are (most of the time) slower, and
therefore hold locks longer. All of this adds up to raising a reasonable
question about whether the familiarity of procedural code is worth the
costs.

My first effort is always to find a set-oriented way to do the job. That
leverages the strengths of SQL Server and is usually faster since it uses
the SQL Server to handle the process, rather than passing it back to the
interpretation of my TSQL procedural code. There is also plenty of help on
'thinking relational' rather that procedural. The Inside SQL Server books
by Izak Ben-Gan and others are a big help.

On the other hand, for some jobs a cursor is almost the only way to get the
job done. So, my take is to reserver cursors for when there is no other
good answer.

RLF


"Nigel A" <nigel@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:177AFC27-BF2D-4D06-B19D-0085871AE53A@xxxxxxxxxxxxxxxx

Previous Posts In This Thread:

On Tuesday, April 17, 2007 12:37 PM
Nigel A wrote:

Cursors VS Temp tables
I have noticed a couple of posts that have expressed dissatisfaction with
cursors when compared to temp tables. Now I come from a VFP background where
cursors are de rigueur so when they turned up in SQL2005 they seemed a great
idea to me. Can anyone explain if there is a good reason for preferring temp
tables or is it just personal taste or simply a preference for the old way
of doing things? Pointers to blogs on the subject welcome.

TIA
Nigel Ainscoe

On Tuesday, April 17, 2007 1:41 PM
Tom Cooper wrote:

There is nothing wrong with cursors except that 99.
There is nothing wrong with cursors except that 99.99% of the time there is
a faster set based way to do accomplish what you want. That's not always
true, sometimes a cursor is faster and sometimes it's the only way to do
something. But those cases are very rare. And the performance hit for
using cursors can be very large. And the performance hit gets worse as the
amount of data increases. Most SQL Server consultants have stories like "I
was hired by <so and so corporation> and they wanted to know how to fix a
particular stored procedure which ran in 5 seconds when the system was
implemented, but a year later with a year's worth of data in the system, it
now takes 15 minutes. I looked at the stored proc, it had a cursor, I
replaced the cursor with a set based solution and the stored proc ran in 2
seconds."

But it is not as simple as replacing cursors with with temp tables. It is
more becoming familiar with set based concepts and using them. The key to
begin successful with SQL Server (IMHO) is first, get the database schema
correct - particularily the correct amount of normalization, the correct
constraints, and the correct indexes. Then when writing your DML, think in
sets, you should be telling SQL Server what to do, not how to do it.

Avoidance of cursons is not not just personal taste. Because of the
performance problems, you should almost always avoid them. If you have a
problem which you can't solve without a cursor, bring the problem to this
group with a description of the problem, schema, and sample data (see
www.aspfaq.com/5006 for how to do this) and the odds are you will get a set
based solution that will run circles around any cursor.

Tom

"Nigel A" <nigel@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:177AFC27-BF2D-4D06-B19D-0085871AE53A@xxxxxxxxxxxxxxxx

On Tuesday, April 17, 2007 1:43 PM
Russell Fields wrote:

Nigel,The cursor question is the SQL equivalent of the GOTOs / NO GOTOs
Nigel,

The cursor question is the SQL equivalent of the GOTOs / NO GOTOs debates in
the 70's and 80's. There are blogs also for both sides of all of these the
argument.

From a technology point of view, cursors are more subject to the standard
coding problems of procedural code, are (most of the time) slower, and
therefore hold locks longer. All of this adds up to raising a reasonable
question about whether the familiarity of procedural code is worth the
costs.

My first effort is always to find a set-oriented way to do the job. That
leverages the strengths of SQL Server and is usually faster since it uses
the SQL Server to handle the process, rather than passing it back to the
interpretation of my TSQL procedural code. There is also plenty of help on
'thinking relational' rather that procedural. The Inside SQL Server books
by Izak Ben-Gan and others are a big help.

On the other hand, for some jobs a cursor is almost the only way to get the
job done. So, my take is to reserver cursors for when there is no other
good answer.

RLF


"Nigel A" <nigel@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:177AFC27-BF2D-4D06-B19D-0085871AE53A@xxxxxxxxxxxxxxxx

On Tuesday, April 17, 2007 2:55 PM
David Portas wrote:

Re: Cursors VS Temp tables
"Nigel A" <nigel@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:177AFC27-BF2D-4D06-B19D-0085871AE53A@xxxxxxxxxxxxxxxx

Processing data row by row using a temp table instead of a cursor is just a
form of cursor by another name. Set based processing should generally be
preferred instead of either approach. If you don't have much SQL experience
I suggest you avoid cursors and temp tables completely for the time being,
otherwise you will learn bad habits. Stick to writing set-based SQL and most
of the time you'll find you won't need the "cursor-style" solutions at all.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

On Tuesday, April 17, 2007 4:26 PM
Nigel A wrote:

Thanks for all the replies, I shall consider them next time I am tempted to
Thanks for all the replies, I shall consider them next time I am tempted to
use a cursor or a temp table.

Regards
Nigel Ainscoe MBCS, MCP

On Tuesday, April 17, 2007 5:36 PM
Steve Dassin wrote:

Re: Cursors VS Temp tables
Who can deny that confusion is a wonderful form of entertainment.
Thanks! -:)

On Tuesday, April 17, 2007 7:15 PM
Greg Linwood wrote:

One more thing Nigel - remember that temp tables & cursors are both
One more thing Nigel - remember that temp tables & cursors are both
materialised on disk in the tempdb (even table variables). Whichever you
use, you are forcing the output of your query to be spooled to disk in the
temp before being read back to the client. This is a sure way to slow things
down.

Wherever possible, try to use set based statements which don't usually
suffer this consequence

Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood


"Nigel A" <nigel@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5F354DB2-9526-4462-A826-326C6D7082B3@xxxxxxxxxxxxxxxx

EggHeadCafe - Software Developer Portal of Choice
In-Memory Data Caching for Performance
http://www.eggheadcafe.com/tutorials/aspnet/c924e9aa-9085-41b3-9821-e5008e6123d0/inmemory-data-caching-fo.aspx
.

Relevant Pages

  • Re: Updating multiple rows
    ... The temp table model in SQL ... I'm honestly curious because I don't know much about other SQL server ... A recent example posted here had a cursor ... > Okay, it's a performance improvement. ...
    (microsoft.public.sqlserver.programming)
  • Re: Updating multiple rows
    ... but it depends on the release of SQL Server. ... The temp table model in SQL ... A recent example posted here had a cursor ... transaction file/master file update from the mag tape days. ...
    (microsoft.public.sqlserver.programming)
  • Re: get some records and then call a stored proc once for each??
    ... This is actually probably worse than a cursor, depending on the size of the ... right kind of cursor (fast forward is usually acceptable for a temp table) ... > create proc foo ... > exec foo 3 ...
    (microsoft.public.sqlserver.programming)
  • Re: Why are cursors Bad in Sql 2000
    ... sets are going to be faster than the various 'cursor' solutions. ... These articles are also suggesting I use While loops ... > uses a temp table and a cursor on 10,000 rows. ...
    (microsoft.public.sqlserver.programming)
  • Re: Cursor printout without space between each loop
    ... One method is to insert the results into a temp table using INSERT ... ... DECLARE database_table_name CURSOR ... > FETCH NEXT FROM database_table_name INTO @table_name ...
    (microsoft.public.sqlserver.programming)