Back to OIM Explorer

dbo.QBM_PQBMDBQueuePondDelete

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.073 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVDatetimeToString source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FGITableCountAll source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PWaitForSeconds source text reference

References

Referenced By

Complete Source

SQL101 lines
1CREATE PROCEDURE QBM_PQBMDBQueuePondDelete(2  @RowLimit int = 2000,3  @MaxLimit int = 800004)5AS6BEGIN7  DECLARE @einRuck int8  DECLARE @LaufDelete int9  DECLARE @AnzahlLoesch int10  DECLARE @AnzahlLoeschGesamt int = 011  DECLARE @Start datetime12  DECLARE @ende datetime13  DECLARE @diff int14  DECLARE @ExpectedRuntime float = 1000.015  DECLARE @PondGroup varchar(38) = NULL16  DECLARE @DebugSwitch int = 017  SET XACT_ABORT OFF18  BEGIN TRY19    SET lock_timeout 5020    IF dbo.QBM_FGITableCountAll('QBMDBQueuePond') > 021    BEGIN22      GOTO normal23    END24    BEGIN TRY25      truncate TABLE QBMDBQueuePond26      SELECT @LaufDelete = 127    END TRY28    BEGIN CATCH29      SELECT @LaufDelete = 030    END CATCH31    IF @LaufDelete = 132    BEGIN33      GOTO endLabel34    END35    normal:36    IF @DebugSwitch > 037    BEGIN38      print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Pondgroup suchen'39    END40    SELECT TOP 1 @PondGroup = p.PondGroup41    FROM QBMDBQueuePond p42    WHERE43      GenProcID = 'DELETE'44    IF @PondGroup IS NULL45    BEGIN46      GOTO endlabel47    END48    IF @DebugSwitch > 049    BEGIN50      print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Pondgroup gefunden ' + @PondGroup51    END52    SELECT @LaufDelete = 153    SELECT @einRuck = @RowLimit54    WHILE @LaufDelete > 0 AND @AnzahlLoeschGesamt < @MaxLimit55    BEGIN56      SELECT @Start = getutcdate()57      BEGIN TRY58        DELETE TOP(@einRuck)59        FROM QBMDBQueuePond60        WHERE61          PondGroup = @PondGroup62        SELECT @AnzahlLoesch = @@rowcount63        SELECT @LaufDelete = @AnzahlLoesch64      END TRY65      BEGIN CATCH66        SELECT @AnzahlLoesch = 067        EXEC QBM_PWaitForSeconds 0.0568      END CATCH69      SELECT @AnzahlLoeschGesamt += @AnzahlLoesch70      SELECT @ende = getutcdate()71      SELECT72        @diff = DATEDIFF(ms,73        @start,74        @ende)75      IF @DebugSwitch > 076      BEGIN77        print 'Laufzeit 1 Portion ms ' + str(@diff)78      END79      SELECT @einRuck = CASE80      WHEN @diff = 0 THEN81      @RowLimit82      WHEN @AnzahlLoesch = 0 THEN83      @RowLimit84      WHEN @diff > @ExpectedRuntime THEN85      @RowLimit86      ELSE convert(int,87      @ExpectedRuntime * convert(float, @einRuck) / convert(float, @diff))88      END89    END90  END TRY91  BEGIN CATCH92    EXEC QBM_PSessionErrorAdd DEFAULT93    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()94    RAISERROR(@Rethrow,95    18,96    1)97      WITH NOWAIT98  END CATCH99  endLabel:100  RETURN(@AnzahlLoeschGesamt)101END
Open raw exported source
SQL · Raw15 lines
1create   procedure QBM_PQBMDBQueuePondDelete ( @RowLimit int = 2000  , @MaxLimit int = 80000  ) as begin declare @einRuck int  declare @LaufDelete2 int declare @AnzahlLoesch int declare @AnzahlLoeschGesamt int = 0 declare @Start datetime declare @ende datetime declare @diff int declare @ExpectedRuntime3 float = 1000.0  declare @PondGroup varchar(38) = null declare @DebugSwitch int = 0 SET XACT_ABORT OFF BEGIN TRY set lock_timeout 50 if dbo.QBM_FGITableCountAll4('QBMDBQueuePond') > 0 begin goto normal end BEGIN TRY truncate table QBMDBQueuePond select @LaufDelete = 1 END TRY BEGIN CATCH  select @LaufDelete = 05 END CATCH if @LaufDelete = 1 begin goto endLabel end normal: if @DebugSwitch > 0 begin print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Pondgroup suchen'6 end select top 1 @PondGroup = p.PondGroup from QBMDBQueuePond p where  GenProcID = 'DELETE' if @PondGroup is null  begin goto endlabel end if @DebugSwitch7 > 0 begin print dbo.QBM_FCVDatetimeToString(GETUTCDATE()) + ' Pondgroup gefunden ' + @PondGroup end select @LaufDelete = 1 select @einRuck = @RowLimit8 while @LaufDelete > 0 and @AnzahlLoeschGesamt < @MaxLimit begin select @Start = getutcdate()  BEGIN TRY delete top (@einRuck) from QBMDBQueuePond where9 PondGroup = @PondGroup   select @AnzahlLoesch = @@rowcount select @LaufDelete = @AnzahlLoesch  END TRY BEGIN CATCH  select @AnzahlLoesch = 0 exec QBM_PWaitForSeconds10 0.05 end CATCH select @AnzahlLoeschGesamt += @AnzahlLoesch select @ende = getutcdate() select @diff = DATEDIFF(ms, @start, @ende) if @DebugSwitch > 0 11begin print 'Laufzeit 1 Portion ms ' + str(@diff) end select @einRuck = case when @diff = 0 then @RowLimit when @AnzahlLoesch = 0 then @RowLimit when @diff12 > @ExpectedRuntime then @RowLimit else convert(int, @ExpectedRuntime * convert(float, @einRuck) / convert(float, @diff)) end  end  END TRY BEGIN CATCH13 exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH 14endLabel: return (@AnzahlLoeschGesamt) end 15