new to cursors

Remember that you should generally try to avoid using cursors at all. They
are not usually a good solution to a problem in SQL because of their
performance and resource constraints compared to the set-based alternatives.

My view is that the majority of cursors fall into one of three categories:
Procedural administrative tasks (a reasonable use of a cursor); Written by
procedural programmers who don't know SQL; Used to work around a poor data
design (e.g. lack of keys in tables). There are other cases but they are few
and far between in my experience.

--
David Portas
SQL Server MVP
--

Jul 20 '05 #3

Chuck Conover

David,
I'm not disagreeing exactly, but I would be interested in your opinion.
Typically I use cursors to load data from an outside source. Since you
can't control what that outside data looks like, I will do the following:

- load data into a temp table
- cursor thru the temp table, verifying data types and generally massaging
the data, if needed
- insert into our production table(s) if the data passes examination (done
in the cursor)

I'm not certain if you'd consider this an administrative task. Some of the
data checking we do would be hard or impossible to do using a set-based
structure, I think.

Appreciate your views.
Best regards,
Chuck

"David Portas" <RE****************************@acm.org> wrote in message
news:Jb********************@giganews.com...

Remember that you should generally try to avoid using cursors at all. They
are not usually a good solution to a problem in SQL because of their
performance and resource constraints compared to the set-based alternatives.
My view is that the majority of cursors fall into one of three categories:
Procedural administrative tasks (a reasonable use of a cursor); Written by
procedural programmers who don't know SQL; Used to work around a poor data
design (e.g. lack of keys in tables). There are other cases but they are few and far between in my experience.

--
David Portas
SQL Server MVP
--

Jul 20 '05 #4

"Chuck Conover" <cc******@commspeed.net> wrote in
news:10***************@news.commspeed.net:

David,
I'm not disagreeing exactly, but I would be interested in your
opinion. Typically I use cursors to load data from an outside
source. Since you can't control what that outside data looks like,
I will do the following:

- load data into a temp table> - cursor thru the temp table, verifying data types and generally massaging the data, if needed
- insert into our production table(s) if the data passes
examination (done in the cursor)

Here's what I do:

1) bcp the data to load into a staging table
2) using set-based logic to validate against master tables putting the
'clean' results into a 'good' table and 'bad' data into another
(for later reporting)
3) once all the validation is done, a single insert from the 'good'
table is used to slam all the data into the target table.
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com

Jul 20 '05 #5

As Pablo says, many data cleansing and transformation operations can be
performed using set-based statements, even if you don't have keys in your
source data. For the rest I would use a dedicated ETL tool. DTS is the
component that ships with SQLServer but there are also other packages
specifically designed to automate the type of data prep you are describing
and then load the cleansed data into your database. This approach has lots
of advantages over hand-coded conversions in an RDBMS that isn't really
designed and optimised for the job.

http://pervasive.datajunction.com/djcosmos/
http://www.embarcadero.com/products/dtstudio/index.html
http://www.informatica.com

--
David Portas
SQL Server MVP
--

Jul 20 '05 #6

David Portas (RE****************************@acm.org) writes:

My view is that the majority of cursors fall into one of three
categories: Procedural administrative tasks (a reasonable use of a
cursor); Written by procedural programmers who don't know SQL; Used to
work around a poor data design (e.g. lack of keys in tables). There are
other cases but they are few and far between in my experience.

I'll add one more case: you already have a stored procedures that
performs some complex logic, and this procedure accepts its input
in scalar parameters, and you want to apply that logic to entire
set of data.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #7

Erland Sommarskog <so****@algonet.se> wrote in
news:Xn*********************@127.0.0.1:

David Portas (RE****************************@acm.org) writes:

I'll add one more case: you already have a stored procedures that
performs some complex logic, and this procedure accepts its input
in scalar parameters, and you want to apply that logic to entire
set of data.

I'll counter the above with the fact that you're using the wrong
tool for the job. The SP was written to handle single row inserts
therefore if you plan on doing batch inserts, then the SQL should be
written and tuned accordingly. Unless you wish this side of sucky
performance. :)
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com

Jul 20 '05 #8

--CELKO--

>> My view is that the majority of cursors fall into one of three
categories:
Procedural administrative tasks (a reasonable use of a cursor);
Written by procedural programmers who don't know SQL; Used to work
around a poor data design (e.g. lack of keys in tables). There are
other cases but they are few and far between in my experience. <<

The only other one that comes to mind is an NP complete problem
(traveling salesman, etc.) where you can use the first near-optimal
answer. Being a set-oriented language, SQL tends to find the **entire
set** of solutions and that can take a **lot** of time. Thank god you
don't run intot hem very often.

Jul 20 '05 #9

Pablo Sanchez (ho******@blueoakdb.com) writes:

Erland Sommarskog <so****@algonet.se> wrote in
news:Xn*********************@127.0.0.1:
I'll add one more case: you already have a stored procedures that
performs some complex logic, and this procedure accepts its input
in scalar parameters, and you want to apply that logic to entire
set of data.

I'll counter the above with the fact that you're using the wrong
tool for the job. The SP was written to handle single row inserts
therefore if you plan on doing batch inserts, then the SQL should be
written and tuned accordingly. Unless you wish this side of sucky
performance. :)

I'm not talking of simple SPs that inserts a single row into a table.
I'm talking about stored procedures with more than 1500 lines of code,
and which calls plenty of over procedures, activating another 1500 lines
of code. Those lines of code include important business rules, that you
don't want to have duplicated in a scalar version of the procedure and
a table-oriented one. And when many of the calls to the procedure for
busieness reasons are in fact one off, there may be a performance penalty
of the procedure is rewritten to be table-oriented.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #10

Jason

Most of our data comes from outside sources in which we have no
control over the quality or accuracy of the data. As a business, we
felt that it was more important to get 99% of the data into our
systems, then deal with those rows that fail afterwards.

Since I was unable to figured out a way to do this as sets (since a
set either all works or all fails together) I have had to cursor
processing all over the place. I also make use of stored procedures
which (as far as I know) cannot be passed in data sets, only scalar
values.

Jul 20 '05 #11

Jason

Another thought (more of a question).

When you need to provide logging (auditing) of each transaction the
easiest way is to run through each row. Do the action, then log it. Go
to next row.

Am I wrong? What are the alternatives?

I provide a real life situation as an example. Derivatives (futures,
options, and future options) are stocks that expire at some date.
After their expiry date, I need to mark the record in our Security
Master table as being inactive. I want a log of each security that was
marked as inactive. If I do not use a cursor, how else can I do it?
The only other way I can think our is do perform the query twice (one
select, then one update) inside a begin / end transaction. The select
statement would give me info to output to a log file and the update
would mark it as inactive...

Jul 20 '05 #12

Erland Sommarskog <so****@algonet.se> wrote in
news:Xn*********************@127.0.0.1:

I'm not talking of simple SPs that inserts a single row into a
Nope, I didn't assume that you were ... I assumed complicated beasts.
Those lines of code include important business rules, that you don't
want to have duplicated in a scalar version of the procedure and a
table-oriented one.
If the batch processing is important, you do. If it's not something
that requires to be loaded within a time constraint, I completely
agree with you.

And when many of the calls to the procedure for busieness reasons
are in fact one off, there may be a performance penalty of the
procedure is rewritten to be table-oriented.

For example?
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com

Jul 20 '05 #13

> After their expiry date, I need to mark the record in our Security

Master table as being inactive.
Why? If the expiry date is recorded in your system then you already *know*
whether a stock has expired or not based on the current date and time. An
active / inactive column would just be redundant data. Put the status in a
view if you like - not in a table.
The only other way I can think our is do perform the query twice (one
select, then one update)
Yes. But you would need two statements whether you do it in a cursor or not.
It should be quicker without a cursor.
inside a begin / end transaction. The select

If you really wanted to do it you don't need a transaction but I don't see
the point of the AuditLog unless Stocks are going to change Inactive ->
Active as well as Active -> Inactive. On limited info here's a guess:

INSERT INTO AuditLog (status, col1, col2, .... )
SELECT 'inactive', col1, col2, ....
FROM Stocks
WHERE expirydate <= CURRENT_TIMESTAMP

UPDATE Stocks
SET status = 'inactive'
WHERE status = 'active'
AND EXISTS
(SELECT *
FROM AuditLog
WHERE status = 'inactive'
AND pkcol = Stocks.pkcol)

--
David Portas
SQL Server MVP
--

Jul 20 '05 #14

> Most of our data comes from outside sources in which we have no

control over the quality or accuracy of the data. As a business, we
felt that it was more important to get 99% of the data into our
systems, then deal with those rows that fail afterwards.

See my reply earlier in this thread. There are tools specifically designed
to solve this problem.

--
David Portas
SQL Server MVP
--

Jul 20 '05 #15

Pablo Sanchez (ho******@blueoakdb.com) writes:

Erland Sommarskog <so****@algonet.se> wrote in
news:Xn*********************@127.0.0.1:
And when many of the calls to the procedure for busieness reasons
are in fact one off, there may be a performance penalty of the
procedure is rewritten to be table-oriented.

For example?

We did take to task to rewrite one of our procedures to be table-oriented.
We did this, because this one creates an account transaction, updates
positions, balances and a whole lot more things. The scope for the database
transaction for this may be a singe account transaction, for instance a
simple deposit of money. The scope may also be over 50000 account
transactions, for instance capitalization of interest, or a corporate
action in a major company like Ericsson.

The outcome of this adventure is that we can now rewrite the multi-
transaction updaets to be set-based and be a lot faster than before.
But anything that is still one-by-one due to legacy is now slower,
say one second instead of 200 ms. Instead of having single values
in variables, it is now in 43 table variables, and that is of course
slower.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #16

David Portas (RE****************************@acm.org) writes:

After their expiry date, I need to mark the record in our Security
Master table as being inactive.

Why? If the expiry date is recorded in your system then you already *know*
whether a stock has expired or not based on the current date and time. An
active / inactive column would just be redundant data. Put the status in a
view if you like - not in a table.

It isn't that easy. I don't know Jason's business, but I know my own.

When an instrument has expired, it should indeed be inactivated, or
deregistered to use the terminology in our system. But you cannot
deregister if there are still are positions or unsettled trades. Even
if the instrument has expired, you may still have to register transactions
in it. For instance, you may not until now discover that you have
registered a trade for the wrong account, and have to cancel and
create a replacement note. So even if the instrument is expired, it
should still be fully valid in transactions - but of course there
should be validation that you don't specify a trade date after
expiration.

Once everything has been cleared up, all trades resulting from expiration
has been registered, and all unused options has been booked out, you
can deregister the instrument.

But there is of course no reason to use a cursor just because of this.
A very simple-minded approach is:

INSERT #temp(id)
SELECT id
FROM instruments
WHERE -- should be deregistered

UPDATE instruments
SET deregdate = getdate()
FROM instruments ins
JOIN #temp t ON ins.id = t.id

INSERT audirlog (...)
SELECT ...
FROM #temp ...
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #17

Erland Sommarskog <so****@algonet.se> wrote in
news:Xn*********************@127.0.0.1:

The outcome of this adventure is that we can now rewrite the
multi- transaction updaets to be set-based and be a lot faster
than before. But anything that is still one-by-one due to legacy
is now slower, say one second instead of 200 ms. Instead of having
single values in variables, it is now in 43 table variables, and
that is of course slower.

[ I don't know if you want to pursue it further so if you don't
respond, I'll assume not. ]

The fact that there's some legacy sounds like that legacy code needs
to be refactored as well. It's only natural that that's what needs
to be done when taking row-at-a-time and converting to set-based.
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com

Jul 20 '05 #18