Pchełki SQL: ROW_NUMBER() bez sortowania

xpil - 2017/10/12 - Pchełki /Pchełki SQL /

Każdy, kto przez jakiś czas pracował z językiem SQL wie, że prędzej czy później pojawia się potrzeba ponumerowania kolejnych rekordów. W tym celu wymyślono funkcję ROW_NUMBER(), która – jak sama nazwa wskazuje – generuje kolejne wartości od jeden wzwyż, dla każdego rekordu.

Przykład:

;WITH q1 AS ( SELECT CONVERT(VARCHAR(255), NEWID()) id
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
)
SELECT ROW_NUMBER() OVER (ORDER BY id) rn
, id
FROM q1;

Powyższe zapytanie wyświetli (ważne: alfabetycznie!) 10 losowych identyfikatorów i ponumeruje je od 1 do 10:

RN ID
1 09878F7F-75E5-418C-971C-1929475FADC8
2 19535BBA-DE30-4E31-8BFF-75275C3FAC0A
3 1E62CF95-62D0-4A11-9B97-2B66BE4457AE
4 30E4103A-1A3A-4D7C-B132-9CD7952DDAEC
5 7C03409E-C19D-4CC8-B066-771A537E1929
6 8240BC5D-ECE0-4FA6-8E8C-CC2A1993A1AD
7 884C49C7-26F0-40BA-B398-A06910BB4D46
8 8B35FF0E-D2AD-4C77-8850-56AC9E6EB701
9 A05F3F0A-E4CA-47CC-99E7-9803A0406FF4
10 D4306896-ED27-4912-83F7-63D2FB29C210

Jeżeli jednak zerkniemy na plan wykonania tego zapytania zobaczymy, że 100% zasobów zostało zużytych na operację sortowania danych (tak naprawdę PRAWIE 100%, wartości są zaokrąglane do najbliższej całości).

Czasem jednak bywa tak, że owszem, potrzebujemy ponumerować rekordy od jeden wzwyż, ale kompletnie nie interesuje nas ich kolejność. W takim scenariuszu sortowanie elementów to marnowanie zasobów serwera.

Jednak składnia funkcji ROW_ID() wymaga użycia OVER(ORDER BY …). bez tego nasze zapytanie nawet się nie skompiluje.

No i co?

Okazuje się, że można zastosować bardzo prosty (acz na pierwszy rzut oka nieoczywisty) trick, dzięki któremu unikniemy sortowania danych, jednocześnie zachowując zgodność ze składnią języka.

Trick wygląda następująco:

ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

Innymi słowy sortujemy wirtualnie utworzoną pustą kolumnę – i nic ponadto.

Pełne zapytanie z przykładu powyżej, ale już bez sortowania, wygląda tak:

;WITH q1 AS (         SELECT  CONVERT(VARCHAR(255), NEWID()) id
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
)
SELECT    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
        , id
FROM    q1;

Wynik:

RN ID
1 ECFDBF52-22DC-466A-B87B-82033E01FA2A
2 9E52B339-BC35-4646-A596-B2B28D273AA2
3 7E8AAB59-9F5D-457A-AF2E-4D4F8C9A9706
4 F7CE5A52-F4C6-4E20-948B-14EAE616F993
5 01BC815D-3E89-4DD1-9CE3-81B928FF8BFE
6 04812135-89A9-4699-9D1A-9C8F38900765
7 65DB014B-808E-4360-A658-BAC0E845333A
8 BCCF214E-58B0-42FB-AAF4-B7CFC89F861C
9 9725EFE4-595E-4F11-A976-3228B37DDBD2
10 BAEB3DCA-01E9-4BEF-9EA3-1AA3595534CD

Jak widać dane są nieposortowane, a plan zapytania się nieco uprościł:

Większość zasobów poszła na operację Constant Scan, czyli „samo gęste”. Nie marnujemy cennych cykli procesora na zbędne sortowanie.

Ciekawe? Więcej pchełek SQL tutaj: http://xpil.eu/k/pchelki-2/pchelki-sql/

Dodaj komentarz

avatar
  Subscribe  
Powiadom o
%d bloggers like this: