February 17, 2011 at 8:03 am
I learned one best practice is to avoid using sql server cursors. Because they generally use a lot of sql server resources and reduce the performacne of applicaitons. If need to perform row-by-row operations, try to find another method to perform the task.
So I would like to know what other method is available for row-by-row operations?
Thanks
February 17, 2011 at 8:21 am
I try always and use sets, I cant only think of half a dozen occasions that I've resorted to a cursor, and most of those were due to time constraints where I didnt have time to think out the set logic.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 17, 2011 at 9:20 am
Anna_SQL (2/17/2011)
I learned one best practice is to avoid using sql server cursors. Because they generally use a lot of sql server resources and reduce the performacne of applicaitons. If need to perform row-by-row operations, try to find another method to perform the task.So I would like to know what other method is available for row-by-row operations?
Thanks
Any form of Row-By-Agonizing-Row (RBAR) operations will face the same issues - and in fact, some can be worse than utilizing a cursor.
I'd suggest reading the RBAR and tally table articles by Jeff Moden - they can be found here[/url].
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 17, 2011 at 10:37 am
Anna_SQL (2/17/2011)
I learned one best practice is to avoid using sql server cursors. Because they generally use a lot of sql server resources and reduce the performacne of applicaitons. If need to perform row-by-row operations, try to find another method to perform the task.So I would like to know what other method is available for row-by-row operations?
Thanks
Cursor isn't the enemy, but row by row logic without express need for it is. WHILE loops, Cursors, correllated subqueries, scalar functions... all of these are row by row logic, implicit or explicit. Occassionally it's necessary, and you find the most optimized version you can (which is occassionally Cursors).
What you have to realize is that you need to change the way you think when you work in SQL Server. The engine is optimized to work with rows in bulk. Any time you start doing things at a row by row basis, you want to determine if there's a better way to avoid it completely. If you can't, then you start looking into your row by row options.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 11:57 am
One of the best ways I know of to get used to optimizing SQL and stepping away from row-by-row is to stop looking at rows, and starting thinking about what you want to do to a column. That may be another Jeff line, I don't remember. Could even be from Celko, I'm really not sure. But it's a great way to start getting into the correct frame of reference on SQL.
- 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply