Fastest way to 1,000,000 individual DML operations for Change Tracking

• Jim Foster 80123

  SSChasing Mays

  Points: 655

  More actions

  June 27, 2011 at 3:40 pm

  #238502

  Long story short, I need to find the fastest way to force 1,000,000 individual DML operations (of any kind). I'm not a fool (much) - this is actually needed to cover for a known MS bug in change tracking related to mirroring where it "loses" it's identity in certain situations and has to be brought back in range manually. In 2008 R2 this is fixed, but I've got one machine "stuck" on 2008 (non-R2) that needs this workaround.

  So for example...

  I need to move the CHANGE_TRACKING_CURRENT_VERSION() to CHANGE_TRACKING_CURRENT_VERSION() + 1,000,000.

  Things that don't work: update 1,000 rows 1,000 times. In change tracking this would only create 1,000 versions (the number of versioned executions).

  My current solution:

  Update a single bit field 1,000,000 times. It takes almost 2.5hrs to run. I've even written it to be "parallel" (note the "_A"). Running it parallel with 4 tables being updated in different query windows actually slowed it down some).

  Any clever ideas out there? Or am I missing something obvious?

  SET NOCOUNT ON

  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChangeTrack_Fix_A]') AND type in (N'U'))

  BEGIN

  CREATE TABLE [dbo].[ChangeTrack_Fix_A](

  [ID] [bit] UNIQUE NOT NULL,

  [BitField] [bit] NOT NULL,

  CONSTRAINT [PK_ChangeTrack_Fix_A] PRIMARY KEY CLUSTERED

  (

  [ID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  ALTER TABLE [dbo].[ChangeTrack_Fix_A] ENABLE CHANGE_TRACKING

  INSERT INTO [UnifiedCad].[dbo].[ChangeTrack_Fix_A]

  ([ID],[BitField])

  VALUES (1,0);

  END

  IF EXISTS (SELECT COUNT(*) FROM sys.change_tracking_tables)

  BEGIN

  DECLARE @i BIGINT

  , @NumberToAdd BIGINT = 100000

  , @MinVersion BIGINT = 0

  , @CurrentVersion BIGINT = 0

  , @DateStart DATETIME = GETDATE()

  SELECT @i = 0

  , @MinVersion = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.[ChangeTrack_Fix_A]'))

  , @CurrentVersion = CHANGE_TRACKING_CURRENT_VERSION();

  SELECT @MinVersion

  , @CurrentVersion;

  DECLARE @TargetHolder BIGINT = @CurrentVersion + @NumberToAdd

  WHILE @CurrentVersion < @TargetHolder

  BEGIN

  UPDATE dbo.[ChangeTrack_Fix_A] SET BitField = 0;

  SET @i=@i+1;

  --This loop needs to regather the current version because other threads may have manipulated it. But only check every 1000th iteration.

  IF @i % 1000 = 0

  SET @CurrentVersion = CHANGE_TRACKING_CURRENT_VERSION();

  END

  SELECT @MinVersion = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.[ChangeTrack_Fix_A]'))

  , @CurrentVersion = CHANGE_TRACKING_CURRENT_VERSION();

  SELECT @MinVersion

  , @CurrentVersion

  , @NumberToAdd/(DATEDIFF(second,@DateStart,GETDATE())*1.0) AS CTsPerSecond

  , @NumberToAdd/(DATEDIFF(minute,@DateStart,GETDATE())*1.0) AS CTsPerMinute;

  END

  It is interesting to note: the execution plan shows a CLUSTERED INDEX UPDATE to the base table - even though I'm not updating the clustered index. Any idea why from anyone?

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  June 27, 2011 at 6:15 pm

  #1344915

  I tried it on my desktop and got it to run in about 5 minutes by a small change...

  SET NOCOUNT ON

  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChangeTrack_Fix_A]') AND type in (N'U'))

  BEGIN

  CREATE TABLE [dbo].[ChangeTrack_Fix_A](

  [ID] [bit] UNIQUE NOT NULL,

  [BitField] [bit] NOT NULL,

  CONSTRAINT [PK_ChangeTrack_Fix_A] PRIMARY KEY CLUSTERED

  (

  [ID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  ALTER TABLE [dbo].[ChangeTrack_Fix_A] ENABLE CHANGE_TRACKING

  INSERT INTO [dbo].[ChangeTrack_Fix_A]

  ([ID],[BitField])

  VALUES (1,0);

  END

  IF EXISTS (SELECT 1 FROM sys.change_tracking_tables)

  BEGIN

  DECLARE @i BIGINT

  , @NumberToAdd BIGINT = 1000000

  , @MinVersion BIGINT = 0

  , @CurrentVersion BIGINT = 0

  , @DateStart DATETIME = GETDATE()

  SELECT @i = 0

  , @MinVersion = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.[ChangeTrack_Fix_A]'))

  , @CurrentVersion = CHANGE_TRACKING_CURRENT_VERSION();

  SELECT @MinVersion

  , @CurrentVersion;

  DECLARE @TargetHolder BIGINT = @CurrentVersion + @NumberToAdd

  -- notice that I removed all the extra bits from the WHILE loop, just left the bare minimum.

  WHILE CHANGE_TRACKING_CURRENT_VERSION() < @TargetHolder

  UPDATE dbo.[ChangeTrack_Fix_A] SET BitField =1;

  SELECT @MinVersion = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.[ChangeTrack_Fix_A]'))

  , @CurrentVersion = CHANGE_TRACKING_CURRENT_VERSION();

  SELECT @MinVersion

  , @CurrentVersion

  , @NumberToAdd/(DATEDIFF(second,@DateStart,GETDATE())*1.0) AS CTsPerSecond

  , @NumberToAdd/(DATEDIFF(minute,@DateStart,GETDATE())*1.0) AS CTsPerMinute;

  END

  Results:

  MinVersion 121127

  CurrentVersion 1121128

  CTsPerSecond 3215.43408360128617

  CTsPerMinute 166666.66666666666666

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]