Back to OIM Explorer

dbo.QBM_PDBQueueCompress

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> at line 16; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 4.551 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> at line 16
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task at line 16 Bulk DBQueue insert -> at line 16
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGITableCountAll source text reference
  • references source dbo.QBM_FSQIndexDef source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PIndexDrop source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL198 lines
1CREATE PROCEDURE QBM_PDBQueueCompress2AS3BEGIN4  DECLARE @CurrentDeadlock_priority int5  DECLARE @DebugSwitch int = 06  DECLARE @DebugLevel varchar(1) = 'W'7  DECLARE @StartDate datetime = getutcdate()8  DECLARE @Message nvarchar(4000)9  DECLARE @XUserRestore nvarchar(64)10  DECLARE @anzahl int11  DECLARE @IndexDef nvarchar(max) = dbo.QBM_FSQIndexDef('DialogDBQueue',12  '%')13  DECLARE @Before int = dbo.QBM_FGITableCountAll('DialogDBQueue')14  DECLARE @After int15  DECLARE @DifferentPathLengthExists BIT = 016  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')17  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')18  SET XACT_ABORT OFF19  BEGIN TRY20    SET nocount21      ON22    CREATE TABLE #DialogDBQueue(23      UID_DialogDBQueue varchar(38) collate database_default NOT NULL,24      UID_Task varchar(38) collate database_default NULL,25      Object varchar(38) collate database_default NULL,26      SubObject varchar(38) collate database_default NULL,27      PathLength int DEFAULT 0 NOT NULL,28      GenProcID varchar(38) collate database_default NOT NULL,29      Generation int DEFAULT 0 NULL30    )31    SELECT TOP 1 @CurrentDeadlock_priority = s.deadlock_priority32    FROM sys.dm_exec_sessions s33      WITH(readpast34  )35  WHERE36    s.session_id = @@spid37  SELECT38    @CurrentDeadlock_priority = isnull(@CurrentDeadlock_priority,39    040    )41SET deadlock_priority 842SET lock_timeout -143IF @DebugSwitch > 044SELECT @anzahl = dbo.QBM_FGITableCountAll('DialogDBQueue'45)46IF @DebugSwitch > 0 print 'Anzahl vorher ' + str(@anzahl47)48IF EXISTS(49  SELECT TOP 1 150  FROM DialogDBQueue q51  JOIN QBMDBQueueTaskMetric m52    ON q.UID_Task = m.UID_Task53  WHERE54    q.PathLength <> m.PathLength55)56BEGIN57  SELECT @DifferentPathLengthExists = 158END59BEGIN60  TRANSACTION61  INSERT INTO #DialogDBQueue(UID_DialogDBQueue,62  UID_Task,63  Object,64  SubObject,65  GenProcID,66  Generation,67  PathLength68)69SELECT70  UID_DialogDBQueue,71  UID_Task,72  Object,73  SubObject,74  GenProcID,75  Generation,76  PathLength77FROM DialogDBQueue q78  WITH(tablockx79)80IF @DebugSwitch > 0 print 'in tempdb ' + str(DATEDIFF(ss,81@StartDate,82getutcdate())83) truncate TABLE DialogDBQueue84CREATE index #DialogDBQueue_i85  ON #DialogDBQueue(86  UID_Task,87  Object,88  SubObject,89  GenProcID90) include(UID_DialogDBQueue,91Generation,92PathLength93)94IF @DebugSwitch > 0 print 'index created Tempdb' + str(DATEDIFF(ss,95@StartDate,96getutcdate())97)98EXEC QBM_PIndexDrop 'DialogDBQueue',99'%'100IF @DebugSwitch > 0 print 'index dropped dbqueue' + str(DATEDIFF(ss,101@StartDate,102getutcdate())103)104SELECT @XUserRestore = dbo.QBM_FGISessionContext('XUser'105)106EXEC QBM_PSessionContextSet 'XUser',107'QBM_PDBQueueInsert_Bulk'108IF @DifferentPathLengthExists = 0109BEGIN110  INSERT INTO DialogDBQueue(UID_DialogDBQueue,111  UID_Task,112  Object,113  SubObject,114  GenProcID,115  Generation,116  PathLength117)118SELECT max(q.UID_DialogDBQueue119)120AS121UID_DialogDBQueue,122q.UID_Task,123q.Object,124q.SubObject,125q.GenProcID,126max(q.Generation) AS Generation,127max(PathLength) AS PathLength128FROM #DialogDBQueue q129GROUP BY q.UID_Task,130q.Object,131q.SubObject,132q.GenProcID133END134ELSE135BEGIN136  INSERT INTO DialogDBQueue(UID_DialogDBQueue,137  UID_Task,138  Object,139  SubObject,140  GenProcID,141  Generation,142  PathLength)143  SELECT144    max(q.UID_DialogDBQueue) AS UID_DialogDBQueue,145    q.UID_Task,146    q.Object,147    q.SubObject,148    q.GenProcID,149    max(q.Generation) AS Generation,150    max(isnull(mo.PathLength, q.PathLength)) AS PathLength151  FROM #DialogDBQueue q152  LEFT153  OUTER154  JOIN QBMDBQueueTaskMetric mo155    ON q.UID_Task = mo.UID_Task156  GROUP BY q.UID_Task,157  q.Object,158  q.SubObject,159  q.GenProcID160END161IF @DebugSwitch > 0 print 'restored ' + str(DATEDIFF(ss, @StartDate, getutcdate()))162EXEC QBM_PSessionContextSet 'XUser',163  @XUserRestore164EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @IndexDef,165  @LockTimeOut_ms = 4000,166  @MaxWaitTimeForLock_s = 20,167  @DeadLockPriority = 5,168  @ExecuteWithTransact = 0169IF @DebugSwitch > 0 print 'indexe angelegt ' + str(DATEDIFF(ss, @StartDate, getutcdate())) COMMIT TRANSACTION170IF @DebugSwitch > 0 print 'transaktion fertig ' + str(DATEDIFF(ss, @StartDate, getutcdate())) truncate TABLE #DialogDBQueue171SELECT @After = dbo.QBM_FGITableCountAll('DialogDBQueue')172IF @DebugSwitch > 0 print 'Anzahl Nachher ' + str(@After)173SELECT174  @Message = CONCAT('#LDS#DialogDBQueue compressed from {0} to {1} records.|',175  trim(str(@Before)),176  '|',177  trim(str(@After)),178  '|')179EXEC QBM_PJournal @Message,180  @@procid,181'I',182  @DebugLevel183END TRY184BEGIN CATCH185  EXEC QBM_PSessionErrorAdd DEFAULT186  RAISERROR('',187  18,188  1)189    WITH NOWAIT190END CATCH191endLabel:192EXEC QBM_PSessionContextSet 'GenProcID',193  @GenProcID_R194EXEC QBM_PSessionContextSet 'XUser',195  @XUser_R truncate TABLE #DialogDBQueue196SET deadlock_priority @CurrentDeadlock_priority197RETURN198END
Open raw exported source
SQL ยท Raw30 lines
1   create   procedure QBM_PDBQueueCompress as begin   declare @CurrentDeadlock_priority int declare @DebugSwitch int = 0 declare @DebugLevel varchar2(1) = 'W' declare @StartDate datetime = getutcdate() declare @Message nvarchar(4000) declare @XUserRestore nvarchar(64) declare @anzahl int declare @IndexDef3 nvarchar(max) = dbo.QBM_FSQIndexDef('DialogDBQueue', '%') declare @Before int = dbo.QBM_FGITableCountAll('DialogDBQueue') declare @After int declare @DifferentPathLengthExists4 bit = 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') SET XACT_ABORT5 OFF BEGIN TRY set nocount on Create Table #DialogDBQueue ( UID_DialogDBQueue varchar(38) collate database_default NOT NULL , UID_Task varchar(38) collate6 database_default NULL, Object varchar(38) collate database_default NULL, SubObject varchar(38) collate database_default NULL, PathLength int default 07 NOT NULL, GenProcID varchar(38) collate database_default NOT NULL, Generation int default 0 NULL ) select top 1 @CurrentDeadlock_priority = s.deadlock_priority8 FROM sys.dm_exec_sessions s with (readpast) where s.session_id = @@spid select @CurrentDeadlock_priority = isnull(@CurrentDeadlock_priority, 0) set deadlock_priority9 8 set lock_timeout -1 if @DebugSwitch > 0 select @anzahl = dbo.QBM_FGITableCountAll('DialogDBQueue') if @DebugSwitch > 0 print 'Anzahl vorher ' + str(@anzahl10) 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 select11 @DifferentPathLengthExists = 1 end begin transaction  insert into #DialogDBQueue (UID_DialogDBQueue, UID_Task, Object, SubObject, GenProcID, Generation12, PathLength) select UID_DialogDBQueue, UID_Task, Object, SubObject, GenProcID, Generation, PathLength from DialogDBQueue q with (tablockx)  if @DebugSwitch13 > 0 print 'in tempdb ' + str(DATEDIFF(ss, @StartDate, getutcdate())) truncate table DialogDBQueue create index #DialogDBQueue_i on #DialogDBQueue (UID_Task14, 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, getutcdate16())) select @XUserRestore = dbo.QBM_FGISessionContext('XUser') exec QBM_PSessionContextSet 'XUser', 'QBM_PDBQueueInsert_Bulk' if @DifferentPathLengthExists17 = 0 begin  insert into DialogDBQueue (UID_DialogDBQueue, UID_Task, Object, SubObject, GenProcID, Generation, PathLength) select max(q.UID_DialogDBQueue18) as UID_DialogDBQueue , q.UID_Task , q.Object , q.SubObject , q.GenProcID , max(q.Generation) as Generation , max(PathLength) as PathLength from #DialogDBQueue19 q group by q.UID_Task, q.Object, q.SubObject, q.GenProcID end else begin  insert into DialogDBQueue (UID_DialogDBQueue, UID_Task, Object, SubObject, GenProcID20, 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_Task22  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_s24 = 20 , @DeadLockPriority = 5 , @ExecuteWithTransact = 0 if @DebugSwitch > 0 print 'indexe angelegt ' + str(DATEDIFF(ss, @StartDate, getutcdate())) commit25 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_PSessionErrorAdd28 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