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 )