SQL Server Skripts für „Insert, Delete und Update“

Dieser Abschnitt enthält die create und insert Anweisungen um die Beispiele aus Kapitel 8, „Schreiboperationen“ in einer Oracle Datenbank durchzuführen. Eine einzelne Abfrage liefert die Ergebnisse für alle drei Abschnitte dieses Kapitels.

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;

Vorherige Seite Nächste Seite

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

SQL Server Skripts für „Insert, Delete und Update“

Über den Autor

Der 3-Minuten Test

Inhaltsverzeichnis

Aktuelle Blog Posts

Geek-Ware

Mit Markus Winand verbinden

SQL Server Skripts für „Insert, Delete und Update“

Über den Autor

Sein Buch kaufen

Sein Training

Der 3-Minuten Test

Inhaltsverzeichnis

Aktuelle Blog Posts

Geek-Ware

Mit Markus Winand verbinden