t-sql: difference in performance between cursor and cursor static

Question

I have two simple scripts One is:

declare @active_from date = '01.03.2014'
declare @active_to date = '01.04.2014'
declare @house_id integer = 11927
----
    declare @service_id integer
    declare @addendum_id integer
    declare @activity_id integer
    declare @session_id integer
    declare @cur_active_from datetime
    declare @cur_active_to datetime
    declare @session_license_fee_cur cursor
    -- prepare cursor
        set @session_license_fee_cur =  cursor static for 
        select activity_id
                , addendum_id
                , service_id
                , active_from
                , active_to
        from dbo.bills_supp_get_activate_license_fee_for_sessions_by_house(@active_from, @active_to, @house_id)
    -- open cursor
    open @session_license_fee_cur
    fetch next from @session_license_fee_cur into @activity_id, @addendum_id, @service_id, @cur_active_from, @cur_active_to
    while (@@FETCH_STATUS = 0)
    begin
        -- get next record
        fetch next from @session_license_fee_cur into @activity_id, @addendum_id, @service_id, @cur_active_from, @cur_active_to
    end
    -- 
    close @session_license_fee_cur
    deallocate @session_license_fee_cur

It works less then one second. The second one is the same, but instead

set @session_license_fee_cur =  cursor static for

I use

set @session_license_fee_cur =  cursor for

Without "static". It works more than 1 minute. Why such difference in performance? The count record in query is about 3000

Tony Hopkinson · Accepted Answer · 2014-03-30 18:43:50Z

4

Static cursor, query is run, result stored in tempdb and then you iterate through it.

So basically it's a readonly copy, no need to synchronise with the underlying data, so no need for locks and such.

Didn't realise it had that much of an overhead, there again I put a lot of effort into not using cursors at all for anything except one off admin tasks.

answered Mar 30, 2014 at 18:43

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

andreszs Over a year ago

In other words, there's no possible way to use the sqlsrv_num_rows method without creating a huge overhead with cursors.

Collectives™ on Stack Overflow

t-sql: difference in performance between cursor and cursor static

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related