avoid using cursors

[David Portas]

Re: avoid using cursors

> If there's a better/more appropriate way of doing this I'd be[color=blue]
> interested to hear it.[/color]

Very likely there is a better way but we'll need more details first:

http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--

[John A Fotheringham]

Re: avoid using cursors

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote:
[color=blue]
>I would recommend that you don't use cursors in triggers. As already
>discussed, if you have a legacy of procedural code that works on one
>row at a time then yes, you may be forced to call that code in a loop
>just because of the cost of rewriting your procedure in set-based form.
>That's a pity because some day you may want to update more than one row
>at a time and anyway you really don't need the overhead of a cursor
>declaration in a trigger, even for a single row.[/color]

So how would you advise to select a single row from the inserted table
without using a cursor?
[color=blue][color=green]
>> A lot of the "set-based" solutions I see here
>> just seem to be implementing loops using a table and from a purely
>> programming point of view (my background) seem a counter-intuitive[/color]
>way[color=green]
>> of doing things.[/color]
>
>I don't know what you are referring to. Maybe you have an example? If
>you mean using WHILE loops and SELECT statements in place of cursors
>then yes, that is just cursor in disguise and all my comments about
>cursors apply equally to those other row-by-row constructs.[/color]

Well that's what I thought. Most of these approaches seem to be a
loop through table, and it's not obvious (to the newbies) that this
would be more efficient than a cursor.
[color=blue]
>It is true that declarative SQL requires a slightly different mindset
>and it is often noted that procedural programmers find these methods
>counter-intuitive. However, the relational model and SQL are the
>dominant industry database standards with good reason and that is
>hopefully a good enough incentive for the programmer to learn the
>standard techniques and best-practices, aside from the very practical
>consideratio ns already explained.[/color]

I'm not disputing this, just asking questions so I can better get into
the correct mindset for SQL.
--
HTML-to-text and markup removal with Detagger

http://www.jafsoft.com/detagger/

[John A Fotheringham]

Re: avoid using cursors

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote:
[color=blue][color=green]
>> If there's a better/more appropriate way of doing this I'd be
>> interested to hear it.[/color]
>
>Very likely there is a better way but we'll need more details first:
>http://www.aspfaq.com/etiquette.asp?id=5006[/color]

Well I was really just asking a generic question. The tables involved
are fairly lengthy (although as far as the logic goes only a few
fields apply) and I didn't think to fill my post with all the details.
I wasn't seeking a particular solution, just asking the general
question.

In essence I have

CREATE TABLE transaction
(
ID int IDENTITY(1,1),

ts datetime
job varchar(20)
status char(1)
...
other transaction fields
...
)

CREATE TABLE jobs
(
job varchar(20)
status char(1)
last_update datetime
...
other job fields
...
)

Each time a transaction comes in I use the details in the transaction
to update the jobs table. If it's a new job I create a new record,
otherwise I perform an update. The nature of the update can depend
on the value of the new status, so that depending on the status
different values amongst the "other transaction fields" will cause
different updated for the "other job fields". Further processing may
occur for some status codes.

To achieve this I wrote a "processTrn " procedure which takes a single
transaction and executes all the (largely procedural) updates.

To call this procedure I created a trigger on the transactions table,
and it's there that I use a cursor to go through the "inserted" table
to extract each new record in turn and call the procedure on it.

To my mind this is a naturally loop+procedural process.

Note, because the transactions table is added to 1 record at a time by
an external process, the actual cursor loops in this case are usually
for a single record.

--
HTML-to-text and markup removal with Detagger

http://www.jafsoft.com/detagger/

[David Portas]

Re: avoid using cursors

> So how would you advise to select a single row from the inserted
table[color=blue]
> without using a cursor?[/color]

I *wouldn't* select a single row. The problem is precisely to AVOID
processing single rows of data and process the whole set of data at
once. This is what we mean by "set-based" code. By putting business
logic in a stored proc that opeartes only on one row at a time you have
forced yourself to call that proc once for each row. It likely doesn't
have to be that way but since you haven't explained what the proc does
I can't really advise on the alternatives.

--
David Portas
SQL Server MVP
--

[David Portas]

Re: avoid using cursors

> If it's a new job I create a new record

INSERT INTO Jobs (job, ...)
SELECT job, ...
FROM Inserted
WHERE NOT EXISTS
(SELECT *
FROM Jobs
WHERE job = Inserted.job)

Note however that you should generally avoid duplicating data between
tables (except for key columns). Duplicated data is a problem in a
relational database and the goal of Normalization in db design is to
eliminate it. You should also aim to eliminate transitive dependencies
- i.e. columns that can always be derived from data in other (non-key
columns) - doing so reduces the need for triggers.
[color=blue]
> otherwise I perform an update[/color]

UPDATE Jobs
SET .... ?
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE job = Jobs.job AND ... ?)
[color=blue]
> The nature of the update can depend
> on the value of the new status, so that depending on the status
> different values amongst the "other transaction fields" will cause
> different updated for the "other job fields".[/color]

That's not much information to go on but you could probably use CASE
expressions for this.

--
David Portas
SQL Server MVP
--

[John A Fotheringham]

Re: avoid using cursors

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote:
[color=blue][color=green]
>> If it's a new job I create a new record[/color]
>
>INSERT INTO Jobs (job, ...)
> SELECT job, ...
> FROM Inserted
> WHERE NOT EXISTS
> (SELECT *
> FROM Jobs
> WHERE job = Inserted.job)[/color]

Thanks. I begin to see how the procedural approach can be avoided.


--
HTML-to-text and markup removal with Detagger

http://www.jafsoft.com/detagger/

[tal_mcmahon@hotmail.com]

Re: avoid using cursors

LOL,
yeah I guess you are right must have been late.

[Erland Sommarskog]

Re: avoid using cursors

John A Fotheringham (jafsoft@gmail. com) writes:[color=blue]
> This is the only situation in which I've used a cursor so far.
>
> I have a trigger set on insert into one table, and for each inserted
> record I want to use it's contents to create and/or update the
> contents of a record in a second table.
>
> I've written a procedure to do the fairly complex update from one
> record to another, and I call that procedure from inside a fairly
> simple cursor loop that forms the main body of the trigger procedure.
>
> I'm not too worried about cursor overheads here, because in general
> only one record at a time is being inserted into the first table.[/color]

Normally, it is not a good idea ot have a cursor in a trigger, but if
you know your business well enough to be confident that one row-at-a-time
is the normal case, this sounds like a sound approach to me. From a
theoretical point of view, the trigger certainly could be improved. But as
long as the penalty for the cursor is low or non-existent, it seems very
difficult to justify spending time on a more complex solution.

That cannot be denied, if you want to encapsulate logic by putting
it in stored procedures, this is easier for scalar values than for
sets of values, since procedure parameters are scalar. It is possible
to work around this by sharing temp tables or similar, but only does
this increase complexity. You can also get recompilation issues that
are bad for performance.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp