--************************* -- Fix User Categories --************************* /*** Initialize all usage data with user categories information (CategoryFlyweightId) ***/ Use CardioLog GO /*** Initialize all usage data ***/ declare @top int declare @startTime datetime declare @endTime datetime set @top = 10000 set @startTime = '2015-01-01' --change to needed time range set @endTime = '2016-06-24' --change to needed time range select top 1 '1' from tab_event_log while @@rowCOunt > 0 begin print cast(@top as varchar(50)) + ' rows initialized' ;with a as (select top (@top) CategoryFlyweightId from tab_event_log LG where isnull(CategoryFlyweightId,0) != 0 and timestamp >= @starttime and timestamp < @endtime ) update a set CategoryFlyweightId = 0 end GO /*** Set flyweights ***/ create index idx_tab_users_for_init on tab_users (userAccountName, isDeleted) include(CategoryFlyweightId) set nocount on declare @bulksize int declare @delay char(8) declare @startTime datetime declare @endTime datetime declare @rowsDone int declare @totalRows int declare @continueLoop int declare @eventType smallint declare @timestamp datetime set @rowsDone = 0 set @bulksize = 5000 set @delay = '00:00:00' set @totalRows = 0 set @startTime = '2015-01-01' --change to need time range set @endTime = '2016-06-24' --change to need time range declare @rowsUpdated table ( EventLogId bigint, CategoryFlyweightId 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(CategoryFlyweightId,0) <= 1 and timestamp >= @starttime and timestamp < @endtime order by eventType, [timestamp] select @totalRows = count(*) from tab_event_log where isnull(CategoryFlyweightId,0) <= 1 and timestamp >= @starttime and timestamp < @endtime 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 >= @starttime and timestamp < @endtime order by eventType, timestamp ) update top (@bulksize) t1 set t1.CategoryFlyweightId = isnull(t2.CategoryFlyweightId,t1.CategoryFlyweightId ) output INSERTED.EventLogId, INSERTED.CategoryFlyweightId, 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.CategoryFlyweightId = t2.CategoryFlyweightId 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, '+ ' ,' + cast(@rowsDOne as varchar(max)) + ' rows done ' waitfor delay @delay -- check if no rows were found if (@continueLoop < @bulksize and @eventType < 8) 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'