/***************************************************** Init tab_event_log with Flyweights ******************************************************/ Use CardioLog GO create index idx_tab_users_for_init on tab_users (userAccountName, isDeleted) include(flyweightId) set nocount on declare @bulksize int declare @delay char(8) declare @startTime datetime declare @rowsDone int declare @totalRows int declare @continueLoop int declare @startDate datetime declare @endDate datetime -- new variables declare @eventType smallint declare @timestamp datetime set @startTime = getdate() set @rowsDone = 0 set @bulksize = 5000 set @delay = '00:00:00' set @totalRows = 0 set @startDate = '2015-01-01' --change to the needed time range set @endDate = '2016-01-01' --change to the needed time range declare @rowsUpdated table ( EventLogId bigint, flyweightId int, EventType smallint, ETimestamp datetime ) -- init variables, for NULL, -1, 0, 1 values select top 1 @eventType = eventType, @timestamp = [timestamp], @continueLoop = 1 from tab_event_log where isnull(flyweightId,0) <= 1 and timestamp >= @startDate and timestamp < @endDate order by eventType, [timestamp] select @totalRows = count(*) from tab_event_log where isnull(flyweightId,0) <= 1 and timestamp >= @startDate and timestamp < @endDate print 'should update '+cast(@totalrows as varchar(max)) + ' rows' while @continueLoop > 0 begin ;with a as ( select top (@bulksize) * from tab_event_log where (eventType = @eventType and timestamp >= @timestamp) and timestamp >= @startDate and timestamp < @endDate order by eventType, timestamp ) update top (@bulksize) t1 set t1.flyweightId = isnull(t2.flyweightId,t1.flyweightId ) output INSERTED.EventLogId, INSERTED.flyweightId, INSERTED.EventType, INSERTED.timestamp into @rowsUpdated from a t1 left outer join tab_users t2 on t1.UserName = t2.userAccountName and t2.isDeleted = 0 select @totalRows = @totalRows - @@rowCOunt, @rowsDOne = @rowsDOne + @@rowCount, @continueLoop = @@rowCount -- update tab_event_log_users update t1 set t1.flyweightId = t2.flyweightId from tab_event_log_users t1 join @rowsUpdated t2 on t1.EventLogId = t2.EventLogId print convert(varchar(max), getdate(), 121)+ ' - ' + cast(@eventType as char(3)) +','+convert(varchar(max),@timestamp,121)+' , ' +cast(@totalRows as varchar(max)) + ' rows left, '+ 'estimated time left : ' + convert(varchar(10),dateadd(s, @totalRows/(@rowsDone / (datediff(s, @startTime, getdate())+1)),0),108)+ ' ,' + cast(@rowsDOne as varchar(max)) + ' rows done ' waitfor delay @delay -- check if no rows were found if (@continueLoop < @bulksize and @eventType < 100) begin print 'skipping eventType' select @eventType = @eventType + 1, @timestamp = '19000101', @continueLoop = 1 end else begin -- get last values for reposition ;with a as ( select eventType, etimestamp, row_number() over(order by eventType, etimestamp) rown from @rowsUpdated ) select @eventType = eventType, @timestamp = etimestamp from a where rown = (select count(*) from a) delete from @rowsUpdated end end drop index tab_users.idx_tab_users_for_init /* Delete reporting data cache */ delete from tab_controls_cache print 'Done'