SQL | SEQUENCE (Generování ID), Nastavení CACHE – Pozor na Cache Size

Poslední aktualizace:

Pro generování ID (primárních klíčů) v aplikačních databázích nebo nebo surrogate keys  v datových skladech existuje řada přístupů. Většinou jsou používány GUIDy nebo numerická ID. Guidy jsou používány hlavně v aplikačních databázích a jsou generovány například pomocí funkce NEWID(). To má své výhody a nevýhody. Největší nevýhodou je performance, protože generování GUID a obecně např. spojování tabulek je pomalejší ve srovnání s číselnými datovými typy.

Pokud chceme vygenerovat číselná ID máme několik způsobů jak to udělat:

  • Založení sloupce tabulky jako IDENTITY(1,1)
  • Generování ID pomocí nějaké funkce např ROW_NUMBER()
  • Generování ID pomocí SEQUENCE

Sekvence jsou tedy jedním ze způsobů, jak vygenerovat v databázové tabulce ID. Tento článek bude zaměřen na to, jak sekvenci založit a používat. Také nakonec podíváme na to, jak sekvence performuje ve srovnání s jinými metodami generování číselných klíčů.

Synaxe Založení Sequence

Při založení sekvence stanovujeme jméno, datový typ, číslo od kterého sekvence začíná, increment, minimální/maximální hodnotu sekvence a případně CACHE se stanovením size (případně NO CACHE).

CREATE SEQUENCE [dbo].[Sequence_Name]
AS [INT]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100000
CACHE 1000

Zavolání Sekvence, Používání OVER (ORDER BY)

Poté co založíme sekvenci si můžeme zavoláním sekvence postupně vyžadovat následující pořadová čísla ze sequence a ta nám je vrátí.

SELECT NEXT VALUE FOR [dbo].[Sequence_Name];

První dotaz nám vrátí 1 (sequence startuje od 1). Druhý dotaz by nám vrátil 2 (1 již byla podána a increment je 1) a tak dále. Pokud bychom narazili na limit MAXVALUE 100000, dostaneme error hlášku “The sequence object ‘Sequence_Name’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated“.

Sekvence můžeme volat v kombinaci s FROM, v tomto případě očíslujeme již existující záznamy v nějaké tabulce. Současně můžeme při generování zohlednit seřazení (v tomto případě budeme číslování začínat od nejnižšího datumu).

SELECT
  NEXT VALUE FOR [dbo].[Sequence_Name] OVER (ORDER BY [Datum]) AS [ID]
  ,[Column]
FROM [Tabulka]

Restart Sekvence

Sekvenci můžeme pochopitelně restartovat celou nebo ji vrátit zpět k určité hodnotě.

Restartování celé Sequence:

ALTER SEQUENCE [dbo].[Sequence_Name] RESTART;
SELECT NEXT VALUE FOR [dbo].[Sequence_Name];

Výsledkem bude 1

Stanovení nové starting value:

ALTER SEQUENCE [dbo].[Sequence_Name] RESTART WITH 151;
SELECT NEXT VALUE FOR [dbo].[Sequence_Name];

Výsledkem bude 151

Srovnání SEQUENCE, IDENTITY a ROW_NUMBER(), CACHE Performance Problem

Níže se můžete podívat jak performují jednotlivé metody generování ID. Testoval jsem generování ID do tabulky obsahující 400 000 záznamů (číslo INT) a ID byla přiřazena po seřazení ORDER BY. Výsledek je ve vteřinách, jedná se teda o velmi hrubé testování. Výsledky se mohou samozřejmě lišit s velikostí a koplexitou tabulek. Pro potřeby toho co chci ukázat to ale dostačuje.

Sequence vs identity(1,1) vs row_number

Poznatky

(i) Podle výsledků je nejrychlejší generování pomocí ROW_NUMBER OVER (ORDER BY). Tady bych rád upozornil, že tato metoda nebude performovat tak dobře nad rozsáhými tabulkami. Naopak dobře bude fungovat nad (velmi) jednoduchými tabulkami nebo dotazy, kde nebudeme používat složité ORDER BY operace.

(ii) IDENTITY bude v průměru fungovat dobře všude v případě, že performance generování klíčů nepotřebujeme řešit a nechceme se o ni moc aktivně starat.

(iii) U SEQUENCE toho můžeme hodně získat, ale i hodně zkazit. To, jak performuje SEQUENCE je závislé na nastavení CACHE Size. Je vidět, že Cache je při používání sekvencí povinnost a to platí dvojnásob v případě, kdy číslujeme větší množství záznamů v rámci jedné transakce.

(iv) CACHE je potřeba nastavit s argumentem Size (a tu je potřeba vhodně stanovit), protože pokud CACHE založíme bez něj, chová se sekvence jako by CACHE nastavena nebyla vůbec.

V dokumentaci Microsoft se píše, že Sequence je možné založit pouze s property CACHE a v tomto případě se Size sama nastaví, ale není tomu tak.

Závěr: SEQUENCE jsou skvělý způsob jak generovát klíče za předpokladu používání CACHE a současně je potřeba nastavit CACHE s dostatečnou Size. To platí zejména pro případ, kdy chceme jednorázově vygenerovat klíče pro velké množství záznamů.

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Category: SQL Administrace SQL příkazy

About Ing. Jan Zedníček - BI Developer, Finance controller

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.