> What will happen with the cursor tomorrow when the servers become more
> multi-CPU?
> While cursors will be optimized up to the max speed of any given cpu
> (not completely true, but almost), the set-oriented solution will
> outperform cursors a lot. Also, optimizers tomorrow might get much
> better.
Maybe. But if I want an answer now, it does not help if the optimizer
will be able to run this query fast in SQL2005, if the query runs for
days in in SQL2000.
This kind of queries are difficult for the optimizer, and the only
good answer is probably better language support. Many, many years ago
I worked with DEC/Rdb. There wasn't an SQL, only their own query language
RDO. There was a table which had a type column, and I was interested
in knowing the distributions of the various type values. But RDO had
no direct language support for this, so I wrote a nested loop. I seem
to recall that the execution time was over an hour, and this in a table
with maybe 25000 rows. Then, towards, the end of my stint at this place,
there came a new version of Rdb, which included SQL support. To compare,
I ran the SQL thing with GROUP BY. It finished in 30 seconds.
Quote:> What do you prefer:
> a) Your programs lives 1-2 years (tech is on the current level)
> b) Your program lives 10-15 years (program is meaningfull even though
> tech advances)
That I can deliver now what my customer is asking for.
Quote:> IMHO, cursors should be used only for support procedures and not in
> production. If one can't solve a DB problem without cursors means that
> he failed to design his database properly.
You are wrong on that point. There are real-world problems which are
difficult enough to solve with an iterative soluition. Doing it set-
based is almost impossible. Here is breif description of such a case:
you have a chain of transactions in a stock. The acquisition cost of
a position at any time is the sum of all buy transactions since you
more recently changed sign on the position. (Note here that there might
be a transaction which you only need to count half of.) You must account
for the the stock have subjects to various corporate actions such as
split, merger, rights issue and so on. The rules for their impact on
acquisition cost is typically defined by the tax authorities, and they
don't care about relational databases.
Apart from that, there are other cases where it's good choice to use a
cursor: you have a stored procedure which performs a complex update,
but takes parameters only updates one row. You want to reuse that logic,
even if you need to update many rows. To this end, you need to run a
loop. As long as you understand the performance implications, this may
be alright. For instance, there might be 30 rows to handle, and the
procedure only needs 100 ms to complete.
--
Erland Sommarskog, Abaris AB
SQL Server MVP