c# - Performance with SQL Server cursor in following application scenario -
i've wondered how can modify application because have performance problem. have following application scenario. i've used c# console application sql server.
application scenario:
select 15 000 unique userid (int)
foreach list of users , list of documents (documentid) every userid. (perhaps 50 documents per 1 user.)
insert database table called permissions - userid, documentsid - perhaps - 15 000 * 50 = 750 000 rows
my scenario:
- i have create 2 stored procedures in sql server, first select userid's , second documents according userid , inserting permissions table.
stored procedure: dbo.createdocumentpermissions
declare @userid bigint begin try declare permissionscursor cursor select userid dbo.[user] open permissionscursor fetch next permissionscursor @userid while @@fetch_status = 0 begin begin try exec dbo.savedocumentspermissions @userid = @userid end try begin catch print error_message() end catch fetch next permissionscursor @userid end end try begin catch end catch begin try close permissionscursor deallocate permissionscursor end try begin catch end catch stored procedure: dbo.savedocumentspermissions @userid = @userid
declare @finaluserset table ( documentid int ) declare @userparentdepts table ( parentpath hierarchyid , path hierarchyid , organisationcode nvarchar(10) , entityid bigint ); parentotv_cte ( parentpath, path, name, itemid, organisationcode ) ( select otv.parentpath , otv.path , otv.text , otv.itemid , otv.organisationcode dbo.organisationtreeview otv otv.itemid = @userid union select otv.parentpath , otv.path , otv.text , otv.itemid , otv.organisationcode dbo.organisationtreeview otv inner join parentotv_cte potv on potv.parentpath = otv.path ) insert @userparentdepts ( parentpath , path , organisationcode , entityid ) select otv.parentpath , otv.path , otv.organisationcode , otv.itemid parentotv_cte otv otv.parentpath not null insert @finaluserset select d.documentid @userparentdepts dpts inner join dbo.documentaudience da on pa.organisationcode = dpts.organisationcode , da.orgentityid = dpts.entityid , da.active = 1 inner join [dbo].[documents] d on da.documentid = d.documentid delete dbo.documentpermissions userid = @userid insert dbo.documentpermissions ( userid, documentid ) select @userid, documentid @finaluserset table structure - documentpermissions
create table [dbo].[documentpermissions] ( [documentpermissionid] [bigint] identity(1,1) not null, [userid] [int] not null, [documentid] [int] not null, constraint [pk_documentpermissions] primary key clustered ([documentpermissionid] asc) (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary] i have nonclustered index userid, documentid.
how can improve application scenario better performance?
Comments
Post a Comment