| ||||
Keyword Search
|
Converting Cursors to a Set Based Solution - Part I
The biggest hurdle a developer has to overcome when moving from traditional 3rd generation languages to T_SQL is moving the mindset from CURSOR based solutions to SET based solutions. Don't get me wrong, I believe CURSOR based solutions have their place, and when working with small datasets you won't notice much of a difference in speed. It is only when working with the lart datasets (100,000+ rows) you will notice a dramatic improvement in speed. I believe there is nothing like a few good examples so I will endeavour to provide multiple examples of the next few posts. Simple Example 1: We have two tables one contains a subset of the other; we need to move all the data from the main table to the subset table. Customer -------- ID Name StreetAddress Suburb City ZIP Sex MartialStatus MailingList ----------- Name Street Suburb City ZIP The CURSOR based solution to do this is:
DECLARE @Name varchar(50), @Street varchar(50)
DECLARE @Suburb varchar(50), @City varchar(50)
DECLARE @ZIP varchar(50)
DECLARE CustomerCursor CURSOR FOR
SELECT Name, StreetAddress, Suburb, City, ZIP
FROM Customer
OPEN CustomerCursor
FETCH NEXT FROM CustomerCursor INTO @Name, @Street, @Suburb, @City, @ZIP
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO MailingList
(Name, Street, Suburb, City, ZIP)
VALUES
(@Name, @Street, @Suburb, @City, @ZIP)
FETCH NEXT FROM CustomerCursor INTO @Name, @Street, @Suburb, @City, @ZIP
END
CLOSE CustomerCursor
DEALLOCATE CustomerCursor
The SET based solution to do this is:
INSERT INTO MailingList
SELECT Name, StreetAddress, Suburb, City, ZIP
FROM Customer
As you can probably see from the above example the simple SET based solution is much more efficient and easier to read. The SET based solution also has one other advantage, if the field sizes are to change then you don't need to worry about the code.
This article was posted on December 28, 2005 |
Disclaimer: The information presented and opinions expressed herein are those of the authors
and do not necessarily represent the views of ArticleCity.com and/or its partners.
|
Search ||
Bulk Article Submission ||
Submit An Article ||
Syndicate Articles
|