August 6, 2008 at 11:54 am
Hi members,
Can any one tell me the difference between cursor and while loop and suggest me which one is better along with an example fetching the records same through cursor and while.
thanks,
sainath
August 6, 2008 at 12:00 pm
Cursor opens a copy of the data, essentially and holds it. This isn't the most efficient way to do things. A While loop typically reruns a query to check if the loop should continue, which may or may not be faster.
If you post some code you're considering or explain more what you want to do, we can give more info and opinions.
August 6, 2008 at 12:07 pm
There really isn't much of a difference. A cursor basically builds a temp table to loop through and you would need to do the same with a while loop. Most problems can be solved without resorting to a cursor or while loop (RBAR - Jeff Moden would call it) and avoiding it usually will be more efficient.
That being said here are the examples you requested using AdventureWorks(note this is NOT how I would do this):
CURSOR:
[font="Courier New"]DECLARE @productid INT, @name NVARCHAR(50)
DECLARE c_products CURSOR FOR
SELECT
ProductID,
Name
FROM
Production.Product
OPEN c_products
FETCH Next FROM c_products INTO
@ProductID,
@Name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @productid < 100
BEGIN
UPDATE ProductIon.Product
SET Name = @name + CONVERT(NVARCHAR(2), @ProductID)
END
FETCH Next FROM c_products INTO
@ProductID,
@Name
END
CLOSE c_products
DEALLOCATE c_products
[/font]
WHILE:
[font="Courier New"]DECLARE @products TABLE(productid INT, name NVARCHAR(50), processed bit DEFAULT 0)
INSERT INTO @products (productid, name)
SELECT
ProductID,
Name
FROM
Production.Product
DECLARE @productid INT
WHILE EXISTS(SELECT * FROM @products WHERE processed = 0)
BEGIN
SELECT
@productid = MIN(productid)
FROM
@products
WHERE
processed = 0
IF @productid < 100
BEGIN
UPDATE ProductIon.Product
SET Name = name + CONVERT(NVARCHAR(2), ProductID)
WHERE
productId = @productid
END
UPDATE @products
SET processed = 1
WHERE
productid = @productid
END[/font]
As Steve said, if you post what you want to do, odds are you will get a solution better than a looping one.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 6, 2008 at 12:16 pm
This sounds like a test question to me. Or an interview question. Do you have a specific situation you are trying to resolve?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2008 at 12:46 pm
HI,
First of all I appreciate for your answer. Actually its not an interview question. I have a 1 million records in a table and I need to find out one particular record and have a join to some other table which is again 500,000 records and I need to apply cursor to do some operation on 2000 records.
so I was thinking how best should I solve this problem with good performance.
Thanks,
sainath
August 6, 2008 at 12:58 pm
As Steve said and I said in my earlier post, if you give us the problem we may be able to solve it using a set-based solution that will be must more efficient than looping.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 6, 2008 at 1:00 pm
Using a cursor or while loop may not be the best answer. If you provide more details about what you are trying to accomplish, plus provide sample tables, sample data, and expected results, we may be able to provide you with a highly performant set-based way to accomplish your task.
😎
August 6, 2008 at 1:20 pm
Hi,
So you mean to say cursor and while both take temp tables and also you said that you wont do like that then can pls explain me how you would do that.
Thanks,
sainath
August 6, 2008 at 1:37 pm
blnbmv (8/6/2008)
Hi,So you mean to say cursor and while both take temp tables and also you said that you wont do like that then can pls explain me how you would do that.
Thanks,
sainath
Unless we know what operation you need to do on the 2000 records we can't tell you how we would do it. If you read the links in my signature you and take a little time to present a complete question/scenario then we can tell how we would solve the problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 12, 2010 at 1:34 pm
Hi Jack Corbett, your example illustrated the basic use of Cursor and While Loop, can you use the same example and provide a set based example. I have a number of cursors which I want to replace with set based queries. (Most cursors do insert and update to a temp table from where final select statement is run). Thanks.
July 13, 2010 at 6:57 am
This site has just republished an excellent two-part article by R Barry Young on how to replace cursors in your code:
There Must Be 15 Ways to Lose Your Cursors[/url]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply