dbo.QBM_PDialogDBQueueDelete

1  create   procedure QBM_PDialogDBQueueDelete ( @RowLimit int = 1000  , @MaxLimit int = 50000  ) as begin declare @Items QBM_YSingleGuid  declare
2 @ItemPortion QBM_YSingleGuid declare @einRuck int  declare @LaufDelete int = 0 declare @AnzahlLoesch int declare @AnzahlLoeschGesamt int = 0 declare @Start
3 datetime declare @ende datetime declare @diff int declare @ExpectedRuntime float = 1000.0 declare @anzahl int SET XACT_ABORT OFF BEGIN TRY set lock_timeout
4 50 select @anzahl = dbo.QBM_FGITableCountAll('DialogDBQueue') if @anzahl > 0 begin goto normal end if @anzahl = 0 and @@TRANCOUNT = 0  begin begin transaction
5 BEGIN TRY  update DialogDBQueue set Generation = q.Generation from DialogDBQueue q with (tablockx) END TRY BEGIN CATCH select @anzahl = 1  END CATCH  
6if dbo.QBM_FGITableCountAll('DialogDBQueue') = 0 and @anzahl = 0 begin BEGIN TRY  truncate table DialogDBQueue  commit select @LaufDelete = 1 END TRY BEGIN
7 CATCH   rollback END CATCH end else begin  rollback end end  if @LaufDelete = 1 begin goto endLabel end normal: insert into @Items (UID_SingleGuid ) select
8 top (@MaxLimit) qt.UID_DialogDBQueue from DialogDBQueue qt with (readpast) where Generation = -1 select @LaufDelete = 1 select @einRuck = @RowLimit while
9 @LaufDelete > 0 begin select @Start = getutcdate() delete  @ItemPortion  insert into @ItemPortion (UID_SingleGuid ) select top (@einRuck) t.UID_SingleGuid
10 from @Items t where t.BitProperty = 0  select @LaufDelete = @@ROWCOUNT if @LaufDelete = 0 begin continue end update @Items set BitProperty = 1  from @Items
11 t join @ItemPortion p on t.UID_SingleGuid = p.UID_SingleGuid  BEGIN TRY delete DialogDBQueue from DialogDBQueue q with (forceSeek) where q.UID_DialogDBQueue
12 in (select t.UID_SingleGuid  from @ItemPortion t ) select @AnzahlLoesch = @@rowcount END TRY BEGIN CATCH  select @AnzahlLoesch = 0 exec QBM_PWaitForSeconds
13 0.05 end CATCH select @AnzahlLoeschGesamt += @AnzahlLoesch select @ende = getutcdate() select @diff = DATEDIFF(ms, @start, @ende) select @einRuck = case
14 when @diff = 0 then @RowLimit when @AnzahlLoesch = 0 then @RowLimit when @diff > @ExpectedRuntime then @RowLimit else convert(int, @ExpectedRuntime * 
15convert(float, @einRuck) / convert(float, @diff)) end  end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow
16() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return (@AnzahlLoeschGesamt) end 
17