dbo.QBM_PDBQueueCalculateDelta

1  create   procedure QBM_PDBQueueCalculateDelta ( @SourceDataOrig QBM_YDataForDelta readonly  , @SlotNumber int , @DeltaQuantity bit , @DeltaDelete
2 bit , @DeltaInsert bit , @DeltaOrigin bit , @CountDeltaQantity  int output , @CountDeltaOrigin  int output , @UseIsInEffect bit    , @DBQueueCurrentExtern
3 QBM_YDBQueueCurrent readonly       ) as begin declare @DBQueueCurrent QBM_YDBQueueCurrent declare @Overload_RowCountCurrent int declare @Overload_RowCountLimit
4 int = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QBM\DBQueue\OverloadLimit'), 200000) declare @Overload_RowCountLimitReset int = @Overload_RowCountLimit
5 * 6 declare @Overload_UID_TaskToReset varchar(38) declare @Overload_ElementsInSlot int declare @Overload_Maxbulk int declare @Overload_IsBulkEnabled bit
6  declare @ChangeLimitReached_String varchar(64) declare @Medium QBM_YDBQueueIntermediate declare @CountItems int  declare @DebugSwitch int = 0 declare
7 @DebugLevel char(1) = 'W' declare @DebugMessage nvarchar(1000) declare @SourceData QBM_YDataForDelta declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity
8 int declare @ErrorState int  declare @Switch int = 0 select @Switch += (@DeltaQuantity | @DeltaInsert | @DeltaDelete) select @Switch += @DeltaOrigin *
9 2   declare @DeleteMaskForXOrigin int = 0 if @DeltaOrigin = 1 and @DeltaDelete = 0  begin select @DeleteMaskForXOrigin = 0x02 end declare @ChangeLimit_internal
10 int declare @OnceMore bit = 0 declare @DBQueueElementsOnceMore QBM_YDBQueueRaw declare @UID_TaskOnceMore varchar(38) declare @SQLLockIgnore nvarchar(max
11)  declare @StartTime datetime = getutcdate() declare @ChangeLimitTimeForDelta_string varchar(64) declare @SlotNumberSource int declare @SlotNumberTarget
12 int declare @DBQueueToMove QBM_YDBQCSCurrentToMove declare @RowsMoved int SET XACT_ABORT OFF BEGIN TRY if exists (select top 1 1 from @DBQueueCurrentExtern
13 ) begin insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter
14, GenProcID from @DBQueueCurrentExtern end else begin insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select
15 UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber end select @ChangeLimit_internal
16 = dbo.QBM_FCVStringToInt( dbo.QBM_FGISessionContext('CHANGELIMIT'), -1111) if @ChangeLimit_internal = -1111 begin select @ChangeLimit_internal = dbo.QBM_FCVStringToint
17( dbo.QBM_FGIConfigparmValue('QBM\DBQueue\ChangeLimitDefault'), 3000)  end if isnull(@ChangeLimit_internal, 0) < 10 begin select @ChangeLimit_internal 
18= 10 end insert into @SourceData(Element, AssignedElement ,HashKey , IsUpcommingContent , XIsInEffectBefore, XIsInEffectAfter , XOriginBefore, XOriginAfter
19 )     select o.Element, o.AssignedElement , dbo.QBM_FCVAnyToHash(concat(o.Element, o.AssignedElement)) , isnull(o.IsUpcommingContent, 0) , isnull(o.XIsInEffectBefore
20, 0), isnull(o.XIsInEffectAfter, 0) , isnull(o.XOriginBefore, 0), isnull(o.XOriginAfter, 0) from @SourceDataOrig o select @Overload_RowCountCurrent = @@ROWCOUNT
21 if @Overload_RowCountCurrent = 0 or @Switch = 0 begin if @DebugSwitch > 0 begin print 'na gar nix' end select @CountDeltaQantity = 0 , @CountDeltaOrigin
22 = 0 goto endLabel end if @Overload_RowCountCurrent > @Overload_RowCountLimit  begin   select @ChangeLimitReached_String = dbo.QBM_FCVIntToString(@Overload_RowCountCurrent
23) exec QBM_PSessionContextSet 'OVERLOADREACHED', @ChangeLimitReached_String if @DebugSwitch > 0 begin select @DebugMessage = 'am Overload vorbei gekommen, füllstand: '
24 + str(@Overload_RowCountCurrent) exec QBM_PJournal @DebugMessage, @@procid, 'D', @DebugLevel end    if @Overload_RowCountCurrent > @Overload_RowCountLimitReset
25  begin select @DebugMessage = concat('#LDS#Rowcount Limit for reset {0} reached with {1}, Task {2} will be resetted.|' , str(@Overload_RowCountLimitReset
26) , '|', str(@Overload_RowCountCurrent), '|', @Overload_UID_TaskToReset , '|') exec QBM_PJournal @DebugMessage, @@procid, 'I', @DebugLevel delete @DBQueueToMove
27  insert into @DBQueueToMove(UID_DialogDBQueue) select cul.UID_DialogDBQueue from @DBQueueCurrent cul  if @@ROWCOUNT > 0 begin select @SlotNumberSource
28 = @SlotNumber  select @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('Error05') exec @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove, @SlotNumberSource
29, @SlotnumberTarget    end   goto endlabel end  end  if @DebugSwitch > 0 begin print '@Switch ' + str(@Switch) end if @Switch = 1 begin    insert into 
30@Medium(HashKey, IsToInsert, IsToDelete, IsIntersect, IsInheritChanged, XOriginNew, XIsInEffectNew) select HashKey ,case IsInsertDeleteIntersect when 2
31 then 1 else 0 end as IsToInsert , case IsInsertDeleteIntersect when 1 then 1 else 0 end as IsToDelete , case IsInsertDeleteIntersect when 3 then 1 else
32 0 end as IsIntersect , 0 as IsInheritChanged , 0 as XOriginNew , 0 as XIsInEffectNew from ( select HashKey , sum( distinct IsUpcommingContent +1) as IsInsertDeleteIntersect
33  from @SourceData s group by s.HashKey ) as x where IsInsertDeleteIntersect in (1, 2 )  end if @Switch = 2 begin    insert into @Medium(HashKey, IsToInsert
34, IsToDelete, IsIntersect, IsInheritChanged, XOriginNew, XIsInEffectNew) select HashKey , 0 as IsToInsert , 0 as IsToDelete , 1 as IsIntersect , 1 as IsInheritChanged
35 , XOriginNew , sign(XIsInEffectNew * XOriginNew )  from ( select HashKey , sum( distinct IsUpcommingContent +1) | @DeleteMaskForXOrigin as IsInsertDeleteIntersect
36 , max( s.XOriginBefore) as XOriginOld , MAX(s.XOriginAfter & 0x0001) + MAX(s.XOriginAfter & 0x0002) + MAX(s.XOriginAfter & 0x0004) + MAX(s.XOriginAfter
37 & 0x0008)   + MAX(s.XOriginAfter & 0xfff0) as XOriginNew  , case @UseIsInEffect when 1 then min(case when s.IsUpcommingContent = 1 then sign(s.XIsInEffectAfter
38) else 1 end ) else 1 end as XIsInEffectNew , case @UseIsInEffect when 1 then MAX(convert(int, s.XIsInEffectBefore)) else 1 end as XIsInEffectOld from 
39@SourceData s group by s.HashKey ) as x where IsInsertDeleteIntersect in (3 )  and ((x.XOriginOld ^ x.XOriginNew) + (x.XIsInEffectOld ^ x.XIsInEffectNew
40) > 0)  end if @Switch = 3 begin    insert into @Medium(HashKey, IsToInsert, IsToDelete, IsIntersect, IsInheritChanged, XOriginNew, XIsInEffectNew) select
41 HashKey ,case IsInsertDeleteIntersect when 2 then 1 else 0 end as IsToInsert , case IsInsertDeleteIntersect when 1 then 1 when 3 then @DeltaDelete * (x.XOriginNew
42 ^1)  else 0 end as IsToDelete , case IsInsertDeleteIntersect when 3 then 1 else 0 end as IsIntersect , case IsInsertDeleteIntersect when 3 then sign((x.XOriginOld
43 ^ x.XOriginNew) + (x.XIsInEffectOld ^ x.XIsInEffectNew) ) else 0 end as IsInheritChanged , XOriginNew , sign(XIsInEffectNew * XOriginNew )  from ( select
44 HashKey , sum( distinct s.IsUpcommingContent +1) | @DeleteMaskForXOrigin as IsInsertDeleteIntersect , max( s.XOriginBefore) as XOriginOld , MAX(s.XOriginAfter
45 & 0x0001) + MAX(s.XOriginAfter & 0x0002) + MAX(s.XOriginAfter & 0x0004) + MAX(s.XOriginAfter & 0x0008)   + MAX(s.XOriginAfter & 0xfff0) as XOriginNew 
46 , case @UseIsInEffect when 1 then min(case when s.IsUpcommingContent = 1 then sign(s.XIsInEffectAfter) else 1 end ) else 1 end as XIsInEffectNew , case
47 @UseIsInEffect when 1 then MAX(convert(int, s.XIsInEffectBefore)) else 1 end as XIsInEffectOld from @SourceData s group by s.HashKey ) as x where IsInsertDeleteIntersect
48 = 1  or (IsInsertDeleteIntersect = 2 and x.XOriginNew > 0)  or ((x.XOriginOld ^ x.XOriginNew) + (x.XIsInEffectOld ^ x.XIsInEffectNew) > 0)  if @DebugSwitch
49 > 0 begin print 'Zwischenstand von X' select HashKey , sum( distinct s.IsUpcommingContent +1) | @DeleteMaskForXOrigin as IsInsertDeleteIntersect , max
50( s.XOriginBefore) as XOriginOld , MAX(s.XOriginAfter & 0x01) + MAX(s.XOriginAfter & 0x02) + MAX(s.XOriginAfter & 0x04) + MAX(s.XOriginAfter & 0x08) as
51 XOriginNew  , case @UseIsInEffect when 1 then min(case when s.IsUpcommingContent = 1 then sign(s.XIsInEffectAfter) else 1 end ) else 1 end as XIsInEffectNew
52 , case @UseIsInEffect when 1 then MAX(convert(int, s.XIsInEffectBefore)) else 1 end as XIsInEffectOld from @SourceData s group by s.HashKey end end  update
53 @Medium set element = x.element , AssignedElement = x.AssignedElement  from @Medium y join @sourceData x on y.HashKey = x.HashKey  where y.IsToInsert 
54= 1 and (@DeltaInsert = 1 or @DeltaQuantity = 1) or y.IsToDelete = 1 and (@DeltaDelete = 1 or @DeltaQuantity = 1) or y.IsInheritChanged = 1 and @DeltaOrigin
55 > 0 if @DebugSwitch > 0 begin print 'content of @Medium' select * from @Medium end select @CountDeltaQantity = 0 select @CountDeltaOrigin = 0   if @DeltaDelete
56 =1 begin    truncate table #QBMDeltaDelete insert into #QBMDeltaDelete (Element, AssignedElement) select top (@ChangeLimit_internal)  m.Element, m.AssignedElement
57 from @Medium m  where m.IsToDelete = 1  or ( m.IsInheritChanged = 1 and m.XOriginNew = 0 ) select @CountItems = @@ROWCOUNT  if @CountItems = @ChangeLimit_internal
58 begin select @OnceMore = 1 end  select @CountDeltaQantity += @CountItems if @CountItems > 0 and @SlotNumber > 0 begin  update #QBMDeltaDelete set GenProcID
59 = c.GenProcID from #QBMDeltaDelete d join @DBQueueCurrent c on d.element = c.UID_Parameter end end   if @DeltaInsert =1 begin    truncate table #QBMDeltaInsert
60 insert into #QBMDeltaInsert (Element, AssignedElement, XOrigin, XIsInEffect) select top (@ChangeLimit_internal)  m.Element, m.AssignedElement, m.XOriginNew
61, m.XIsInEffectNew from @Medium m  where m.IsToInsert = 1 select @CountItems = @@ROWCOUNT  if @CountItems = @ChangeLimit_internal begin select @OnceMore
62 = 1 end  select @CountDeltaQantity += @CountItems if @CountItems > 0 and @SlotNumber > 0 begin  update #QBMDeltaInsert set GenProcID = c.GenProcID from
63 #QBMDeltaInsert d join @DBQueueCurrent c on d.element = c.UID_Parameter end end if @DeltaQuantity =1 begin    truncate table #QBMDeltaQuantity insert 
64into #QBMDeltaQuantity (Element) select distinct top (@ChangeLimit_internal) m.Element from @Medium m  where m.IsToDelete = 1 or m.IsToInsert = 1  or (
65 m.IsInheritChanged = 1 and m.XOriginNew = 0 ) select @CountItems = @@ROWCOUNT  if @CountItems = @ChangeLimit_internal begin select @OnceMore = 1 end  
66select @CountDeltaQantity += @CountItems  if @CountItems > 0 and @SlotNumber > 0 begin  update #QBMDeltaQuantity set GenProcID = c.GenProcID from #QBMDeltaQuantity
67 d join @DBQueueCurrent c on d.element = c.UID_Parameter end end   if @DeltaOrigin =1  begin    truncate table #QBMDeltaOrigin insert into #QBMDeltaOrigin
68 (Element, AssignedElement, XOrigin, XIsInEffect) select top (@ChangeLimit_internal)  m.Element, m.AssignedElement, m.XOriginNew, m.XIsInEffectNew from
69 @Medium m  where m.IsInheritChanged = 1 select @CountItems = @@ROWCOUNT  if @CountItems = @ChangeLimit_internal begin select @OnceMore = 1 end  select
70 @CountDeltaOrigin += @CountItems if @CountItems > 0 and @SlotNumber > 0 begin  update #QBMDeltaOrigin set GenProcID = c.GenProcID from #QBMDeltaOrigin
71 d join @DBQueueCurrent c on d.element = c.UID_Parameter end end    if @OnceMore = 1 begin delete  @DBQueueElementsOnceMore  insert into @DBQueueElementsOnceMore
72(Object, SubObject, GenProcID) select cu.UID_Parameter, cu.UID_SubParameter, cu.GenProcID from @DBQueueCurrent cu select top 1 @UID_TaskOnceMore = cu.UID_Task
73 from QBMDBQueueCurrent cu with (readpast)  where cu.SlotNumber = @SlotNumber and cu.UID_Task > ' '  if @UID_TaskOnceMore is null begin select top 1 @UID_TaskOnceMore
74 = f.UID_Task from QBMDBQueueCurrent  f with (readpast) where f.SlotNumber = @SlotNumber and f.UID_Task > ' '  if @UID_TaskOnceMore is null begin  select
75 top 1 @UID_TaskOnceMore = c.UID_Task from @DBQueueCurrent cu join QBMDBQueueCurrent c with (readpast) on cu.UID_DialogDBQueue = c.UID_DialogDBQueue where
76 c.UID_Task > ' ' if @UID_TaskOnceMore is null begin  select top 1 @UID_TaskOnceMore = c.UID_Task from @DBQueueCurrent cu join DialogDBQueue c with (readpast
77) on cu.UID_DialogDBQueue = c.UID_DialogDBQueue where c.UID_Task > ' ' end  end  end  exec QBM_PDBQueueInsert_Bulk @UID_TaskOnceMore, @DBQueueElementsOnceMore
78 if @DebugSwitch > 0 begin select @DebugMessage = 'restore once more ' + @UID_TaskOnceMore + '@CountDeltaOrigin ' + str(@CountDeltaOrigin) + ' @CountDeltaQantity '
79 + str(@CountDeltaQantity) exec QBM_PJournal @Debugmessage, @@procid, 'D', @DebugLevel end    end  select @ChangeLimitReached_String = dbo.QBM_FCVIntToString
80(@CountDeltaOrigin + @CountDeltaQantity) exec QBM_PSessionContextSet 'CHANGELIMITREACHED', @ChangeLimitReached_String select @ChangeLimitTimeForDelta_string