September 23, 2009 at 12:00 am
Hi,
What is set base code ?
Can anyone explain with suitable example.
Why we should opt for set base code instead of cursor or loop ?
Thanks
[font="Verdana"]Regards
Kumar Harsh[/font]
September 23, 2009 at 1:56 am
It would be too long to explain in a forum question: I suggest you look for articles around.
http://www.google.com/search?q=set+based+code
Basically, the main concept here is that set based code leaves the task of joining, filtering etc. to the database engine, rather than trying to "do it yourself" with cursors. The engine "knows better" how to work on data, don't try to beat it. Set based code will always be faster.
Stupid, but very clear example:
CURSOR BASED
DECLARE @id int
DECLARE cur CURSOR
FOR
SELECT Id
FROM MyTable
WHERE Column2 = 0
OPEN cur
FETCH NEXT FROM cur INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE AnotherTable
SET SomeColumn = 0
WHERE Id = @Id
FETCH NEXT FROM cur INTO @id
END
CLOSE cur
DEALLOCATE cur
SET BASED
UPDATE AnotherTable
SET SomeColumn = Column2
FROM AnotherTable AS A
INNER JOIN MyTable AS B
ON A.Id = B.Id
WHERE B.Column2 = 0
Both pieces of code update AnotherTable, setting the value 0 in the column SomeColumn for all the Ids in MyTable that have the value 0 in Column2. The set based one is MUCH faster.
Hope this helps.
Gianluca
-- Gianluca Sartori
September 23, 2009 at 1:59 am
I think Jeff Moden says it best..
Stop thinking ROW, start thinking COLUMN..
CEWII
September 23, 2009 at 2:17 am
Jeff Moden always says it best!:-)
-- Gianluca Sartori
September 23, 2009 at 2:22 am
Gianluca Sartori (9/23/2009)
Jeff Moden always says it best!:-)
Well, I don't want to give him a big head.. But he has a good saying for this topic..
CEWII
September 23, 2009 at 3:47 am
After reading this thread, I suspect his head will be quite big enough!
Here is an example, in pseudo-code.
1) Traditional Loop/Cursor
Foreach Executive in Person
Set Salary = Salary * 1.1
Next Executive
2) Set-Based Code
Update Person
Set Salary = Salary * 1.1
Where Executive = 'Y'
In example 1, every executive in the Person table is given a 10% pay rise, by looping round the executives 1 record at a time, in a read/write/read/write ... fashion.
In example 2, we say to SQL Server: "Please increase the salaries of all Executives in the Person table by 10%." SQL Server than does this as a single operation, using its own optimised update methods. It is much faster to do it this way.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply