MSSQL i LIMIT z mySQL'a

Mój pierwsyz post :‑) Wybaczcie niedociągnięcia.

Dzisiaj przyszło mi się zmierzyć z tematem: Implementing Virtual Mode with Just-In-Time Data Loading in the Window...

W pewnym momencie kodu znajduje się polecenie budujące zapytanie SQL:

         command.CommandText = "Select Top " + rowsPerPage + " " +
            CommaSeparatedListOfColumnNames + " From " + tableName +
            " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " +
            lowerPageBoundary + " " + columnToSortBy + " From " +
            tableName + " Order By " + columnToSortBy +
            ") Order By " + columnToSortBy;
        adapter.SelectCommand = command;

Pomyślałem sobie wtedy: "ale by się przydała klauzula LIMIT z mySQL’a." Zrobiłem test na jednej z tabel w mojej bazie danych

CREATE TABLE [dbo].[Expedition](
	[ID_Expedition] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[ID_Receiver] [int] NOT NULL,  -- klucz obcy
	[ExpeditionDate] [datetime] NULL,
	[ExpeditionNumber] [varchar](16) NOT NULL
)

Są w niej 8192 rekordy. Postanowiłem sprawdzić jak wydajnościowo będzie się miało to zapytanie SQL. Utworzyłem kod SQL który wyciąga po 10 kolejnych wierszy z tabeli, wrzuca do tabeli tymczasowej żeby ich nie wyświetlało w SSMS:

DECLARE @FIRST INT, @COUNT INT, @CNT INT
SET @COUNT=10
SET @FIRST=0
SET @CNT=(SELECT COUNT(*) FROM Expedition)

PRINT 'START ' + CONVERT(CHAR(28), GETDATE(), 121)
WHILE @FIRST<@CNT
BEGIN

   SELECT * INTO #tmp FROM (
    SELECT TOP (@COUNT) *
     FROM Expedition
     WHERE ID_Expedition NOT IN 
     (
      SELECT TOP (@FIRST) ID_Expedition
       FROM Expedition
       ORDER BY ID_Expedition ASC
    )
    ORDER BY ID_Expedition ASC
   ) AS A
   DROP TABLE #tmp
   SET @FIRST=@FIRST+10
END
PRINT 'END ' + CONVERT(CHAR(28), GETDATE(), 121)
GO

Czas realizacji: 3.627 sec START 2011-06-22 13:12:49.310 END 2011-06-22 13:12:52.937

Hmmm… zacząłem się zastanawiać czy da się szybciej. Pogrzebałem i znalazłem najbardziej powtarzające się zapytanie z podwójnym TOP i sortowaniem. Sprawdziłem:

DECLARE @FIRST INT, @COUNT INT, @CNT INT
SET @COUNT=10
SET @FIRST=0
SET @CNT=(SELECT COUNT(*) FROM Expedition)

PRINT 'START ' + CONVERT(CHAR(28), GETDATE(), 121)
WHILE @FIRST<@CNT
BEGIN

   SELECT * INTO #tmp FROM (
    SELECT TOP (@COUNT) * FROM (
     SELECT TOP (@COUNT+@FIRST) *
      FROM Expedition
      ORDER BY ID_Expedition ASC
     ) AS D ORDER BY ID_Expedition DESC
    ) AS A ORDER BY ID_Expedition ASC

   DROP TABLE #tmp

   SET @FIRST=@FIRST+10

END
PRINT 'END ' + CONVERT(CHAR(28), GETDATE(), 121)
GO

Czas realizacji: 7.140 sec START 2011-06-22 12:54:54.467 END 2011-06-22 12:55:01.607

Hmmm…. Dwa razy dłużej. Odpada.

Pomyślałem sobie: „Zaraz zaraz. Tabela ma przecież kolumnę ID_Expedition typu IDENTITY. Zakładając, że ma kolejne ID od 1 co 1 ….”. No to sprawdziłem:

DECLARE @FIRST INT, @COUNT INT, @CNT INT
SET @COUNT=10
SET @FIRST=1
SET @CNT=(SELECT COUNT(*) FROM Expedition)

PRINT 'START ' + CONVERT(CHAR(28), GETDATE(), 121)
WHILE @FIRST<@CNT
BEGIN

   SELECT TOP (@COUNT) * INTO #Tmp
    FROM Expedition
    WHERE ID_Expedition>=@FIRST
    ORDER BY ID_Expedition ASC

   DROP TABLE #tmp

   SET @FIRST=@FIRST+10
END
PRINT 'END ' + CONVERT(CHAR(28), GETDATE(), 121)
GO

Czas realizacji: 0.757 sec START 2011-06-22 12:55:01.623 END 2011-06-22 12:55:02.390

No rewelacja. Nieporównywalnie szybciej niż pierwsze rozwiązanie.

No tak ale co będzie jak pokasuję rekordy albo będę miał jako klucz więcej niż jedną kolumnę np. przy złączeniach tabel. Tutaj postanowiłem sprawdzić jak będzie się miało w tym przypadku wydajność z ROW_NUMBER().

DECLARE @FIRST INT, @COUNT INT, @CNT INT
SET @COUNT=10
SET @FIRST=1
SET @CNT=(SELECT COUNT(*) FROM Expedition)

PRINT 'START ' + CONVERT(CHAR(28), GETDATE(), 121)
WHILE @FIRST<=@CNT
BEGIN

   SELECT TOP (@COUNT) * INTO #Tmp FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY ID_Expedition ASC) AS RowNumber, *
     FROM Expedition
   ) AS a WHERE RowNumber >= @First
   ORDER BY RowNumber

   DROP TABLE #tmp

   SET @FIRST=@FIRST+10
END
PRINT 'END ' + CONVERT(CHAR(28), GETDATE(), 121)
GO

Czas realizacji: 2.560 sec START 2011-06-22 12:55:02.390 END 2011-06-22 12:55:04.950

No to i tak o sekundę lepiej niż rozwiązanie na stronach Microsoftu. Ponadto w klauzuli OVER (ORDER BY ID_Expedition ASC) można umiwścić wiecej kolumn wg, których to ma być posortowane np.: OVER (ORDER BY ID_Receiver ASC, ExpeditionDate ASC).

Tak tylko chciałem sie z Wami podzielić wnioskami. :‑)

Pozdrawiam.

P.S. Przydała by sie mozliwość wspawiania pustych spacji szczególnie w kodzie.