473,899 Members | 4,309 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

new to cursors

Can anyone point me to a good resource for learning cursors in MSSQL?

Thanks

Dave
Jul 20 '05
19 5773
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
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******** *************@1 27.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_TIMESTA MP

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******@blueo akdb.com) writes:
Erland Sommarskog <so****@algonet .se> wrote in
news:Xn******** *************@1 27.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******** *************@1 27.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
Pablo Sanchez (ho******@blueo akdb.com) writes:
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.


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #19
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.
Jul 20 '05 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
9065
by: Alban Hertroys | last post by:
Oh no! It's me and transactions again :) I'm not really sure whether this is a limitation of psycopg or postgresql. When I use multiple cursors in a transaction, the records inserted at the start of the transaction aren't visible to those later on in that transaction (using a different cursor). Attached is a simplified example (the except's are a bit blunt, I know) of what I'm trying to do. In reality, the different cursors are...
22
10682
by: T.S.Negi | last post by:
Hi All, I want to avoid using cursors and loops in stored procedures. Please suggest alternate solutions with example (if possible). Any suggestion in these regards will be appreciated. Thanks in advance, T.S.Negi
5
6202
by: Todd Huish | last post by:
I have noticed something disturbing when retrieving datasets over a relatively slow line (multiple T1). I am looking at about 25 seconds to retrieve 500 rows via a php-odbc link. This same select from the cli is for all intents practicaly instantaneous. After much research I discovered that PHP by default uses a dynamic cursor type which can be quite a bit slower than a forward only cursor. BTW I have been searching forward only/read...
6
2516
by: a | last post by:
Hello, I am doing some multithreading in an MDI app, and I can't seem to get the cursor to stay as an Hourglass. I call: Cursor.Current = cursors.wait at the beginning of my routing, and set it back to cursors.default when the thread ends (using a callback)
10
17404
by: Just Me | last post by:
Does Me.Cursor.Current=Cursors.WaitCursor set the current property of Me.Cursor to Cursors.WaitCursor And Me.Cursor.Current=Cursors.Default set the Me.Current property to something (default) stored in Me.Cursor. Or is Cursors.Default some process wide cursor shape? What is a correct statement?
5
1397
by: Boni | last post by:
Dear all, 1.Is there a standard set of cursors in windows which can be used? If yes, where. I need a "hand"- cursor for drag-drop operation. 2. What is a best practice to place cursors? In app directory or in resource file? Thanks, Boni
7
3558
by: H. Williams | last post by:
I know the .Net Cursor class doesn't work with color cursors. So I'm currently using the LoadCursorFromFile API with reflection to set color cursors: here is my code: public static extern IntPtr LoadCursorFromFile( string fileName ); IntPtr hwdCursor= LoadCursorFromFile( "color.cur" ); myCursor.GetType().InvokeMember("handle",BindingFlags.Public |
17
6828
by: vishal | last post by:
I am new to sql and require some help on cursors? what are they and how and why are they used for??? it will be kind enough if anyone helps me in this regards.. regards vishal jain.
3
2307
by: schwartzenberg | last post by:
Dear friends, I have just run into a strange DB2 problem. Something i'd some of you would answer, if only shortly. My basic question is: How do i ensure 'insensitive' (ie static) cursors that are only forward readable (in DB2 for mainframe)?? It seems that the cursors i'm working on suddenly have become (after
1
6702
by: Dima Kuchin | last post by:
Hello, I was trying to find the information about when and where should I use cursors in DB2, no luck. Maybe you can point me to some article that describes just that (or tell me which page is it in DB2 SQL reference book, if it's there)? Or maybe you can post a short answer here, if there's no dedicated article? Another question would be about scrollable cursors (just found out about their existence) - what performance gains will I...
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
10966
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10493
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
8039
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7201
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5886
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6081
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4299
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3316
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.