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
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.