Declare Cursor performance

Question

Declare Cursor performance

Larry Stein

Default port

Points: 1447
More actions
March 30, 2004 at 3:48 pm

#107598

A developer's proc is bottlenecking in production. The statement that seems to hang for 30 seconds is a DECLARE CURSOR on a temp (#) table. The #table is 63 rows, and the query is SELECT DISTINCT (integer_col) FROM #table ORDER BY integer_col.
Are there any special gotchas about DECLARE CURSOR?
I intend to see if there is other contention going on, but it should not be locks as it is his own session #table. Perhaps a tempdb issue...
Thanks,
Larry
Larry

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

replace the "distinct" with a "group by" if you can, it might get index-only access, whilest distinct is an extra passthroug of the resultset .

- http://www.sql-server-performance.com/cursors.asp

- http://www.sql-server-performance.com/dp_no_cursors.asp

- http://www.sqlservercentral.com/articles/articlesexternal.asp?articleid=477

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 2

Even better....STAY AWAY from cursors....good performance and cursors rarely go in the same statement.

99.99% of database actions can be done without cursors.

Cursors really are only needed when a calculation on 1 row affects the calculation for the next in the same dataset.....like generating sequence numbers...(and even that can have a non-cursor solution)

An analogy i've seen before to describe cursors....is that they are like using tweezers to put grains of sugar into a cup of tea...instead of using a spoon. The task is achieveable, but horrible!

Post the code....sample table structures, sampel data and a description of what is being attempted to be achieved and 90%+ chance there is a better non-cursors solution available.