Re: Nested Updates using SQLCLIENT classes


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:%23fIf0bFjIHA.4344@xxxxxxxxxxxxxxxxxxxxxxx
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..



Thanks for the detailed reply.

You also cleared up another issue that I often wondered about. I'm not a
database programmer, though like most experienced application/system people
I often think I know more than I do. <g>

Luckily I learned early on, to swallow my pride whenever dealing with a
complex query, and go find the local database guru to write one for me. Over
the years, while it is obvious that Oracle ain't SQLServer, and there are
subtle differences in relationships, etc., I've always been surprised by the
variation in the solutions - especially, when to my eyes, it looked like I
was asking the same question as the last time.

To me a cursor was just "A Cursor". Interesting to learn that that is not
necessary so. <g>

thanks
-ralph


.

Relevant Pages

  • Re: Opinions on approach, please...
    ... Code conversion is much more tricky. ... to update more than 32k records without a commit in-between. ... I advise you to do cursor definitions on working storage). ... I don't want to see ANY SQL code in them. ...
    (comp.lang.cobol)
  • Re: Opinions on approach, please...
    ... to update more than 32k records without a commit in-between. ... Translating the above to SQL it would be as follows. ... I advise you to do cursor definitions on working storage). ... exec sql open file-a end-exec. ...
    (comp.lang.cobol)
  • Re: Millions of Delete Statements
    ... You mention a cursor. ... Others have mentioned batching the commands, ... so I will throw out one that is less good (executing them ... get the whole SQL statement I don't know of another way to do it). ...
    (comp.databases.ms-sqlserver)
  • Re: Nested Updates using SQLCLIENT classes
    ... your explanation so I cannot say if a set-based operation is ... SQL is often called a "set-based" language, ... KEYSET or DYNAMIC cursor. ... lot more than SQL Server programmers as there's no significant performance ...
    (microsoft.public.data.ado)
  • Re: Daten schnell lesen =?ISO-8859-1?Q?=28Gegenst=FCck_zu_BU?= =?ISO-8859-1?Q?LK_INS
    ... Damit meinte ich eine direkte Verarbeitung mittels ... 10maligem FETCH (näheres findest Du unter DECLARE CURSOR). ... Ein DataReader in einer SQL Server Assembly ist ... Anweisungen auf dem Server erledigt werden kann. ...
    (microsoft.public.de.sqlserver)