new to cursors

[Erland Sommarskog]

Re: new to cursors

Pablo Sanchez (honeypot@blueo akdb.com) writes:[color=blue]
> Erland Sommarskog <sommar@algonet .se> wrote in
> news:Xns9480365 5A9EEYazorman@1 27.0.0.1:[color=green]
>> 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.[/color]
>
> For example?[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

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

[Erland Sommarskog]

Re: new to cursors

David Portas (REMOVE_BEFORE_ REPLYING_dporta s@acm.org) writes:[color=blue][color=green]
>> After their expiry date, I need to mark the record in our Security
>> Master table as being inactive.[/color]
>
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

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

[Pablo Sanchez]

Re: new to cursors

Erland Sommarskog <sommar@algonet .se> wrote in
news:Xns94814EA FE04FYazorman@1 27.0.0.1:
[color=blue]
> 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.[/color]

[ 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

[Erland Sommarskog]

Re: new to cursors

Pablo Sanchez (honeypot@blueo akdb.com) writes:[color=blue]
> 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.[/color]

Needs to is a relative item.

I don't know how much work it took to do rewrite that particular
stored procedure, but I seem to recall that my time estimate was 100
hours. In a company like hours, 100 hours is not something you take out
of thin air.

Some of these jobs running one-by-one have been rewritten, rest assured.
But there is at least one process where a rewrite will take another
100 hours, maybe more.) (This one is however not keeping all calls in one
database transaction.) And I would not expect this to happen to either
we have someone coughing up money for it, or a customer yelling loud
enough about the performance. (The latter is of course much more likely
than the former.)

Anyway, this particular procedure that we rewrote had to be rewritten, for
our system being able to scale. But I have another case, where most calls
are one at a time, and where only one functions make sucessive calls, and
this function is used by one customer only. I'd be cautious before I init
a rewrite here.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

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

[Jason]

Re: new to cursors

Thanks for the imput guys. I thought about auditing everything as a
step and then performing the action as another step but I felt it was
somehow "wrong".

Erland summed it up nicely about the inactive status and unprocessed
trades. To add to that, there are other status's (like Halted) that
can be applied to a security. That is another reason for the Status
column.