dbo.QBM_PDBQueueCompress

1   create   procedure QBM_PDBQueueCompress as begin   declare @CurrentDeadlock_priority int declare @DebugSwitch int = 0 declare @DebugLevel varchar
2(1) = 'W' declare @StartDate datetime = getutcdate() declare @Message nvarchar(4000) declare @XUserRestore nvarchar(64) declare @anzahl int declare @IndexDef
3 nvarchar(max) = dbo.QBM_FSQIndexDef('DialogDBQueue', '%') declare @Before int = dbo.QBM_FGITableCountAll('DialogDBQueue') declare @After int declare @DifferentPathLengthExists
4 bit = 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') SET XACT_ABORT
5 OFF BEGIN TRY set nocount on Create Table #DialogDBQueue ( UID_DialogDBQueue varchar(38) collate database_default NOT NULL , UID_Task varchar(38) collate
6 database_default NULL, Object varchar(38) collate database_default NULL, SubObject varchar(38) collate database_default NULL, PathLength int default 0
7 NOT NULL, GenProcID varchar(38) collate database_default NOT NULL, Generation int default 0 NULL ) select top 1 @CurrentDeadlock_priority = s.deadlock_priority
8 FROM sys.dm_exec_sessions s with (readpast) where s.session_id = @@spid select @CurrentDeadlock_priority = isnull(@CurrentDeadlock_priority, 0) set deadlock_priority
9 8 set lock_timeout -1 if @DebugSwitch > 0 select @anzahl = dbo.QBM_FGITableCountAll('DialogDBQueue') if @DebugSwitch > 0 print 'Anzahl vorher ' + str(@anzahl
10) if exists (select top 1 1 from DialogDBQueue q join QBMDBQueueTaskMetric m on q.UID_Task = m.UID_Task where q.PathLength <> m.PathLength ) begin select
11 @DifferentPathLengthExists = 1 end begin transaction  insert into #DialogDBQueue (UID_DialogDBQueue, UID_Task, Object, SubObject, GenProcID, Generation
12, PathLength) select UID_DialogDBQueue, UID_Task, Object, SubObject, GenProcID, Generation, PathLength from DialogDBQueue q with (tablockx)  if @DebugSwitch
13 > 0 print 'in tempdb ' + str(DATEDIFF(ss, @StartDate, getutcdate())) truncate table DialogDBQueue create index #DialogDBQueue_i on #DialogDBQueue (UID_Task
14, Object, SubObject, GenProcID) include (UID_DialogDBQueue, Generation, PathLength) if @DebugSwitch > 0 print 'index created Tempdb' + str(DATEDIFF(ss,
15 @StartDate, getutcdate())) exec QBM_PIndexDrop 'DialogDBQueue', '%' if @DebugSwitch > 0 print 'index dropped dbqueue' + str(DATEDIFF(ss, @StartDate, getutcdate
16())) select @XUserRestore = dbo.QBM_FGISessionContext('XUser') exec QBM_PSessionContextSet 'XUser', 'QBM_PDBQueueInsert_Bulk' if @DifferentPathLengthExists
17 = 0 begin  insert into DialogDBQueue (UID_DialogDBQueue, UID_Task, Object, SubObject, GenProcID, Generation, PathLength) select max(q.UID_DialogDBQueue
18) as UID_DialogDBQueue , q.UID_Task , q.Object , q.SubObject , q.GenProcID , max(q.Generation) as Generation , max(PathLength) as PathLength from #DialogDBQueue
19 q group by q.UID_Task, q.Object, q.SubObject, q.GenProcID end else begin  insert into DialogDBQueue (UID_DialogDBQueue, UID_Task, Object, SubObject, GenProcID
20, Generation, PathLength) select max(q.UID_DialogDBQueue) as UID_DialogDBQueue , q.UID_Task , q.Object , q.SubObject , q.GenProcID , max(q.Generation) 
21as Generation , max(isnull(mo.PathLength, q.PathLength)) as PathLength from #DialogDBQueue q left outer join QBMDBQueueTaskMetric mo on q.UID_Task = mo.UID_Task
22  group by q.UID_Task, q.Object, q.SubObject, q.GenProcID end if @DebugSwitch > 0 print 'restored ' + str(DATEDIFF(ss, @StartDate, getutcdate())) exec 
23QBM_PSessionContextSet 'XUser', @XUserRestore exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @IndexDef , @LockTimeOut_ms = 4000 , @MaxWaitTimeForLock_s
24 = 20 , @DeadLockPriority = 5 , @ExecuteWithTransact = 0 if @DebugSwitch > 0 print 'indexe angelegt ' + str(DATEDIFF(ss, @StartDate, getutcdate())) commit
25 transaction if @DebugSwitch > 0 print 'transaktion fertig ' + str(DATEDIFF(ss, @StartDate, getutcdate())) truncate Table #DialogDBQueue select @After 
26= dbo.QBM_FGITableCountAll('DialogDBQueue') if @DebugSwitch > 0 print 'Anzahl Nachher ' + str(@After) select @Message = concat( '#LDS#DialogDBQueue compressed from {0} to {1} records.|'
27 , trim(str(@Before)), '|' , trim(str(@After)), '|' ) exec QBM_PJournal @Message, @@procid, 'I', @DebugLevel END TRY BEGIN CATCH exec QBM_PSessionErrorAdd
28 default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser'
29, @XUser_R truncate Table #DialogDBQueue  set deadlock_priority @CurrentDeadlock_priority  return end 
30