Guest User

Untitled

a guest
Jun 7th, 2017
125
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE @mod INT = 1000,
  2. @start_time DATETIME,
  3. @end_time DATETIME;
  4.  
  5. DROP TABLE IF EXISTS dbo.STG_1048576;
  6. CREATE TABLE dbo.STG_1048576 (ID BIGINT NOT NULL);
  7. INSERT INTO dbo.STG_1048576
  8. SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
  9. FROM master..spt_values t1
  10. CROSS JOIN master..spt_values t2;
  11.  
  12. DROP TABLE IF EXISTS dbo.CCI_BIGINT;
  13. CREATE TABLE dbo.CCI_BIGINT (ID BIGINT NOT NULL, INDEX CCI CLUSTERED COLUMNSTORE);
  14.  
  15. DROP TABLE IF EXISTS #RES;
  16. CREATE TABLE #RES (MOD_NUM INT NOT NULL, TIME_IN_MS INT NOT NULL);
  17.  
  18.  
  19. WHILE @mod <= 20000
  20. BEGIN
  21.     TRUNCATE TABLE dbo.CCI_BIGINT;
  22.  
  23.     SET @start_time = GETDATE();
  24.  
  25.     INSERT INTO dbo.CCI_BIGINT WITH (TABLOCK)
  26.     SELECT ID % @mod
  27.     FROM dbo.STG_1048576
  28.     OPTION (MAXDOP 1);
  29.  
  30.     SET @end_time = GETDATE();
  31.  
  32.     INSERT INTO #RES VALUES (@mod, DATEDIFF(MILLISECOND, @start_time, @end_time));
  33.     SET @mod = @mod + 1000;
  34.  
  35. END;
  36.  
  37. SELECT * FROM #RES
RAW Paste Data