Cursors - Are they always the wrong way
-
I usually never use cursors unless I don't have the time to make an alternative set based solution...
-
Cursors are fine. They are a tool, that's all.
It's as if you are claiming you never use the tack hammer. That's fine. There are times that a tack hammer is useful.
Patrick
Quand on parle du loup, on en voit la queue
-
Like the tack hammer comparison, but if you are working on the crown jewels of the company, bla,bla.
-
Look at CASE in SQL Books. With CASE you process by set and still set different columns to values depending on complex evaluations of columns in one or more joined tables. You can even leave totally non-qualifying columns be setting them as themselves (i.e. not changing them). It's like serially processing each row, but leaving all the work on the server and eliminating network traffic altogether.
In general, cursor processing is like using SQL Server as a file access method.
-
quote:
The most common use of the cursor I see is in a trigger to support multiple row transactions, where each row will result in a proc call (so that we get code reuse).In our system, we have 90% of our business logic in stored procedures. When somebody changes the pricing method at the order level, we have to go through every line on the order and reprice it. Repricing is done through a stored procedure.
Instead of cursors, we use while exists loops with a Select Top 1 to loop through the lines on the order. Example:
Set @LineCtr = 0
While Exists (
Select * From OrderLine
Where Order = @Order And
Line > @LineCtr
)
Begin
Select Top 1
@LineCtr = Line,
@OtherVar = OtherField
From OrderLine
Where Order = @Order And
Line > @LineCtr
Order By Line
... business logic goes here
End
Is this worse than using a cursor? I haven't been able to tell the difference with about 30,000 rows in the Order table and 500,000 rows in the OrderLine table.
-
Question: If you need to loop through several records to do work, such as updating status of items when orders are placed, how else would you go about doing it?
Thanks,
Johnny
-
quote:
Question: If you need to loop through several records to do work, such as updating status of items when orders are placed, how else would you go about doing it?The idea is not to 'loop' through rows. Rather to apply the logic to the set of rows. For example, if you had three different status values A-Available, O-On order and B-Back ordered. You could use a case statement for setting the status value. Another alternative is to update the rows in different statements for each condition enclosing the statements in a transaction.
Cursors use a lot of memory, generate a lot of network traffic and do not scale well at all. What works fine in test may fail miserably in production. What works initially in production may fail as load grows.
Be careful of cursors. Sure they are part of the ANSI spec but they are not by their very nature part of the architecture. They are at best a compromise. I have found that if you train yourself to think in terms of set based processing, you will begin to naturally see solutions without cursors. It takes time to alter your thinking, nut it's worth it.
Changing from DOS programming to Windows took time but think of how easy it is now.
-
A number of people have said cursors are a problem because of how much memory they use. I have seen a problem with speed and cursors, and haven't clearly seen the explanation for it anywhere yet.
It takes my code as long to process 3 rows of a 100,000 row table as 50,000 rows through a cursor.
This is insane.
Near as I can tell, somehow the cursor does *something* with each row in the table, regardless of whether the rows are in the result set or not.
Now, my solution to this doesn't help memory at all (in fact, it eats more), but helps speed a lot; where I know that relatively few records will normally need to be processed through the cursor, I create a table variable, store the output of (what would normally be) the cursor query there, then run the cursor on the table variable. Even if the cursor is monkeying around with every row, no problem - all the rows are of interest in the table variable.
I saw this cut a process that was taking hours to run down to completing in 5-10 minutes.
Just a suggestion. YMMV
RD Francis
R David Francis -
What cursor type? Local or global?
Andy
-
Hay dear ones,
Cursors are really easy to understand, but for queries taking ours together to complete may create problem, through out this time we have to block our users in order to avoid data inconsistency. I am having an experience of replacing one data-warehousing query that was taking more than one hour to merely two seconds
Here consider this sample cash table
Fin_tbl_cash
finDate OBPayments Receipts CB
5/1/2003 1000100 1500
5/2/2003 0230 1900
…………..
…………...
Here the problem is defined like we have to calculate CB and OB for entire month, normal tendency will be going for a cursor I am sure. Here just have a look at my alternative
The following script will serve the purpose I think, I am mailing this query with out checking
update A Fin_tbl_cash
set CB=(
select OB from Fin_tbl_cash B datepaart(month, B.finDate)=5 and datepaart(year, B.finDate)=2003 and datepaart(day, B.finDate)=1
)
+
(
select sum(Payments- Receipts) from
Fin_tbl_cash B where datepaart(month, B.finDate)=5 and datepaart(year,B.finDate)=2003 and datepaart(day, B.finDate)<= datepaart(day, A.finDate)
)
From
Fin_tbl_cash A
where
datepaart(month, finDate)=5 and datepaart(year, finDate)=2003Sub queries are not a much discussed field in sql if somebody starts a thread on that topic I will help my best
-
In the situation I described last week, the cursor in question was global.
RD Francis
R David Francis
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply