I am a DBA i knew bit of developmet, can i know the difference betwee set based and cursor basedapproch, i heared in this forum that, fallowing set based is better than cursor based for good performace..? What is the internal mechanisam for both processes...
Thanks In advance...
asked Feb 27, 2011 at 10:06 PM in Default
Think of it this way. If your wife wants you to fold the washing that's come out of the tumble dryer - she might well say 'can you please fold the washing?'. That's a set based approach - it's an operation over a collection of items.
The cursor based approach would be the equivalent of your wife asking you to fold a t-shirt, then when you had come back with that asking you to fold a pair of trousers, then when you had come back with that, asking if you could fold a jumper. No doubt, that would really hack you off. SQL Server doesn't fare much better, and really prefers to be asked to do things the set based way.
answered Feb 28, 2011 at 01:53 AM
Matt Whitfield ♦♦
Think of it this way. If you are cutting wood so you have logs for your fire over for winter - You might well say I'm going to cut some wood. You would get in your pick-up with your saw and other tools. Cut down a tree, cut it into logs, load the logs onto the truck and bring it all back top stack on your log pile. That's a set based approach - it's an operation over a collection of items.
The cursor based approach would be the equivalent of going to the forest, cutting down a tree, cutting one log off it and bringing it home to stack on the wood pile, then returning to the forest to get another log, bringing it back to the log pile and so on.
No doubt, that would really hack you off, it would cost a lot of resources - fuel, time, traffic on the road etc. SQL Server doesn't fare much better, and really prefers to be asked to do things the set based way.
[absolute plagiarism of Matt's answer - just wanted a LumberJack version rather than doing the washing!!]
Jeff Moden (SSC contributor and all round SQL genius) has the following text in his signature over on the SSC site
I've always liked that explanation of set-based vs looping
answered Feb 28, 2011 at 02:31 AM
Kev Riley ♦♦
A great way to better try and understand the difference is through one explanation I heard a while back (can't remember who said/wrote it).
When working in T-SQL, try to tell the system what you want to do with the data, not how you want it done.
Most programming is the other way around, you tell the computer what it should do with an eye on the outcome almost being a secondary thought. With T-SQL / Set Based thinking, you will often win by constructing commands that explain what result you want - the query optimiser will then find a good way of arriving at that result.
A cursor is a programming construct that prevents the query optimiser from working with the entire data-set you are wanting to process. You queue up the data and manipulate each row, one at a time. The query optimiser then "thinks" that you are working with one single data row and will decide how to best satisfy your single row request.
An easy way to demonstrate the difference would be to imagine a table with 100 rows and wanting to return the rows to the client:
Look at a cursor solution that gets the rows and returns them to the client:
Now look at the set based approach:
This is an extremely simplified example, but shows that both will achieve the answer, but the cursor spends most of the time telling the system what to do instead of what results we want.
There are really very few reasons for using cursors nowadays, the programming constructs that are offered from SQL 2005 onwards reduced the need even further.
If you have a cursor based solution and want help removing it, let us know on here and we will gladly help.
answered Feb 28, 2011 at 01:30 AM
On Cursor based approach otherwise procedure approach you are manipulating data set on a row-by-row basis otherwise called looping instead of Set.This will hurts the performance if you are dealing with large number of rows.
On set-based approach you are dealing with one or more set of records and the data manipulation is done using SQL Join Operators like OUTER, INNER JOINS, sub-queries etc. Most of the time Set-based approach outperforms Cursor based or procedural ways because, SQL Server engine can make use of indexes and statistics defined on underlying tables of the sets. Refer this link for more details
answered Feb 27, 2011 at 10:43 PM