2012-03-12SQL Server Scripts for “Insert, Delete and Update”

This section contains the create and insert statements to run the examples from Chapter 8 in an SQL Server database. There is only one query that reports all figures for the insert, delete and update sections.

WITH generate_series_1k(n) AS (
   SELECT 0
    UNION ALL
   SELECT n + 1
     FROM generate_series_1k
    WHERE N + 1 < 10000
), generate_series(n, n1, n2) AS (
   SELECT gs2.n * 1000 + gs1.n, gs1.n, gs2.n
     FROM generate_series_1k gs1, 
          generate_series_1k gs2
    WHERE gs1.n < 1000
)
SELECT n id1
     , CAST(CEILING(ABS(CAST(NEWID() AS BINARY(6)) % 900000))
       + 100000 AS numeric) id2
     , CAST(CEILING(ABS(CAST(NEWID() AS BINARY(6)) % 900000))
       + 100000 AS numeric) id3
     , CAST(CEILING(ABS(CAST(NEWID() AS BINARY(6)) % 900000))
       + 100000 AS numeric) id4
     , CAST(CEILING(ABS(CAST(NEWID() AS BINARY(6)) % 900000))
       + 100000 AS numeric) id5
  INTO scale_write_0
  FROM generate_series
OPTION(MAXRECURSION 32767);
GO

SELECT *
  INTO scale_write_1
  FROM scale_write_0;
GO

SELECT *
  INTO scale_write_2
  FROM scale_write_0;
GO

SELECT *
  INTO scale_write_3
  FROM scale_write_0;
GO

SELECT *
  INTO scale_write_4
  FROM scale_write_0;
GO

SELECT *
  INTO scale_write_5
  FROM scale_write_0;
GO

CREATE INDEX scale_write_1_1 on scale_write_1(id1);
GO

CREATE INDEX scale_write_2_1 on scale_write_2(id1);
GO
CREATE INDEX scale_write_2_2 on scale_write_2(id2, id1);
GO

CREATE INDEX scale_write_3_1 on scale_write_3(id1);
GO
CREATE INDEX scale_write_3_2 on scale_write_3(id2, id1);
GO
CREATE INDEX scale_write_3_3 on scale_write_3(id3, id2, id1);
GO

CREATE INDEX scale_write_4_1 on scale_write_4(id1);
GO
CREATE INDEX scale_write_4_2 on scale_write_4(id2, id1);
GO
CREATE INDEX scale_write_4_3 on scale_write_4(id3, id2, id1);
GO
CREATE INDEX scale_write_4_4 on scale_write_4(id4, id3, id2
                                             ,id1);
GO

CREATE INDEX scale_write_5_1 on scale_write_5(id1);
GO
CREATE INDEX scale_write_5_2 on scale_write_5(id2, id1);
GO
CREATE INDEX scale_write_5_3 on scale_write_5(id3, id2, id1);
GO
CREATE INDEX scale_write_5_4 on scale_write_5(id4, id3, id2
                                             ,id1);
GO
CREATE INDEX scale_write_5_5 on scale_write_5(id5, id4, id3
                                             ,id2, id1);
GO

CREATE PROCEDURE 
run_insert(@idxes INT, @q INT, @n INT, @mode VARCHAR(64) OUT)
AS BEGIN
DECLARE @r2  INT = CEILING(rand() * 9000000)+1000000;
DECLARE @r3  INT = CEILING(rand() * 9000000)+1000000;
DECLARE @r4  INT = CEILING(rand() * 9000000)+1000000;
DECLARE @r5  INT = CEILING(rand() * 9000000)+1000000;
DECLARE @d1  INT;
  WHILE (@n > 0) BEGIN
  SET @d1 = CEILING(rand() * @q);
  IF @idxes = 0 
         INSERT INTO scale_write_0 (id1, id2, id3, id4, id5)
                            VALUES (@d1, @r2, @r3, @r4, @r5);
  ELSE IF @idxes = 1
         INSERT INTO scale_write_1 (id1, id2, id3, id4, id5)
                            VALUES (@d1, @r2, @r3, @r4, @r5);
  ELSE IF @idxes = 2 
         INSERT INTO scale_write_2 (id1, id2, id3, id4, id5)
                            VALUES (@d1, @r2, @r3, @r4, @r5);
  ELSE IF @idxes = 3
         INSERT INTO scale_write_3 (id1, id2, id3, id4, id5)
                            VALUES (@d1, @r2, @r3, @r4, @r5);
  ELSE IF @idxes = 4
         INSERT INTO scale_write_4 (id1, id2, id3, id4, id5)
                            VALUES (@d1, @r2, @r3, @r4, @r5);
  ELSE IF @idxes = 5 
         INSERT INTO scale_write_5 (id1, id2, id3, id4, id5)
                            VALUES (@d1, @r2, @r3, @r4, @r5);
  SET @n = @n - 1;
  END;
  SET @mode = 'insert';
END;
go

CREATE PROCEDURE
run_delete(@idxes INT, @q INT, @n INT, @mode VARCHAR(64) OUT)
AS BEGIN
DECLARE @cnt INT = @n;
DECLARE @aff INT = 0;
DECLARE @d1  INT;
  WHILE (@cnt >0) BEGIN
  SET @d1 = CEILING(rand() * @q) + @q;
  IF @idxes = 1  
         DELETE FROM scale_write_1 WHERE id1 = @d1;
  ELSE IF @idxes = 2 
         DELETE FROM scale_write_2 WHERE id1 = @d1;
  ELSE IF @idxes = 3 
         DELETE FROM scale_write_3 WHERE id1 = @d1;
  ELSE IF @idxes = 4 
         DELETE FROM scale_write_4 WHERE id1 = @d1;
  ELSE IF @idxes = 5 
         DELETE FROM scale_write_5 WHERE id1 = @d1;
  SET @aff = @aff + @@ROWCOUNT;
  SET @cnt = @cnt - 1;
  END;  
  SET @mode = CASE WHEN @aff = @n THEN 'delete' ELSE NULL END;
END;

go

CREATE PROCEDURE
run_update_all(@idxes INT, @q INT, @n INT, @mode VARCHAR(64) OUT)
AS BEGIN
DECLARE @r2  INT = CEILING(rand() * 9000000)+1000000;
DECLARE @r3  INT = CEILING(rand() * 9000000)+1000000;
DECLARE @r4  INT = CEILING(rand() * 9000000)+1000000;
DECLARE @r5  INT = CEILING(rand() * 9000000)+1000000;
DECLARE @cnt INT = @n;
DECLARE @aff INT = 0;
DECLARE @d1  INT;
  WHILE (@cnt >0) BEGIN
    SET @d1 = CEILING(rand() * @q) + 2 * @q;
    IF @idxes = 1 
       UPDATE scale_write_1
          SET id2=@r2, id3=@r3, id4=@r4, id5=@r5 WHERE id1=@d1;
    ELSE IF @idxes = 2 
       UPDATE scale_write_2
          SET id2=@r2, id3=@r3, id4=@r4, id5=@r5 WHERE id1=@d1;
    ELSE IF @idxes = 3 
       UPDATE scale_write_3
          SET id2=@r2, id3=@r3, id4=@r4, id5=@r5 WHERE id1=@d1;
    ELSE IF @idxes = 4 
       UPDATE scale_write_4
          SET id2=@r2, id3=@r3, id4=@r4, id5=@r5 WHERE id1=@d1;
    ELSE IF @idxes = 5 
       UPDATE scale_write_5
          SET id2=@r2, id3=@r3, id4=@r4, id5=@r5 WHERE id1=@d1;

    SET @aff = @aff + @@ROWCOUNT;
    SET @cnt = @cnt - 1;
  END;  
  SET @mode = CASE WHEN @aff = @n THEN 'update all'
                   ELSE NULL END;
END;
go


CREATE PROCEDURE
run_update_one(@idxes INT, @q INT, @n INT, @mode VARCHAR(64) OUT)
AS BEGIN
DECLARE @r2 INT = CEILING(rand() * 9000000)+1000000;
DECLARE @cnt INT = @n;
DECLARE @aff INT = 0;
DECLARE @d1  INT;
  WHILE (@cnt >0) BEGIN
    SET @d1 = CEILING(rand() * @q) + 3 * @q;
    IF @idxes = 1 -- no index updated
           UPDATE scale_write_1 SET id2 = @r2 WHERE id1=@d1;
    ELSE IF @idxes = 2 -- one index updated
           UPDATE scale_write_2 SET id2 = @r2 WHERE id1=@d1;  
    ELSE IF @idxes = 3 -- one index updated
           UPDATE scale_write_3 SET id3 = @r2 WHERE id1=@d1;
    ELSE IF @idxes = 4 -- one index updated
         UPDATE scale_write_4 SET id4 = @r2 WHERE id1=@d1; 
    ELSE IF @idxes = 5 -- one index updated
         UPDATE scale_write_5 SET id5 = @r2 WHERE id1=@d1;
    ELSE SET @aff = 0;
    SET @mode = CASE WHEN @@ROWCOUNT = 1 THEN 'update one'
                     ELSE NULL END;
    SET @aff = @aff + @@ROWCOUNT;
    SET @cnt = @cnt - 1;
  END;  
  SET @mode = CASE WHEN @aff = @n THEN 'update one'
                   ELSE NULL END;
END;
go



CREATE PROCEDURE
test_write_scalability (@n int, @inner int)
AS BEGIN
DECLARE @iter INT;
DECLARE @indxs  INT;
DECLARE @strt DATETIME2;
DECLARE @dur  NUMERIC;
DECLARE @cmnd INT;
DECLARE @q    INT;
DECLARE @c1   VARCHAR(64);
DECLARE @table TABLE
( indxes  NUMERIC NOT NULL,
  mode    VARCHAR(64) NOT NULL,
  seconds NUMERIC NOT NULL,
  cnt     NUMERIC NOT NULL);

  SELECT @q = (max(id1) - min(id1))/4 FROM scale_write_1;
  SET @iter = 0;
  WHILE (@iter < @n) BEGIN
    SET @cmnd = 0;
    WHILE (@cmnd <= 3) BEGIN
      SET @indxs = 0;
      WHILE (@indxs <= 5) BEGIN
        SET @strt = SYSDATETIME();
        IF (@cmnd = 0)
           exec [dbo].run_insert     @indxs, @q, @inner
                                   , @mode=@c1 OUTPUT;
        ELSE IF (@cmnd = 1) 
           exec [dbo].run_update_all @indxs, @q, @inner
                                   , @mode=@c1 OUTPUT;
        ELSE IF (@cmnd = 2) 
           exec [dbo].run_update_one @indxs, @q, @inner
                                   , @mode=@c1 OUTPUT;
        ELSE IF (@cmnd = 3) BEGIN
           exec [dbo].run_delete     @indxs, @q, @inner
                                   , @mode=@c1 OUTPUT;
        END;

        SET @dur = datediff(microsecond, @strt, SYSDATETIME());
        IF @c1 IS NOT NULL
        BEGIN
          INSERT INTO @table
          VALUES (@indxs, @c1, @dur, 1);
        END;
        SET @indxs = @indxs +1;
      END;
      SET @cmnd = @cmnd +1;
    END;
    SET @iter = @iter + 1;
  END;
  SELECT indxes, mode, seconds, cnt from @table;
END;

SET NOCOUNT ON;

CREATE TABLE #res (
  indxes  NUMERIC NOT NULL,
  mode    VARCHAR(64) NOT NULL,
  seconds NUMERIC NOT NULL,
  cnt     NUMERIC NOT NULL); 
GO

INSERT INTO #res
EXEC test_write_scalability 1000;
go

SELECT indxes, [insert], [delete], [update all], [update one]
  FROM (SELECT indxes, mode, seconds/1000000 seconds
          FROM #res
       ) x
         PIVOT (AVG(seconds)
           FOR mode
            IN ([insert],[delete],[update all],[update one]))
            AS AvgExecTime
 ORDER BY indxes;