dotNETportal.cz

Dobrý den,

mám dotaz ohledně výběru dat z MSSQL databáze.

Když si do své asp.net aplikace potřebuji natáhnout data z MS SQL databáze, tak se stránka načítá několik desítek vteřin. Když poté kliknu na znovuobnovení stránky, tak se data z databáze stáhnou za méně než jednu vteřinu při dalších opakováních opět jen méně než jednu vteřinu. Poté si dám chvíli pauzu, vrátím se k PC za několik desítek minut kliknu na znovuobnovení stránky a výběr dat zase trvá několik desítek vteřin.

Spojení se s databází přitom ve všech případech trvá jen několik milisekund.

Jedná se sice o poměrně složitý dotaz, nicméně pokud někdy trvá výběr dat méně než vteřinu a jindy desítky vteřin, tak asi dělá něco špatně SQL server, a nebo něco dělám špatně já. Níže zasílám příklad dotazu, který spouštím. Asi to nikomu nic neřekne, ale možná mi tam někde chybí nějaký parametr, a nebo něco přebývá ...

Používám MSSQL 2005

Předem děkuji za vaše nápady a připomínky.

Petr

WITH TB18915 AS ( SELECT ROW_NUMBER() OVER ( ORDER BY CONVERT(VARCHAR, T0.UcetniRok) + '/' + RIGHT('0' + CONVERT(VARCHAR, T0.UcetniMes), 2) DESC, SUM(T0.[PocetChybDop]) ASC, SUM(T0.[PocetChybCelk]) ASC, SUM(T0.[PocetChybSkl]) ASC, SUM(T0.[PocetChybAdm]) ASC, SUM(T0.[PocetChybKli]) ASC, SUM((T0.PocetPri + T0.PocetVyd + T0.PocetCD)) ASC ) AS CROW, CONVERT(VARCHAR, T0.UcetniRok) + '/' + RIGHT('0' + CONVERT(VARCHAR, T0.UcetniMes), 2) AS C19, SUM((T0.PocetPri + T0.PocetVyd + T0.PocetCD)) AS C17, SUM(T0.[PocetChybKli]) AS C13, SUM(T0.[PocetChybCelk]) AS C9, SUM(T0.[PocetChybDop]) AS C10, SUM(T0.[PocetChybSkl]) AS C11, SUM(T0.[PocetChybAdm]) AS C12, (CASE WHEN SUM(T0.PocetPri + T0.PocetVyd + T0.PocetCD) > 0 THEN ( CAST(SUM(T0.PocetChybKli) AS FLOAT) / CAST(SUM(T0.PocetPri + T0.PocetVyd + T0.PocetCD) AS FLOAT) * 100 ) ELSE 0 END) AS C15, (CASE WHEN SUM(T0.PocetPri + T0.PocetVyd + T0.PocetCD) > 0 THEN (CAST(SUM(T0.PocetChybDop + T0.PocetChybAdm + T0.PocetChybSkl + T0.PocetChybOst) AS FLOAT) / CAST(SUM(T0.PocetPri + T0.PocetVyd + T0.PocetCD) AS FLOAT) * 100) ELSE 0 END) AS C16, (CASE WHEN SUM(T0.PocetPri + T0.PocetVyd + T0.PocetCD) > 0 THEN (CAST(SUM(T0.PocetChybDop) AS FLOAT) / CAST(SUM(T0.PocetPri + T0.PocetVyd + T0.PocetCD) AS FLOAT) * 100) ELSE 0 END) AS C20, (CASE WHEN SUM(T0.PocetPri + T0.PocetVyd + T0.PocetCD) > 0 THEN (CAST(SUM(T0.PocetChybSkl) AS FLOAT) / CAST(SUM(T0.PocetPri + T0.PocetVyd + T0.PocetCD) AS FLOAT) * 100) ELSE 0 END) AS C21, (CASE WHEN SUM(T0.PocetPri + T0.PocetVyd + T0.PocetCD) > 0 THEN (CAST(SUM(T0.PocetChybAdm) AS FLOAT) / CAST(SUM(T0.PocetPri + T0.PocetVyd + T0.PocetCD) AS FLOAT) * 100) ELSE 0 END) AS C22 FROM ( SELECT [KlFakturM], [Aktivita], [Adresa], [DIC], [UcetniRok], [UcetniMes], [PocetObj], [PocetPri], [PocetVyd], [PocetCD], [PocetChybCelk], [PocetChybDop], [PocetChybSkl], [PocetChybAdm], [PocetChybKli], [PocetChybOst] FROM [AiwisChybovVyh2013] WITH ( NOLOCK ) UNION ALL SELECT [KlFakturM], [Aktivita], [Adresa], [DIC], [UcetniRok], [UcetniMes], [PocetObj], [PocetPri], [PocetVyd], [PocetCD], [PocetChybCelk], [PocetChybDop], [PocetChybSkl], [PocetChybAdm], [PocetChybKli], [PocetChybOst] FROM [AiwisChybovVyh2014] WITH ( NOLOCK ) UNION ALL SELECT [KlFakturM], [Aktivita], [Adresa], [DIC], [UcetniRok], [UcetniMes], [PocetObj], [PocetPri], [PocetVyd], [PocetCD], [PocetChybCelk], [PocetChybDop], [PocetChybSkl], [PocetChybAdm], [PocetChybKli], [PocetChybOst] FROM [AiwisChybovVyh2015] WITH ( NOLOCK ) ) AS T0 LEFT OUTER JOIN [CADADH201500001] AS T1 WITH ( NOLOCK ) ON ( T1.[KLIC1ADR] = T0.[Adresa] ) WHERE ( T0.[Aktivita] = 0 AND ( T0.[UcetniRok] <> 0 ) AND ( T0.[UcetniMes] <> 0 ) AND (CONVERT(VARCHAR, T0.UcetniMes) <> '') ) GROUP BY CONVERT(VARCHAR, T0.UcetniRok) + '/' + RIGHT('0' + CONVERT(VARCHAR, T0.UcetniMes), 2) ) SELECT * FROM TB18915 WHERE CROW BETWEEN 1 AND 15 ORDER BY CROW ASC OPTION ( MAXDOP 8 )

Jste si jistý, že skutečně trvá 10 vteřin ten dotaz? Tj. debugoval jste si to, nebo profiloval?

ASP .NET musí stránku zbuildit, než ji může použít, proto trvá první request vždy déle - záleží na hodně nastaveních,.. Po delší době ji například z paměti může zase dát pryč - pokud například dochází paměť, nebo se opravdu delší dobu nepoužívá. Je tedy například běžné u levnějších ASP hostingů, že poprvé to trvá klidně 10-15sec a potomm pod 1/4 vteřiny.

Pokud jste si jistý, že je to databází.. ta databáze si samozřejmě může výsledky / mezi výsledky cacheovat.. také si dělá statistiku, podle které poté ví (příště bude vědět), jak dotaz zpracovat efektivněji..

Nehledě na to, že data z DB jsou samozřejmě na disku, tak si je musí často také natáhnout (to by jste ale pocítit neměl)

Dobrý den, děkuji za vaší reakci.

Jsem si jistý, že problém je v databázi. Ve své asp.net aplikaci si sahám do databáze pomocí webové služby, a to až poté, co se stránka vrátí klientovi. Navíc jsem si nadefinoval měření času mezi jednotlivými kroky ( connection.open, command.executereader, connection.close ), takže vím jak dlouho trvá spojení s databází a jak dlouho samostatné zpracování dotazu.

asp.net aplikaci mám na svém serveru, stejně jako mssql databázi.

Takže to tedy znamená, že výsledky dotazu jsou uloženy po určitou dobu v cache na serveru a zpracování dotazu ve skutečnosti trvá desítky vteřin?

Dobrý den, děkuji za vyčerpávající odpověď.

Konkrétní příklad je výběrem dat ze čtyřech VIEW propojených pomocí UNION ALL a k nim zleva napojena jedna tabulka. Samotná VIEW jsou potom každé výběrem dat ze dvou tabulek. VIEW jsem vytvořil pomocí SQL management studia. Kompletní výsledek poté rozkouskovávám po 15ti záznamech pomocí ROW_NUMBER BETWEEN 1 AND 15. První dotaz trvá dlouho. Poté kdy přejdu na další stránku, což je ROW_NUMBER() BETWEEN 16 AND 30, to proběhne rychle, i když se vlastně jedná o jiná data, ale ze stejných tabulek. To by znamenalo, že v mezipaměti jsou tedy asi části tabulek.

Ještě doplním, že žádná z tabulek nemá více jak jeden milion řádků, spíše desítky až stovky tisíc.

Všechny tabulky v databázi mají nastaveny indexy a jednou týdně se provádí rebuild databáze. Jedná se o databázi informačního systému. Já z této databáze pouze tahám data do své webové aplikace, abych mohl data ze systému v uživatelsky přátelské formě zobrazovat klientům přes webové rozhraní.

Tento zmíněný dotaz má stejné výsledky a zpracovává se stejnou dobu i když ho zpracovávám přímo v SQL management studiu. Trochu začínám podezírat z této problematiky samotný informační systém, kterému databáze patří. Často poslední dobou dochází k vzájemné blokaci procesů z informačního systému.

Ohledně omezení na zpracování maximálně v osmi vláknech ... Toto teď jen testuji, ale podle mého názoru to nemá vliv absolutně na nic. Ať MAXDOP nastavím na hodnotu 0, 1, 4, 8 nebo MAXDOP vůbec do dotazu nedám, tak na výsledném čase zpracovávání dotazu se absolutně nic nemění. Přiznám se, že moc nerozumím k čemu to je dobré. Někde jsem se o tom na fórech dočetl, a tak jsem to zkusil, Dle diskutujících se mělo jednat o to, mezi kolik jader procesoru mohu zpracování dotazu rozložit.

Dokáže mi SQL management studio zkontrolovat, zda mu pokládám otimalizované dotazy, případně navrhnout optimalizaci?

Dobrý den,

pokud pozorujete postupné zpomalování systému a navzájem se blokující transakce, mohlo by to směřovat ke špatně navrženým indexům. Dokud bylo v DB málo dat, SQL to dal hrubou silou za použití clusterovaných indexů na primárních klíčích. Jak se tabulky zvětšují, hrubá síla přestává stačit. Pokud je to opravdu tohle, je otázka času než se stane celý systém špatně použitelným. Indexy je potřeba mít navržené správně na sloupcích, přes které se nejčastěji pokládají dotazy a to mnohdy není primární klíč. Naopak není možné mít indexy na všech sloupcích, zabírají místo a může se pak stát, že indexy zabírají více místa než samotná tabulka a také se zpomalují modifikace tabulek (insert, update, delete).

Jediná cesta je pustit si dotaz v management studiu se zobrazením exekučního plánu. (klávesová zkratka Ctrl+M) Tam bude vidět kde je zakopaný pes. Jak jsem psal výše, hledejte table scan, index scan.

K analýze chybějících indexů je k dispozici Database engine tunning advisor, ale s ním opatrně, jak jsem psal s oblibou navrhuje indexy přes všechny sloupce. Určitě ale dokáže dobře napovědět ve kterých tabulkách je něco špatně a jaké indexy a statistiky by tam měly být.

MAXDOP se používá k omezení počtu jader, které paralelně dotaz zpracovávají. Občas se využije třeba pro blokování rezervy jader na serveru pro ostatní dotazy, nebo může pomoci v okamžiku kdy se MSSQL snaží nějaký složitý dotaz provést paralelně, ale zvolí chybný exekuční plán, který trvá hodně dlouho, pak omezení na 1 jádro ho donutí udělat plán jiný. Je to případ od případu.

Dobrý den, děkuji za Vaší odpověď, ... no je to pravda ... UNION ALL, CASE, CONVERT, spojování řetězců, agregační funkce, ... toto všechno v dotazu je. Ani bych neočekával, že výsledky dostanu hned, jen mě překvapuje, že čas potřebný ke zpracování dotazu je při každém jeho položení jiný. Zvláště pak první položení dotazu po delší době se počítá na desítky vteřin až minutu, a při druhém a dalším položení dotazu jsou výsledky zpět v intervalu od 800 ms - 4000 ms, což je obrovský rozdíl.

Teď už tomu trochu rozumím. Budu se asi muset spojit s dodavatelem informačního systému, do jehož databáze si sahám, a tyto věci s ním prodiskutovat.

Díky, P.

Ještě jeden tip, pokud dotaz spouštíte v Management Studiu, abyste ověřil jak se samotný dlouho vykonává, je dobré před vlastní dotaz umístit následující příkazy pro zobrazení statistik a vyprázdnění veškerých cacheovaných dat.

SET STATISTICS TIME ON
SET STATISTICS IO ON

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS