SQL Server Skripts für „Teilergebnisse“

Dieser Abschnitt enthält die create und insert Anweisungen um die Beispiele aus Kapitel 7, „Teilergebnisse“ in einer SQL Server Datenbank durchzuführen.

Top-N Zeilen Abfragen

Der Skalierungstest wird nach derselben Methode wie im Kapitel „Testen und Skalierbarkeit“ durchgeführt.

CREATE FUNCTION [dbo].test_top_n_scalability (@n int)
   RETURNS @table TABLE
( section  NUMERIC NOT NULL PRIMARY KEY,
  duration NUMERIC NOT NULL,
  rows     NUMERIC NOT NULL)
AS BEGIN
   DECLARE @strt DATETIME2
   DECLARE @iter INT
   DECLARE @xsec INT
   DECLARE @xcnt INT
   DECLARE @xrnd INT

   SET @iter = 0
   WHILE (@iter < @n) BEGIN
      SET @xsec = 0
      WHILE (@xsec < 300) BEGIN
         SET @strt = SYSDATETIME()

         SELECT @xcnt = COUNT(*)
           FROM (SELECT TOP 100 *
                   FROM scale_data
                  WHERE section=@xsec
                  ORDER BY id2) tlb; 

         IF @iter = 0 BEGIN
           INSERT INTO @table
           VALUES ( @xsec
                  , datediff(microsecond, @strt, SYSDATETIME())
                  , @xcnt);
         END; ELSE BEGIN
           UPDATE @table
              SET duration = duration 
                  + datediff(microsecond, @strt, SYSDATETIME())
                , rows = rows + @xcnt
            WHERE section = @xsec
         END;
         SET @xsec = @xsec + 1
      END;
      SET @iter = @iter + 1
   END;

   RETURN;
END;

GO

Zuerst mit ein pipelined Top-N mit einem Index der die order by-Klausel abdeckt:

CREATE INDEX scale_fast ON scale_data(section, id2, id1);
GO

SELECT * FROM [dbo].[test_top_n_scalability] (10);
GO

Danach mit einem Index, der nur die where-Klausel abdeckt. SQL Server verwendet den Index aber nicht, solange die ID2 Spalte nicht inkludiert wird. Der Test ist damit nicht mehr exakt das selbe wie für andere Datenbanken, die Skalierbarkeit ist aber ähnlich.

DROP INDEX scale_fast ON scale_data;
GO

CREATE INDEX scale_slow ON scale_data(section, id1, id2);
GO

SELECT * FROM [dbo].[test_top_n_scalability] (10);
GO

Durch Ergebnisse Blättern

CREATE FUNCTION [dbo].test_topn_scalability (@n int)
   RETURNS @table TABLE
( section NUMERIC NOT NULL,
  mode    NUMERIC NOT NULL,
  page    NUMERIC NOT NULL,
  seconds NUMERIC NOT NULL)
AS BEGIN
  DECLARE @strt DATETIME2
  DECLARE @iter INT
  DECLARE @xmde INT
  DECLARE @page INT
  DECLARE @xsec INT
  DECLARE @c1 INT, @c2 INT;
  DECLARE @cont TABLE (
    section int NOT NULL,
    c1      int NOT NULL, 
    c2      int NOT NULL
  );

  SET @iter = 0
  WHILE (@iter < @n) BEGIN
    SET @xmde = 0
    WHILE (@xmde <= 1) BEGIN
      SET @page = 0
      WHILE (@page <= 100) BEGIN
        SET @xsec = 5
        WHILE (@xsec < 300) BEGIN
          SET @strt = SYSDATETIME()

          IF @xmde = 0 OR @page = 0 BEGIN
            DECLARE sql CURSOR FAST_FORWARD FOR
             SELECT id2, id1
               FROM (SELECT id2, id1
                          , ROW_NUMBER() OVER (ORDER BY id2, id1) rn
                       FROM scale_data
                      WHERE section = @xsec
                    ) result
              WHERE rn >  100 * (@page  )
                AND rn <= 100 * (@page+1);
         
          END; ELSE BEGIN
            SELECT @c2 = c2, @c1 = c1 FROM @cont WHERE section = @xsec;
            DECLARE sql CURSOR FAST_FORWARD FOR
             SELECT TOP 100 id2, id1
               FROM scale_data
              WHERE section = @xsec
                AND id2 >= @c2
                AND ( 
                       (id2 = @c2 AND id1 > @c1)
                    OR 
                       (id2 > @c2)
                    )
              ORDER BY id2, id1
          END;

          OPEN sql; FETCH NEXT FROM sql INTO @c2, @c1;
          WHILE @@FETCH_STATUS = 0 BEGIN
            FETCH NEXT FROM sql INTO @c2, @c1;
          END
          CLOSE sql; DEALLOCATE sql;

          INSERT INTO @table
          VALUES ( @xsec
                 , @xmde
                 , @page
                 , datediff(microsecond, @strt, SYSDATETIME())
                 );
          UPDATE @cont set c1 = @c1, c2 = @c2
           WHERE section = @xsec;
          IF @@ROWCOUNT = 0
           INSERT INTO @cont VALUES(@xsec, @c1, @c2);
          SET @xsec = @xsec + 1
        END;
        SET @page = @page + 1
      END;
      SET @xmde = @xmde + 1
    END;
    SET @iter = @iter + 1
  END;

  RETURN;
END;
GO

SELECT section, mode, page, sum(seconds)
  FROM [dbo].[test_topn_scalability] (10)
 WHERE section=10
 GROUP BY section, mode, page
 ORDER BY section, mode, page;
GO

Window-Funktionen

SQL Server 2008R2 kann den Index für eine Top-N Abfrage mit der window-Funktion ROW_NUMBER nutzen:

SELECT *
  FROM ( SELECT sales.*
              , ROW_NUMBER() OVER (ORDER BY sale_date DESC
                                          , sale_id   DESC) rn
           FROM sales
       ) tmp
 WHERE rn between 11 and 20
 ORDER BY sale_date DESC, sale_id DESC

|-Sort(ORDER BY:([sale_date] DESC, [sale_id] DESC))
  |-Filter(WHERE:([Expr1004]>=(11) AND [Expr1004]<=(20)))
    |-Top(TOP EXPRESSION:(20))
      |-Sequence Project(DEFINE:([Expr1004]=row_number))
        |-Segment
          |-Nested Loops(Inner Join, WITH ORDERED PREFETCH)
            |-Index Scan([sales].[sl_dtid], ORDERED BACKWARD)
            |-RID Lookup([sales],
               SEEK:([Bmk1000]=[Bmk1000])
               LOOKUP ORDERED FORWARD)

SQL Server liest den Index rückwärts, sodass keine Sortierung für die window-Funktion notwendig ist. Der Top-Schritt bricht die Ausführung der darunter liegenden Operationen ab, sobald 20 Zeilen geliefert wurden. Die letzten beiden Operationen – im Ausführungsplan oben angezeigt – entfernen die ersten Zehn Zeilen und sortieren das restliche Ergebnis. Diese Sortierung betrifft nur die zehn Zeilen des Ergebnisses, und wird daher nicht zum Performanceproblem werden.