XML Feeds
Looping in SQL code almost always causes performance problems. You can use a cursor or a while loop in SQL code. Both offer similarly bad performance. Not all code can be re-written in a set based manner, but it does make sense to revisit your looping code from time to time to see if it can be re-written.
There are rare times when a cursor will outperform set based code. Not all procedures returned by the query need (or should) be re-written. But it is a worth while endeavor to re-examine these procedures from time to time.
How to detect this problem:
- SELECT OBJECT_NAME(OBJECT_ID) AS ProcedureName
- FROM sys.sql_modules S
- WHERE (Definition Like '%cursor%' or Definition Like '%while%')
- And OBJECTPROPERTY(OBJECT_ID, N'IsMSShipped') = 0
- ORDER BY OBJECT_NAME(OBJECT_ID)
How to correct it: Examine each block of code that has a loop, and design an alternative method for accomplishing the same results. This is not always possible.
Level of severity: moderate
Level of difficulty: High
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.