puzzeled with "exec sp_cursor 180150007,40,1"

  • I've noticed in one of our applications a cursor uses operation 40 and that operation is very slow.

    This handles a table with 62 rows with images ( most < 500KB , but some (5) > 2MB )

    MS Docs sp_cursor describe optype, but I cannot actually get my interpretation around as to why this takes so long.

    /* Trace records captured */
    declare @p1 int set @p1=180150007 declare @p3 int set @p3=4 declare @p4 int set @p4=1 declare @p5 int set @p5=-1 exec sp_cursoropen @p1 output,N'select * from myschema.myImages',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 ; /* duration 277 */

    exec sp_cursor 180150007,40,1 /* duration 252824 up to 4000000 */
    -- (33 times )

    exec sp_cursorfetch 180150007,2,1,1 /* duration 150 up to 183732 */
    -- (28 times )

    Can it realy be that slow to fetch this cursor in memory/tempdb ?

    What am I missing?

    We have no way of correcting this application to avoid cursors. ( unless we €€ the vendor for a full revamp )


    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

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

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