Změny v SQL Serveru 2000 - indexované pohledy (Views)

Jan Svoboda a Michal Rogozný: Nová verze SQL serveru firmy Microsoft MS SQL 2000 přináší velké množství významných změn oproti předcházející verzi MS SQL 7. Změny se týkají mnoha oblastí a mají vliv zejména na výkon, škálovatelnost a spolehlivost SQL serveru. Mnoho pozitivních změn se dotklo nejen samotného SQL serveru, ale také jeho nástrojů: Query Analyzeru a Enterprise Manageru.

Popsat veškeré změny by vydalo přinejmenším na celou knihu. V první fázi seznamování se s novým produktem bude hlavní oblast zájmu vývojářů zřejmě upřena na zlepšení v Relačním databázovém engine a v rozšíření jazyka Transact-SQL. V sérii těchto malých článků si ukážeme některé ze změn. Zaměříme se hlavně na oblasti zlepšení výkonu a zjednodušení programátorské činnosti.

Dnes si ukážeme změny týkající se pohledů. Porovnáme si možnosti „klasických“ pohledů a nově pohledů indexovaných.

Výhody pohledů

• soustřeďují potřebná data uživateli
• skrývají uživateli složitost podkladových dat
• zjednodušují správu uživatelských oprávnění
• definují uspořádání dat pro export do jiných aplikací

Obvyklá použití

• podmnožina řádků nebo sloupců základní tabulky
• spojení dvou nebo více základních tabulek
• statistické souhrny
• podmnožina jiného pohledu, případně určitá kombinace pohledů základních tabulek

Až do verze MS SQL 7 jsme se na pohledy dívali a mohli je chápat jako pevně uložený SQL dotaz, který čekal na provedení. Jakmile byl dotaz proveden, vytvořila se virtuální tabulka, ve které se zobrazovala data z jedné či více propojených tabulek. Data zobrazená pohledem tedy nebyla fyzicky uložena přesně ve formě, v jaké byla zobrazena, nýbrž byla „odleskem“ či extraktem dat uložených v podkladových tabulkách. Na tyto virtuální tabulky se samozřejmě dalo odkazovat jako na běžné tabulky.

Vykonání výrazu uloženého v pohledu samozřejmě znamenalo pro SQL server určitou časovou režii. Velká část této režie byla spotřebována na dotazy do podkladových tabulek, indexů a sestavení výsledné množiny – virtuální tabulky. To by příliš nevadilo, pokud by aplikaci sdílelo několik málo uživatelů, nebo kdyby se pohled používal zřídka. Úplně jiná situace ale nastává v okamžiku, kdy máme velmi zatíženou aplikaci. Tady každá milisekunda v celkovém součtu časových režií může znamenat obtížně řešitelný problém s výkonem.

Vývojáři, kteří se potýkali s výkonem svých aplikací, samozřejmě našli jednu z možných cest, jak se této časové režie zbavit. Princip spočíval v tom, že vytvořili několik pomocných tabulek a na podkladové tabulce, či podkladových tabulkách potom umístili spouště. Spouště zajišťovaly aktualizaci pomocné tabulky při modifikacích dat v tabulkách podkladových. Aplikace pak ke své práci využívala pomocných tabulek namísto pohledů (to samozřejmě mělo a má smysl pouze pro nejexponovanější pohledy - dotazy). Časová režie se snížila, protože data se v pomocných tabulkách nacházela fyzicky a odpadl čas potřebný pro spojování tabulek a vytváření tabulky virtuální. Ideální samozřejmě bylo ukládat data do dobře zaindexovaných tabulek anebo v přesném pořadí potřebném pro zobrazování tak, aby odpadla sortovací (třídící) režie.

Ve verzi MS SQL 2000 se už na pohledy můžeme dívat jinýma očima, protože klasická varianta pohledů byla rozšířena o indexované (materializované) pohledy. Indexované pohledy totiž zvyšují výkon. Nárůst výkonu pozorujeme zejména u „rozhodovacích procesů“ (decision support), kde mají vliv na časovou režii náročné agregační funkce.

Důležitou novinkou tedy je, že indexy lze nyní nastavit nejen nad tabulkami, ale také nad pohledy. A aby změn nebylo málo, je možné nastavit indexy v pohledech i nad vypočtenými sloupci, což je obrovská výhoda při vytváření covering indexů.

Další výrazná změna ovlivňující výkon se skrývá v možnosti určení způsobu třídění u indexů. K defaultnímu vzestupnému třídění nyní přibylo rovněž třídění sestupné.

Výsledná množina dat indexovaného pohledu je fyzicky uložena v databázi. Tím se docílilo toho, že se reakční doby výrazně zkrátily. Uložená množina je neustále svázána s podkladovou tabulkou či tabulkami, přičemž je automaticky udržována a aktualizována na základě jejich modifikací.

Na první pohled se může zdát, že to má jednu nevýhodu - data jsou fyzicky uložena vícekrát a my zbytečně plýtváme prostředky počítače. Větší nároky na diskovou kapacitu (cena za 1MB neustále klesá) jsou ovšem kompenzovány zvýšením výkonu, a o to nám většinou jde.

Buď budeme modifikovat existující pohled, anebo vytvoříme nový.

Je nutno (všechna nastavení a syntaxe – viz kód níže):

• Vytvořit, či modifikovat pohled s volbou WITH SCHEMABINDING – tato direktiva zajistí provázání s podkladovými tabulkami
• Před modifikací či tvorbou nastavit některé ANSI volby
• Na tabulkové objekty je nutno se odkazovat názvem včetně vlastníka objektu

Nejdříve si vytvoříme nový indexovaný pohled. K našim experimentům použijeme vzorovou databázi Northwind, která se instaluje společně s SQL serverem.

Najdeme zde běžný pohled s názvem „Order Subtotals“. My si vytvoříme nový pohled, ve kterém si ukážeme výhody indexovaného pohledu.

1. Nejdříve však původní pohled „Order Subtotals“ spustíme a podíváme se na časovou režii a plán provádění.

Poznámka:

Plán provádění zapnete v Query Analyzeru volbou „Query / Show Execution Plan“.

USE Northwind
GO
SELECT * FROM dbo.[Order Subtotals]
GO

Celková časová režie: 5

2. Nyní vytvoříme indexovaný pohled, který je výrazem a funkcionalitou shodný s původním pohledem „Order Subtotals“. Liší se pouze tím, že je pevně svázán s podkladovou tabulkou „Order Details“ a bude na něm v dalším kroku nastaven index.

Jak jsme se již jednou zmínili, je potřeba před vytvořením nového pohledu nastavit ANSI volby:

-- nastavení ANSI voleb
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,
ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO

Pak vytvoříme nový pohled:

-- vytvoření pohledu
CREATE  view dbo.vi_Experiment1 WITH SCHEMABINDING AS
SELECT  OrderID,Sum(CONVERT(money,(UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal,
COUNT_BIG<) AS DetailCount
FROM dbo.[Order Details]
GROUP BY OrderID
GO

Poznámka: Pro seskupování pomocí klauzule GROUP BY je v pohledech příkaz COUNT_BIG<) povinný. Pokud bychom pohled vytvořili bez něho, nebylo by možné později vytvořit clusterovaný index.

3. Nad pohledem nastavíme unikátní clusterovaný index.

CREATE UNIQUE CLUSTERED INDEX IX_Experiment1 ON vi_Experiment1(OrderID)
GO

Poznámka: Mějte na paměti, že je vždy nutné před vytvořením indexu nastavit hodnotu Quoted_Identifier na ON. My jsme to udělali při nastavování ANSI hodnot hned na začátku.

4. Spustíme indexovaný pohled.

Před samotným spuštěním pohledu musíme ještě zajistit, aby si SQL server vytvořil nový plán provádění, jinak by mohl použít původní - existující před vytvořením indexovaného pohledu.

Opět je nutné zapnout volbu QUOTED_IDENTIFIER.

SET QUOTED_IDENTIFIER ON
GO
UPDATE STATISTICS [vi_Experiment1]
GO

SELECT * FROM vi_Experiment1
GO

Celková časová režie: 3,5

Na konkrétním příkladu jsme si ukázali, že porovnáním časové režie původního „klasického“ pohledu a nového indexovaného pohledu jsme dosáhli přibližně 30% zrychlení. Rozdíl bude patrnější v okamžiku, kdy podkladové tabulky budou obsahovat velké množství záznamů. Markantnější zrychlení se rovněž projeví, jakmile se indexovaný pohled použije v dotazech spojený s více tabulkami.

Jakmile indexovaný pohled vytvoříte, můžete z jeho výhod ihned těžit, aniž byste jej museli přímo volat. V případech, že se spouští podobné dotazy, optimalizátor dotazů sám rozhodne, kdy bude účelnější použít indexovaný pohled a kdy ne. Pro vyzkoušení si na závěr spusťte původní pohled „Order Subtotals“ se zapnutým plánem provádění a uvidíte, že se místo skenování podkladové tabulky použilo skenování nově vytvořeného clusterovaného indexu na nově vytvořeném indexovaném pohledu. Časová režie tím byla podstatně snížena.

Jistě jste si všimli, že jako index byl použit unikátní clusterovaný index. Neclusterované indexy jsou rovněž podporovány, ale pouze na pohledech, na kterých byl již dříve definován jiný clusterovaný index. Řádkovým lokátorem pro neclusterovaný index se tedy stane clusterovaný indexový klíč clusterovaného indexu.

A nezapomeneme po sobě ještě uklidit :)

drop view vi_Experiment1
GO

Použité zdroje:

• SQL Server – Implementace databází (Microsoft Press)
• MSDN
• Books Online

Testováno na MS SQL 2000