Optimalizace výkonu v MS SQL Management Studio

C# .NET Databáze Optimalizace výkonu v MS SQL Management Studio

ONEbit hosting Unicorn College Tento obsah je dostupný zdarma v rámci projektu IT lidem. Vydávání, hosting a aktualizace umožňují jeho sponzoři.

Optimalizaci databáze provádíme většinou přidáním databázového indexu na sloupec nebo skupinu sloupců, které často figurují v podmínkách dotazů. Optimalizace se často provádí až po vytvoření aplikace a to pouze u dotazů, které trvají dlouho nebo se spouští velmi často (vygenerování stránky trvá déle, než 500ms). Nemá smysl plýtvat časem vývojářů na optimalizaci dotazu, který se spouští jen zřídka. Z mých zkušeností lze ušetřit čas hlavně na straně serverového jazyka (např. PHP je velmi pomalé) než na straně databáze, které bývají většinou velmi rychlé.

U optimalizace databáze postupujeme následovně:

Obvykle se nejprve podíváme, zda je dotaz v pořádku a zda by nešel vyřešit lépe. Občas se díváme na problém ze špatného úhlu a zbytečně bychom optimalizovali špatný dotaz, když se může položit jinak.

Dále zkontrolujeme přítomnost databázových indexů, pokud znáte teorii okolo vyhledávacích algoritmů (stromů), určitě víte, že to má na výkon obrovský vliv.

Pokud máme s aplikací stále výkonnostní problémy, můžeme přistoupit k tzv. denormalizaci, kdy spojíme 2 nebo více tabulek do jedné, abychom se vyhnuli JOINování. Takováto operace má potom samozřejmě nepříznivý vliv na návrh aplikace. Bohužel často platí nepřímá úměrnost mezi hezky napsanou aplikací (míněno po stránce zdrojového kódu) a rychlou aplikací.

My si zde ukážeme nejčastější způsob optimalizace a to přidáním indexu na sloupec. Opět navážeme na náš vzdělávací projekt a vymyslíme si s ním výkonnostní problém.

Problém

Po několika měsících provozu se výrazně zvýšila zátěž serveru a nastala potřeba tuto situaci řešit. Nejčastějším dotazem je zobrazení obsahu článku podle sloupce PrettyURL. Tato hodnota se serveru odešle jako parametr v GET a server podle něj nalezne a vrátí požadovaný článek. K tomuto sloupci není přiřazen žádný index, což se podepisuje na výkonu aplikace.

Přidání databázového indexu

Ačkoli se na testovacích datech patrně neprojeví žádná změna, rád bych nastínil, jak by optimalizace probíhala na skutečných datech. V nástroji SQL Server Management Studio spustíme dotaz na výběr článku dle PrettyURL a necháme si vygenerovat Execution plan pomocí tlačítka Tlačítko pro vygenerování exekučního plánu:

SELECT
        c.Perex,
        c.PrettyURL,
        c.Keywords,
        c.Titulek,
        c.Obsah,
        c.Publikovano,
        u.Nick
FROM Clanek c, Uzivatel u
WHERE ((c.PrettyURL = 'co-je-to-algoritmus')
        AND (c.AutorID = u.UzivatelID));

Výsledek bude vypadat např. takto:

Exekuční plán v Microsoft SQL Management Studio

A jednotlivé parametry:

Parametry exekučního plánu v Microsoft SQL Management Studio

Následně vytvoříme index na sloupci PrettyURL:

CREATE INDEX idx_clanek_prettyurl ON Clanek (PrettyURL);

Opětovné vyvolání Execution plan by na ostré databázi, která by jistě měla několik desítek MB, jistě přineslo výrazné změny. Na testovacích datech se změna vůbec neprojeví, nicméně jsme si to alespoň vyzkoušeli :)


 

 

Článek pro vás napsal David Čápka
Avatar
Jak se ti líbí článek?
4 hlasů
Autor pracuje jako softwarový architekt a pedagog na projektu ITnetwork.cz (a jeho zahraničních verzích). Velmi si váží svobody podnikání v naší zemi a věří, že když se člověk neštítí práce, tak dokáže úplně cokoli.
Unicorn College Autor sítě se informační technologie naučil na Unicorn College - prestižní soukromé vysoké škole IT a ekonomie.
Aktivity (1)

 

 

Komentáře

Avatar
blazoid
Člen
Avatar
blazoid:6. ledna 18:56

Ahoj, tento dotaz je pro autora:
Jde mně o to, aby byla databáze přístupná z několika pracovišť. Jestliže si takto vytvořím MSSQL SERVER na nějakém stroji, dostanu se na její data z aplikace, která běží zase jen na tom stroji, na kterém běží SQL Server. Možná jsem zde na webu (a nebylo by to poprvé) něco přehlídl, nicméně, je v možnostech MSSQL SERVER Express instalace na nějaký stroj v síti, který bude pracovat jako server a na tento počítač se budou připojovat klienti s jinými stanicemi v síti, u kterých poběží aplikace, která bude prostřednictvím dotazů s touto databází pracovat, nebo je nutné mít nějakou vyšší a placenou verzi čehokoliv (OS, SQL SERVER,....).

Jedna věc je tvorba connection stringu pro připojení k databázi (tam se dají věci jako IP adresa serveru, user name, password a podobné implementovat), druhá věc je nastavení samotného SQL SERVERU, který musí na síti nějakým způsobem naslouchat a na požadavky autorizovaných tazatelů odpovídat. Děkuji mockrát za odpověď, případně za nasměrování na odpověď - někdy je totiž problém, že odpověď má člověk přímo před sebou, nepozná však z důvodu nízké znalosti problematiky, že se jedná o odpověď :-)

Editováno 6. ledna 18:58
 
Odpovědět 6. ledna 18:56
Avatar
Odpovědět 6. ledna 23:54
Nikdy neříkej nahlas, že to nejde. Vždycky se totiž najde blbec, který to neví a udělá to...
Avatar
blazoid
Člen
Avatar
Odpovídá na Michal Štěpánek
blazoid:7. ledna 20:29

Díky, přidávám pro případ že by se to někomu hodilo, něco názornějšího :-)

https://www.youtube.com/watch?…

 
Odpovědět 7. ledna 20:29
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 3 zpráv z 3.