Re: Nested Updates using SQLCLIENT classes

Ralph wrote:
"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:e%23GTfoCjIHA.5088@xxxxxxxxxxxxxxxxxxxxxxx
<snipped>

1. From what I explained, does it feel that I am approaching the
solution in a wrong way?

I probably would not have done it your way. I tend to go for
set-based operations if possible. It takes a lot to convince me a
set-based operation is not possible. Ican't make heads-nor-tails of
your explanation so I cannot say if a set-based operation is
possible in your situation.

<snipped>

I'm going to highjack this thread for a moment.

I use the term "set-based solution" a lot, but just now you got me
thinking that maybe I don't know what I'm talking about... <g>

A 'set-based' solution is one where you do all the fetch and filter
up front to retrieve a sack of stuff or at least as much of the stuff
as you can that answers all the requirements. A 'non-set-based'
solution is where you grab various bags or stuff and then enumerate
through the bags one or more times to gleam the information. Or is
there more I'm missing? <g>


No, you've pretty much got it. SQL is often called a "set-based" language,
in that operations are performed with a single command on a set of data. I
think of it as the opposite of iterative operations. e.g., grabbing a bunch
of data in a cursor and looping through it.

set-based:
update tablename set col='something' where somecondition is true

iterative:
open a recordset and loop through it, setting the col to "something" where
the condition is true (if you have failed to use a filter in the select
statement used to open the recordset). Note: even if you apply the filter in
the sql statement, the act of looping through the records to perform the
update makes it an interative operation rather than a set-based one.

I came across this interesting post from Greg Linwood in another place:

I think it's interesting to consider that all queries are processed
internally with cursor-style execution, whether they are written as set
based statements or as cursors. Take a simple query such as SELECT * FROM
MYTABLE - this set based expression is executed with a SCAN opeartor (either
table or CIX scan). During query execution, the SCAN operator iterates over
every slot (row) in every page in the table in a cursor style of execution.

If you wrapped the statement in a t-sql language cursor, there is certainly
more overhead but how much more depends on whether you're using a STATIC,
KEYSET or DYNAMIC cursor. The STATIC cursor first copies the entire
resultset to the tempdb, writes it to disk & then iterates over it. The
KEYSET copies just the key columns to tempdb, writes them to disk & iterates
over the keys, going back to the user db for non-key columns. The DYNAMIC
cursor maintains key values in variables & then seeks into indexes in the
user table for every iteration.

All three types have different inefficiencies but the first two (STATIC /
KEYSET) can be extremely inefficient due to the fact they materialise
resultsets to disk in the tempdb. In otherwords, these cursor types convert
what would other wise be read only operations into read (from source) +
write (to tempdb) + reads (from tempdb). Clearly this is inefficient & you'd
only do it to achieve isolation from changes in the underlying data source
in the user db.

DYNAMIC is far more efficient in that it doesn't re-write resultsets back to
the tempdb disk. It works by storing key values in variables & seeking into
indexes for every cursor operation. Whilst this is certainly more efficient
than the other two, it's big draw back is that each seek operation requires
usually 2 to 3 page reads, which means significantly more latching & locking
(which in turn means waiting in queues) in addition to extra IO.

So, T-SQL cursors are always less efficient than the internal cursors used
to process set based commands, but this has more to do with the way cursors
have been implemented in SQL Server than anything. Oracle has another type
of cursor option (REFCURSOR) which allows the programmer to write cursor
code which has much closer execution semmantics to "internal" cursors than
the T-SQL cursor options. Hence, Oracle programmers tend to use cursors a
lot more than SQL Server programmers as there's no significant performance
penalty in doing so. So the difference isn't so much about the relative
approaches taken by Oracle vs SQL Server programmers as it is about the
inefficiencies in SQL Server's cursor implementation. If SQL Server
implemented a cursor feature as efficient as the REFCURSOR, I'm sure cursors
would be used more widely amongst SQL Server programmers.

Then again, there's a very good argument that programming with cursors
effectively turns an SQL DBMS system into an ISAM DBMS system & that using
set based expressions vs cursors has more to do with modern programming
techniques than efficiency. There's a lot to be said for using simpler
set-based expressions than spaghetti cursor code, as it allows code to be
developed / changed more efficiently than with cursor spaghetti. This is
where my opinion rests - using set based expressions is really more about
system development agility than performance. Ultimately cursors can actually
perform equally well as set based code (if cursors are implemented
efficiently such as with REFCURSOR) but SQL Server's cursors aren't as
efficient as they should be & it's really about coding style anyway..



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: C5 woes. How stable is VFP really?
    ... After that I close the SQL server cursor and work with my manual cursor instead. ... a VFP9 app on a really old machine ... app was under more "pressure", VFP showed ...
    (microsoft.public.fox.programmer.exchange)
  • Re: SQL HELP PLEASE!! Cursor only returns part of the data
    ... First of all, it's not the cursor as such that is bad, it is the looping. ... and re-writing it to handle set-based data will cost you more ... set-based solution do not perform well, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Using Cursors
    ... it seems I've stirred up a hornet's nest with my cursor question. ... finding the discussion valuable in my circumstances (moving from Oracle to ... SQL Server). ... > DECLARE curEpisode SCROLL CURSOR FOR ...
    (microsoft.public.sqlserver.programming)
  • Re: An ALTER TABLE on a huge table...
    ... Columnist, SQL Server Professional ... We know for large tables the transaction log growth will be huge, and perhaps too much for some customers machines. ... newly added field to 0 in all rows using a cursor, then swithcing back to the original recovery model. ... Perhaps BCP out, truncate table, alter table, BCP back? ...
    (microsoft.public.sqlserver.server)
  • Re: Row Order
    ... morphed into a flat-file design into SQL Server. ... Server is a relational database that assumes set operations and doesn't let ... > somebody doesn't want to have to add an ORDER BY clause to the cursor ...
    (microsoft.public.sqlserver.programming)