May 18, 2018 at 10:22 am
In what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !
May 18, 2018 at 10:31 am
I would stay away from cursors unless you really need to use them. I find that most of the time cursors are good when you need to accomplish maintenance tasks. For other tasks set-based solutions are usually better for both performance and scalability.
May 18, 2018 at 10:37 am
There are some tasks that can only be accomplished with cursors, for example loop through a result set and run a SP for every record in it. Those are the times to use cursors, otherwise generally don't use them.
May 18, 2018 at 10:57 am
ZZartin - Friday, May 18, 2018 10:37 AMThere are some tasks that can only be accomplished with cursors, for example loop through a result set and run a SP for every record in it. Those are the times to use cursors, otherwise generally don't use them.
I would say "It depends." If you are sending emails based on the results, then yes. That usually requires a row by row process. Deciding to use a cursor requires some analysis. If there is a set-based solution it will probably perform and scale better than a cursor-based solution.
Cursors are a tool. If used appropriately they are good. Used inappropriately they are evil.
May 18, 2018 at 11:31 am
I can think of another example of when to use one. I had a work scenario where the business rules for placing a new phone number into a given "slot" in an existing record required that you examine potentially multiple input rows before deciding which number went in which slot, and having decided that a given input row was now "used", you had to be sure it didn't get selected a 2nd time. I was unable to see any viable way to make such a decision in a set-based way, as the update for one incoming row was dependent on both the potential 2nd row as well as the existing data in that row... There was just no functional way to do it. I even considered trying to use quirky update, but even that would not have worked, as the order in which the updates needed to occur would not have agreed with the clustered index order. I also tried several fancy CASE statement evaluations, but because the dependency was on a previous or subsequent row, there was just no way to make it work. Wrote a cursor. It wasn't pretty, but it wasn't a performance dog either.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 19, 2018 at 3:11 am
Ronnie65 - Friday, May 18, 2018 10:22 AMIn what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !
As a general rule, one uses a cursor when NOT working on a set, i.e. when importing files with openrowset etc.
😎
May 21, 2018 at 7:26 am
I would suggest the following two scenarios are appropriate for cursors:
1) When you need to do something outside of CRUDing data in one or more table. (e.g. scripts to rebuild fragmented indexes, send email to an address based on the contents of a field)
2) When using a set based approach would result in code that is really difficult to read and debug. E.g. if you had to create a half dozen temp tables to get your data into a format where you could do set-based processing, it may be easier to understand the code using the cursors
I would try to avoid cursors for results that need to be run in 'real-time'. They are OK for housekeeping and overnight jobs where speed of response is not an issue. Personally I try to find a set-based solution whenever possible because I like the intellectual challenge.
May 22, 2018 at 12:59 pm
Ronnie65 - Friday, May 18, 2018 10:22 AMIn what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !
The main reason we have them in SQL is that the original SQL engines back in the 1980s were built on top of existing filesystems. Also, as Jim great once said, we didn't know what the hell we were doing 🙂 the filesystems already had cursors in them. And if you grew up with IBM tape filesystems, it would recognize all of the basic cursor syntax is literally the commands from the original IBM tape files with a little different syntax.
I've written five cursors in my career, and I know the three of them should not have been written because they could do it with declarative code. Back in the old Sybase days, when we were teaching cursors, the example was updating the bookstore sample table. The problem is to give a discount on the expensive books (price greater than $25) for advertising purposes, but to raise the price on the cheap books. The naïve code look like this:
BEGIN
IF book_price <= 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 1.10
END IF;
IF book_price > 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 0.90
END IF;
END;
now consider a book priced at exactly $25. The first update in the sequence raises the price to $27.50. But now, but now the code goes sequentially to the next update and adjust the price to $24.75. This is clearly wrong.
The training example solution was to write a simple cursor with a row by row piece of logic to control the update. Later, we got the CASE expression and could do this with a simple update statement after SQL–92.
If you can find a computer science major, he might be able to dig up one of those horrible and mostly useless formal proofs that it is always possible to translate in LALR (1) procedural language into declarative primitive recursive functions.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 22, 2018 at 1:07 pm
jcelko212 32090 - Tuesday, May 22, 2018 12:59 PMRonnie65 - Friday, May 18, 2018 10:22 AMIn what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !The main reason we have them in SQL is that the original SQL engines back in the 1980s were built on top of existing filesystems. Also, as Jim great once said, we didn't know what the hell we were doing 🙂 the filesystems already had cursors in them. And if you grew up with IBM tape filesystems, it would recognize all of the basic cursor syntax is literally the commands from the original IBM tape files with a little different syntax.
I've written five cursors in my career, and I know the three of them should not have been written because they could do it with declarative code. Back in the old Sybase days, when we were teaching cursors, the example was updating the bookstore sample table. The problem is to give a discount on the expensive books (price greater than $25) for advertising purposes, but to raise the price on the cheap books. The naïve code look like this:
BEGIN
IF book_price <= 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 1.10
END IF;
IF book_price > 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 0.90
END IF;
END;now consider a book priced at exactly $25. The first update in the sequence raises the price to $27.50. But now, but now the code goes sequentially to the next update and adjust the price to $24.75. This is clearly wrong.
The training example solution was to write a simple cursor with a row by row piece of logic to control the update. Later, we got the CASE expression and could do this with a simple update statement after SQL–92.
If you can find a computer science major, he might be able to dig up one of those horrible and mostly useless formal proofs that it is always possible to translate in LALR (1) procedural language into declarative primitive recursive functions.
Well, Joe, that's because your code above is flawed, period.
May 22, 2018 at 3:56 pm
Lynn Pettis - Tuesday, May 22, 2018 1:07 PMWell, Joe, that's because your code above is flawed, period.
So, what is wrong with the code you posted. Where to start?
First, your IF statement will fail.
Second, if either UPDATE statement DID execute either of them would update the Price of ALL the books in the table.
Third, would you REALLY call the table that contains books Bookstore? If so, you are violating 3rd normal form as you would have to repeat all the Bookstore information with each book.
Now, IF you were to write a cursor for this it would look more like this:
CREATE TABLE [dbo].[Books] (-- Calling the table Bookstore is just wrong as it would violate 3rd normal to list books in the Bookstore table
ISBN VARCHAR(13) NOT NULL PRIMARY KEY NONCLUSTERED -- be sure to include CHECK CONSTRAINT to validate ISBN
, Title VARCHAR(128) NOT NULL
, Price DECIMAL(10,2) NOT NULL);
GO
INSERT INTO [dbo].[Books]
VALUES ('978030640615X','Just A Book', 20.00)
,('9780306406151','Just Another Book', 25.00)
,('9780306406152','Just A Third Book', 30.00);
GO
DECLARE @ISBN VARCHAR(13)
, @Price DECIMAL(10,2);
DECLARE Books INSENSITIVE CURSOR
FOR SELECT [ISBN], [Price] FROM [dbo].[Books] ORDER BY [ISBN]
FOR READ ONLY;
OPEN [Books];
FETCH NEXT FROM [Books]
INTO @ISBN, @Price;
-- The following is a simplified WHILE LOOP for cursor processing. It should do more
-- but is for illistration only.
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Price <= 25.00
UPDATE [dbo].[Books] SET [Price] = [Price] * 1.10 WHERE [ISBN] = @ISBN
ELSE
UPDATE [dbo].[Books] SET [Price] = [Price] * 0.90 WHERE [ISBN] = @ISBN;
FETCH NEXT FROM [Books]
INTO @ISBN, @Price;
END
CLOSE [Books];
DEALLOCATE [Books];
GO
DROP TABLE [dbo].[Books];
GO
May 22, 2018 at 9:13 pm
ZZartin - Friday, May 18, 2018 10:37 AMThere are some tasks that can only be accomplished with cursors, for example loop through a result set and run a SP for every record in it. Those are the times to use cursors, otherwise generally don't use them.
I don't use cursors even for that. they just seem too cumbersome compared to other methods.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2018 at 9:19 pm
Lynn Pettis - Tuesday, May 22, 2018 1:07 PMjcelko212 32090 - Tuesday, May 22, 2018 12:59 PMRonnie65 - Friday, May 18, 2018 10:22 AMIn what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !The main reason we have them in SQL is that the original SQL engines back in the 1980s were built on top of existing filesystems. Also, as Jim great once said, we didn't know what the hell we were doing 🙂 the filesystems already had cursors in them. And if you grew up with IBM tape filesystems, it would recognize all of the basic cursor syntax is literally the commands from the original IBM tape files with a little different syntax.
I've written five cursors in my career, and I know the three of them should not have been written because they could do it with declarative code. Back in the old Sybase days, when we were teaching cursors, the example was updating the bookstore sample table. The problem is to give a discount on the expensive books (price greater than $25) for advertising purposes, but to raise the price on the cheap books. The naïve code look like this:
BEGIN
IF book_price <= 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 1.10
END IF;
IF book_price > 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 0.90
END IF;
END;now consider a book priced at exactly $25. The first update in the sequence raises the price to $27.50. But now, but now the code goes sequentially to the next update and adjust the price to $24.75. This is clearly wrong.
The training example solution was to write a simple cursor with a row by row piece of logic to control the update. Later, we got the CASE expression and could do this with a simple update statement after SQL–92.
If you can find a computer science major, he might be able to dig up one of those horrible and mostly useless formal proofs that it is always possible to translate in LALR (1) procedural language into declarative primitive recursive functions.
Well, Joe, that's because your code above is flawed, period.
I'm thinking that was the whole point. He did call it the "naïve" code, which I took as a much more gentle way of saying "This is an example of what can go wrong if you're an idiot". 😀 And, no... Joe didn't write that. It's the example from the scenario he described.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2018 at 10:26 pm
Jeff Moden - Tuesday, May 22, 2018 9:19 PMLynn Pettis - Tuesday, May 22, 2018 1:07 PMjcelko212 32090 - Tuesday, May 22, 2018 12:59 PMRonnie65 - Friday, May 18, 2018 10:22 AMIn what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !The main reason we have them in SQL is that the original SQL engines back in the 1980s were built on top of existing filesystems. Also, as Jim great once said, we didn't know what the hell we were doing 🙂 the filesystems already had cursors in them. And if you grew up with IBM tape filesystems, it would recognize all of the basic cursor syntax is literally the commands from the original IBM tape files with a little different syntax.
I've written five cursors in my career, and I know the three of them should not have been written because they could do it with declarative code. Back in the old Sybase days, when we were teaching cursors, the example was updating the bookstore sample table. The problem is to give a discount on the expensive books (price greater than $25) for advertising purposes, but to raise the price on the cheap books. The naïve code look like this:
BEGIN
IF book_price <= 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 1.10
END IF;
IF book_price > 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 0.90
END IF;
END;now consider a book priced at exactly $25. The first update in the sequence raises the price to $27.50. But now, but now the code goes sequentially to the next update and adjust the price to $24.75. This is clearly wrong.
The training example solution was to write a simple cursor with a row by row piece of logic to control the update. Later, we got the CASE expression and could do this with a simple update statement after SQL–92.
If you can find a computer science major, he might be able to dig up one of those horrible and mostly useless formal proofs that it is always possible to translate in LALR (1) procedural language into declarative primitive recursive functions.
Well, Joe, that's because your code above is flawed, period.
I'm thinking that was the whole point. He did call it the "naïve" code, which I took as a much more gentle way of saying "This is an example of what can go wrong if you're an idiot". 😀 And, no... Joe didn't write that. It's the example from the scenario he described.
Take away my fun, geez. 😀 😛
May 23, 2018 at 1:38 am
Jeff Moden - Tuesday, May 22, 2018 9:19 PMLynn Pettis - Tuesday, May 22, 2018 1:07 PMjcelko212 32090 - Tuesday, May 22, 2018 12:59 PMRonnie65 - Friday, May 18, 2018 10:22 AMIn what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !The main reason we have them in SQL is that the original SQL engines back in the 1980s were built on top of existing filesystems. Also, as Jim great once said, we didn't know what the hell we were doing 🙂 the filesystems already had cursors in them. And if you grew up with IBM tape filesystems, it would recognize all of the basic cursor syntax is literally the commands from the original IBM tape files with a little different syntax.
I've written five cursors in my career, and I know the three of them should not have been written because they could do it with declarative code. Back in the old Sybase days, when we were teaching cursors, the example was updating the bookstore sample table. The problem is to give a discount on the expensive books (price greater than $25) for advertising purposes, but to raise the price on the cheap books. The naïve code look like this:
BEGIN
IF book_price <= 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 1.10
END IF;
IF book_price > 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 0.90
END IF;
END;now consider a book priced at exactly $25. The first update in the sequence raises the price to $27.50. But now, but now the code goes sequentially to the next update and adjust the price to $24.75. This is clearly wrong.
The training example solution was to write a simple cursor with a row by row piece of logic to control the update. Later, we got the CASE expression and could do this with a simple update statement after SQL–92.
If you can find a computer science major, he might be able to dig up one of those horrible and mostly useless formal proofs that it is always possible to translate in LALR (1) procedural language into declarative primitive recursive functions.
Well, Joe, that's because your code above is flawed, period.
I'm thinking that was the whole point. He did call it the "naïve" code, which I took as a much more gentle way of saying "This is an example of what can go wrong if you're an idiot". 😀 And, no... Joe didn't write that. It's the example from the scenario he described.
Thanks for posting that Jeff. This is almost certainly going to lead to Lynn apologising profusely to Joe and that apology being accepted with great humility. The war of words will end and the whole community will be happy. That, or more likely thing will continue as normal.
May 23, 2018 at 5:51 am
paul s-306273 - Wednesday, May 23, 2018 1:38 AMThanks for posting that Jeff. This is almost certainly going to lead to Lynn apologising profusely to Joe and that apology being accepted with great humility. The war of words will end and the whole community will be happy. That, or more likely thing will continue as normal.
At the very least, it could lead to the temporary decision of "Today, I shall not kill".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply