Všetky matematické funkcie vrátia pri chybe hodnotu NULL.
Unárne mínus. Zmení znamienko argumentu: mysql> SELECT - 2; -> -2 Upozorňujeme, že ak sa tento operátor použije s údajmi typu BIGINT, návratová hodnota bude tiež typu BIGINT! To znamená, že by ste sa mali vyhnúť používaniu operátora na celých číslach, ktoré môžu mať hodnotu -2^63! ABS(X) Vráti absolútnu hodnotu X: mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32 Túto funkciu možno bezpečne použiť pre hodnoty BIGINT. SIGN(X) Vráti znamienko argumentu ako -1 , 0 alebo 1 v závislosti od toho, či je X záporné, nulové alebo kladné: mysql> SELECT SIGN(-32); -> -1 mysql> SELECT SIGN(0); -> 0 mysql> SELECT SIGN(234); -> 1 MOD(N,M) % Hodnota modulu (podobne ako operátor % v C). Vráti zvyšok N delený M: mysql> SELECT MOD(234, 10); -> 4 mysql> SELECT 253 % 7; -> 1 mysql> SELECT MOD(29,9); -> 2 Túto funkciu možno bezpečne použiť pre hodnoty BIGINT. FLOOR(X) Vráti najväčšie celé číslo nie väčšie ako X: mysql> SELECT FLOOR(1,23); -> 1 mysql> SELECT FLOOR(-1,23); -> -2 Všimnite si, že návratová hodnota sa prevedie na BIGINT! CEILING(X) Vráti najmenšie celé číslo nie menšie ako X: mysql> SELECT CEILING(1.23); -> 2 mysql> SELECT CEILING(-1.23); -> -1 Všimnite si, že návratová hodnota sa skonvertuje na BIGINT! ROUND(X) Vráti argument X zaokrúhlený na najbližšie celé číslo: mysql> SELECT ROUND(-1,23); -> -1 mysql> SELECT ROUND(-1,58); -> -2 mysql> SELECT ROUND(1,58); -> 2 Upozorňujeme, že správanie funkcie ROUND(), keď je hodnota argumentu stred medzi dvoma celými číslami, závisí od konkrétnej implementácie knižnice C. Zaokrúhľovanie je možné vykonať: na najbližšie párne číslo, vždy na najbližšie väčšie , vždy na najbližšie menej, vždy smerujte k nule. Aby ste zabezpečili, že zaokrúhľovanie bude vždy prebiehať iba v jednom smere, musíte namiesto toho použiť dobre definované funkcie ako TRUNCATE() alebo FLOOR(). ROUND(X,D) Vráti argument X zaokrúhlený na číslo s D desatinnými miestami. Ak je D 0 , výsledok sa zobrazí bez desatinnej čiarky alebo zlomkovej časti: mysql> SELECT ROUND(1,298, 1); -> 1,3 mysql> SELECT ROUND(1,298; 0); -> 1 EXP(X) Vráti hodnotu e (základ prirodzených logaritmov) umocnenú na X: mysql> SELECT EXP(2); -> 7,389056 mysql> SELECT EXP(-2); -> 0,135335 LOG(X) Vráti prirodzený logaritmus X: mysql> SELECT LOG(2); -> 0. 693147 mysql> SELECT LOG(-2); -> NULL Na získanie logaritmu čísla X pre ľubovoľný logaritmický základ B použite vzorec LOG(X)/LOG(B) . LOG10(X) Vráti základný 10 logaritmus X: mysql> SELECT LOG10(2); -> 0,301030 mysql> SELECT LOG10(100); -> 2.000000 mysql> SELECT LOG10(-100); -> NULL POW(X,Y) POWER(X,Y) Vráti hodnotu argumentu X umocnenú na Y: mysql> SELECT POW(2,2); -> 4,000000 mysql> SELECT POW(2,-2); -> 0,250000 SQRT(X) Vráti nezápornú druhú odmocninu X: mysql> SELECT SQRT(4); -> 2,000000 mysql> SELECT SQRT(20); -> 4,472136 PI() Vráti hodnotu "pi". Predvolená hodnota je 5 desatinných miest, ale MySQL interne používa úplnú dvojitú presnosť na vyjadrenie pi. mysql> SELECT PI(); -> 3,141593 mysql> SELECT PI()+0,00000000000000000; -> 3,141592653589793116 COS(X) Vráti kosínus X , kde X je v radiánoch: mysql> SELECT COS(PI()); -> -1,000000 SIN(X) Vráti sínus X , kde X je v radiánoch: mysql> SELECT SIN(PI()); -> 0,000000 TAN(X) Vráti tangens X , kde X je v radiánoch: mysql> SELECT TAN(PI()+1); -> 1,557408 ACOS(X) Vráti úhlový kosínus X , t.j. hodnota, ktorej kosínus sa rovná X . Vráti NULL, ak X nie je medzi -1 a 1: mysql> SELECT ACOS(1); -> 0,000000 mysql> SELECT ACOS(1,0001); -> NULL mysql> SELECT ACOS(0); -> 1,570796 ASIN(X) Vráti arkussínus X , t.j. hodnota, ktorej sínus sa rovná X. Ak X nie je v rozsahu -1 až 1, vráti NULL: mysql> SELECT ASIN(0.2); -> 0,201358 mysql> SELECT ASIN("foo"); -> 0,000000 ATAN(X) Vráti arkus tangens X , t.j. hodnota, ktorej dotyčnica je X: mysql> SELECT ATAN(2); -> 1,107149 mysql> SELECT ATAN(-2); -> -1,107149 ATAN(Y,X) ATAN2(Y,X) Vráti arkus tangens dvoch premenných X a Y . Výpočet je rovnaký ako pri výpočte arkustangensu Y / X , okrem toho, že na určenie kvadrantu výsledku sa použijú znamienka oboch argumentov: mysql> SELECT ATAN(-2,2); -> -0,785398 mysql> SELECT ATAN2(PI(),0); -> 1,570796 COT(X) Vráti kotangens X: mysql> SELECT COT(12); -> -1,57267341 mysql> SELECT COT(0); -> NULL RAND() RAND(N) Vráti náhodnú hodnotu s pohyblivou rádovou čiarkou medzi 0 a 1,0. Ak je zadaný celočíselný argument N, potom sa použije ako počiatočná hodnota tejto hodnoty: mysql> SELECT RAND(); -> 0. 9233482386203 mysql> SELECT RAND(20); -> 0,15888261251047 mysql> SELECT RAND(20); -> 0,15888261251047 mysql> SELECT RAND(); -> 0,63553050033332 mysql> SELECT RAND(); -> 0,70100469486881 Vo výrazoch ORDER BY by ste nemali používať stĺpec s hodnotami RAND(), pretože použitie operátora ORDER BY bude mať za následok viacero hodnotení v tomto stĺpci. Vo verzii MySQL 3.23 však môžete zadať nasledujúci príkaz: SELECT * FROM názov_tabulky ORDER BY RAND() : je to užitočné na získanie náhodnej inštancie z množiny SELECT * FROM tabuľka1, tabuľka2 WHERE a=b AND c
Tento článok poskytuje riešenie optimalizácie Transact SQL pre problém výpočtu zostatkov zásob. Použité: Rozdeľovacie tabuľky a materializované pohľady.
Formulácia problému
Problém je potrebné vyriešiť pre SQL Server 2014 Enterprise Edition (x64). Spoločnosť má veľa skladov. Každý sklad má každý deň niekoľko tisíc zásielok a príjmov produktov. V sklade je tabuľka pohybu tovaru príjem/výdaj. Je potrebné implementovať:Výpočet zostatku pre zvolený dátum a čas (do hodiny) pre všetky/akékoľvek sklady pre každý produkt. Pre analytiku je potrebné vytvoriť objekt (funkciu, tabuľku, pohľad), pomocou ktorého sa pre zvolený rozsah dátumov zobrazia údaje zdrojovej tabuľky pre všetky sklady a produkty a doplnkový výpočtový stĺpec - zostatok v pozícia sklad.
Očakáva sa, že tieto výpočty budú prebiehať podľa plánu s rôznymi rozsahmi dátumov a mali by sa spustiť v primeranom čase. Tie. ak je potrebné zobraziť tabuľku so zostatkami za poslednú hodinu alebo deň, tak čas vykonania by mal byť čo najrýchlejší, ako aj ak je potrebné zobraziť rovnaké údaje za posledné 3 roky, pre následné načítanie do analytická databáza.
Technické detaily. Samotný stôl:
Vytvorte tabuľku dbo.Turnover (primárny kľúč id int identity, dt datetime not null, ProductID int not null, StorehouseID int not null, Operation smallint not null check (Operation in (-1,1)), -- +1 sklad check , -1 cena zo skladu Množstvo numerické (20,2) nie je nulové, peňažné náklady nie sú nulové)
Dt - Dátum a čas prijatia/odpisu na/zo skladu.
ProductID - Produkt
StorehouseID - sklad
Prevádzka - 2 hodnoty prichádzajúce alebo odchádzajúce
Množstvo - množstvo produktu na sklade. Môže byť skutočný, ak výrobok nie je v kusoch, ale napríklad v kilogramoch.
Náklady - náklady na dávku produktu.
Problémový výskum
Vytvoríme hotovú tabuľku. Aby ste so mnou mohli testovať a vidieť výsledky, navrhujem vytvoriť a vyplniť tabuľku dbo.Turnover skriptom:Ak object_id("dbo.Turnover","U") nie je null drop table dbo.Turnover; ísť s časmi ako (vybrať 1 spojenie id všetko vybrať id+1 z časov, kde id< 10*365*24*60 -- 10 лет * 365 дней * 24 часа * 60 минут = столько минут в 10 лет)
, storehouse as
(select 1 id
union all
select id+1
from storehouse
where id < 100 -- количество складов)
select
identity(int,1,1) id,
dateadd(minute, t.id, convert(datetime,"20060101",120)) dt,
1+abs(convert(int,convert(binary(4),newid()))%1000) ProductID, -- 1000 - количество разных продуктов
s.id StorehouseID,
case when abs(convert(int,convert(binary(4),newid()))%3) in (0,1) then 1 else -1 end Operation, -- какой то приход и расход, из случайных сделаем из 3х вариантов 2 приход 1 расход
1+abs(convert(int,convert(binary(4),newid()))%100) Quantity
into dbo.Turnover
from times t cross join storehouse s
option(maxrecursion 0);
go
--- 15 min
alter table dbo.Turnover alter column id int not null
go
alter table dbo.Turnover add constraint pk_turnover primary key (id) with(data_compression=page)
go
-- 6 min
Mám tento skript na PC s SSD disk spustenie trvalo asi 22 minút a veľkosť tabuľky zaberala asi 8 GB miesta na pevnom disku. Môžete znížiť počet rokov a počet skladov, aby ste skrátili čas potrebný na vytvorenie a vyplnenie tabuľky. Odporúčam však nechať nejakú dobrú sumu na vyhodnotenie plánov dotazov, aspoň 1-2 gigabajty.
Zoskupovanie údajov do jednej hodiny
Ďalej musíme zoskupiť množstvá podľa produktov na sklade za študované časové obdobie, v našej formulácii problému je to jedna hodina (možná je až minúta, až 15 minút, deň. Samozrejme, až na milisekúnd, je nepravdepodobné, že niekto bude potrebovať hlásenie). Pre porovnanie v relácii (okne), kde vykonávame naše dotazy, vykonáme príkaz - set statistics time on;. Ďalej vykonáme samotné dotazy a pozrieme sa na plány dotazov:
Vyberte top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) ako dt, -- zaokrúhliť na hodinu ProductID, StorehouseID, sum(Operation*Quantity) ako Množstvo od dbo .Turnover group by convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID
Cena žiadosti - 12406
(spracované riadky: 1 000)
Prevádzková doba servera SQL:
CPU čas = 2096594 ms, uplynutý čas = 321797 ms.
Ak vytvoríme výsledný dotaz so zostatkom, ktorý sa považuje za priebežný súčet nášho počtu, potom bude plán dotazov a dotazov vyzerať takto:
Vyberte top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) ako dt, -- zaokrúhliť na hodinu ProductID, StorehouseID, sum(Operation*Quantity) ako Množstvo, súčet (sum(Operácia*Množstvo)) nad (rozdelenie podľa StorehouseID, ProductID poradie podľa konverzie(datetime,convert(varchar(13),dt,120)+":00",120)) ako zostatok z dbo.Turnover group by convert (datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID
Cena žiadosti - 19329
(spracované riadky: 1 000)
Prevádzková doba servera SQL:
CPU čas = 2413155 ms, uplynutý čas = 344631 ms.
Optimalizácia zoskupovania
Všetko je tu celkom jednoduché. Samotný dotaz bez spusteného súčtu je možné optimalizovať pomocou materializovaného zobrazenia (indexové zobrazenie). Na vybudovanie zhmotneného pohľadu by to, čo je zhrnuté, nemalo mať nulovú hodnotu, spočítame (Operácia*Množstvo) alebo urobíme, aby každé pole NIE JE NULL, alebo k výrazu pridáme isnull/splynutie. Navrhujem vytvoriť zhmotnený pohľad.
Vytvorte zobrazenie dbo.TurnoverHour so schemabinding ako select convert(datetime,convert(varchar(13),dt,120)+":00",120) as dt, -- zaokrúhliť na hodinu ProductID, StorehouseID, sum(isnull(Operation*) Quantity,0)) as Quantity, count_big(*) qty from dbo.Turnover group by convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID go
A vytvorte na ňom klastrovaný index. V indexe uvádzame poradie polí rovnako ako v zoskupení (pre zoskupenie nie je dôležité poradie, dôležité je, aby boli v indexe všetky polia zoskupenia) a kumulatívny súčet (poradie je tu dôležité - najprv to, čo je v oddiele podľa, potom to, čo je v poradí):
Vytvorte jedinečný klastrovaný index uix_TurnoverHour na dbo.TurnoverHour (StorehouseID, ProductID, dt) s (data_compression=page) – 19 min
Teraz, po vytvorení klastrovaného indexu, môžeme znova spustiť dotazy zmenou agregácie súčtu ako v zobrazení:
Vyberte top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) ako dt, -- zaokrúhliť na hodinu ProductID, StorehouseID, sum(isnull(Operation*Quantity,0) ) ako Množstvo z dbo.Turnover group by convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID select top(1000) convert(datetime,convert(varchar(13) ),dt,120)+":00",120) ako dt, -- zaokrúhliť na hodinu ProductID, StorehouseID, sum(isnull(Operácia*Množstvo,0)) ako Množstvo, sum(sum(isnull(Operácia*Množstvo) , 0))) cez (rozdelenie podľa StorehouseID, poradie ProductID podľa convert(datetime,convert(varchar(13),dt,120)+":00",120)) ako zostatok z dbo.Turnover group by convert(datetime, konvertovať (varchar(13),dt,120)+":00",120), ProductID, StorehouseID
Plány dopytov sa stali:
Cena 0,008
Cena 0,01
Prevádzková doba servera SQL:
CPU čas = 31 ms, uplynutý čas = 116 ms.
(spracované riadky: 1 000)
Prevádzková doba servera SQL:
CPU čas = 0 ms, uplynutý čas = 151 ms.
Celkovo vidíme, že pri indexovanom zobrazení dotaz neskenuje údaje zoskupenia tabuľky, ale zoskupený index, v ktorom je už všetko zoskupené. A podľa toho sa čas vykonania skrátil z 321797 milisekúnd na 116 ms, t.j. 2774 krát.
Tým by naša optimalizácia mohla skončiť, nebyť toho, že často potrebujeme nie celú tabuľku (view), ale jej časť pre vybraný rozsah.
Priebežné zostatky
V dôsledku toho musíme rýchlo vykonať nasledujúci dotaz:
Nastaviť formát dátumu ymd; deklarovať @start datetime = "2015-01-02", @finish datetime = "2015-01-03" vyberte * z (vyberte dt, StorehouseID, ProductId, Quantity, sum(Quantity) over (partition by StorehouseID, ProductID order by dt) ako zostatok z dbo.Hodina obratu s(noexpand) kde dt<= @finish) as tmp
where dt >= @štart
Plánované náklady = 3103. A predstavte si, čo by sa stalo, keby nešlo o zhmotnený pohľad, ale o samotný stôl.
Výstup materializovaného zobrazenia a údajov o stave pre každý produkt na sklade k dátumu s časom zaokrúhleným na najbližšiu hodinu. Na výpočet zostatku je potrebné od samého začiatku (od nulového zostatku) sčítať všetky množstvá až do určeného posledného dátumu (@finish) a potom v súbore súčtových výsledkov údaje orezať po štartovacom parametri.
Tu samozrejme pomôžu priebežné vypočítané zostatky. Napríklad 1. dňa v mesiaci alebo každú nedeľu. S takýmito zostatkami sa úloha redukuje na skutočnosť, že bude potrebné sčítať predtým vypočítané zostatky a vypočítať zostatok nie od začiatku, ale od posledného vypočítaného dátumu. Pre experimenty a porovnania vytvoríme ďalší nezhlukovaný index podľa dátumu:
Vytvorte index ix_dt na dbo.Hodina obratu (dt) include (Množstvo) with(data_compression=page); --7 min A naša požiadavka bude vyzerať takto: set dateformat ymd; deklarovať @start datetime = "2015-01-02", @finish datetime = "2015-01-03" deklarovať @start_month datetime = convert(datetime,convert(varchar(9),@start,120)+"1", 120) vyberte * z (vyberte dt, ID skladu, ID produktu, množstvo, súčet (množstvo) nad (rozdelenie podľa ID skladu, ID produktu poradie podľa dt) ako Zostatok z dbo.TurnoverHour with(noexpand) kde dt medzi @start_month a @finish) ako tmp, kde dt >
Vo všeobecnosti tento dotaz, aj keď má index podľa dátumu, ktorý úplne pokrýva všetky polia ovplyvnené dotazom, vyberie náš zoskupený index a prehľadá. Namiesto vyhľadávania podľa dátumu s následným triedením. Navrhujem vykonať nasledujúce 2 otázky a porovnať, čo sme dostali, potom budeme analyzovať, čo je ešte lepšie:
Nastaviť formát dátumu ymd; deklarovať @start datetime = "2015-01-02", @finish datetime = "2015-01-03" deklarovať @start_month datetime = convert(datetime,convert(varchar(9),@start,120)+"1", 120) vyberte * z (vyberte dt, ID skladu, ID produktu, množstvo, súčet (množstvo) nad (rozdelenie podľa ID skladu, ID produktu poradie podľa dt) ako Zostatok z dbo.TurnoverHour with(noexpand) kde dt medzi @start_month a @finish) ako tmp kde dt >= @začiatok objednávky podľa StorehouseID, ProductID, dt select * from (vyberte dt, StorehouseID, ProductId, Quantity, sum(Quantity) over (partition by StorehouseID, ProductID order by dt) as Balance from dbo.TurnoverHour with( noexpand,index=ix_dt) kde dt medzi @start_month a @finish) ako tmp kde dt >= @start order by StorehouseID, ProductID, dt
Prevádzková doba servera SQL:
CPU čas = 33860 ms, uplynutý čas = 24247 ms.(spracované riadky: 145608)
(spracované riadky: 1)
Prevádzková doba servera SQL:
CPU čas = 6374 ms, uplynutý čas = 1718 ms.
CPU čas = 0 ms, uplynutý čas = 0 ms.
Z času je zrejmé, že indexovanie podľa dátumu je oveľa rýchlejšie. Ale plány dopytov v porovnaní vyzerajú takto:
Cena 1. požiadavky s automaticky vybraným zoskupeným indexom = 2752, ale cena s indexom k dátumu požiadavky = 3119.
V každom prípade tu potrebujeme dve úlohy z indexu: triedenie a výber rozsahu. Tento problém sa nedá vyriešiť jedným z indexov, ktoré máme k dispozícii. IN tento príklad rozsah údajov je len 1 deň, ale ak je tam dlhšie obdobie, ale nie všetky, napríklad 2 mesiace, tak vyhľadávanie podľa indexu určite nebude efektívne kvôli nákladom na triedenie.
Tu z viditeľných optimálnych riešení vidím:
- Vytvorte vypočítané pole Rok-Mesiac a vytvorte index (Rok-Mesiac, ostatné polia zoskupeného indexu). V časti kde dt medzi @začiatok_mesiac a konečný stav nahraďte výrazom Rok-Mesiac [e-mail chránený] mesiac a potom použite filter na požadované dátumy.
- Filtrované indexy – samotný index je ako zhlukovaný, ale filter je podľa dátumu pre požadovaný mesiac. A urobiť toľko takýchto indexov, koľko máme mesiacov celkovo. Myšlienka je blízko k riešeniu, ale ak je rozsah podmienok z 2 filtrovaných indexov, bude potrebné spojenie a ďalšie triedenie je aj tak nevyhnutné.
- Rozdeľte klastrovaný index tak, aby každý oddiel obsahoval iba jeden mesiac údajov.
Rozdeľte index zoskupeného zobrazenia. Najprv odstráňte všetky indexy zo zobrazenia:
Pokles indexu ix_dt na dbo.TurnoverHour; pokles indexu uix_TurnoverHour na dbo.TurnoverHour;
A vytvorte funkciu a schému rozdelenia:
Nastaviť formát dátumu ymd; vytvorte funkciu oddielu pf_TurnoverHour(datetime) ako rozsah vpravo pre hodnoty ("2006-01-01", "2006-02-01", "2006-03-01", "2006-04-01", "2006- 05-01", "2006-06-01", "2006-07-01", "2006-08-01", "2006-09-01", "2006-10-01", "2006-11- 01", "2006-12-01", "2007-01-01", "2007-02-01", "2007-03-01", "2007-04-01", "2007-05-01" , " 2007-06-01", "2007-07-01", "2007-08-01", "2007-09-01", "2007-10-01", "2007-11-01", " 2007-12-01", "2008-01-01", "2008-02-01", "2008-03-01", "2008-04-01", "2008-05-01", "2008- 06-01", "2008-07-01", "2008-08-01", "2008-09-01", "2008-10-01", "2008-11-01", "2008-12- 01", "2009-01-01", "2009-02-01", "2009-03-01", "2009-04-01", "2009-05-01", "2009-06-01" , " 2009-07-01", "2009-08-01", "2009-09-01", "2009-10-01", "2009-11-01", "2009-12-01", " 2010-01-01", "2010-02-01", "2010-03-01", "2010-04-01", "2010-05-01", "2010-06-01", "2010- 07-01", "2010-08-01", "2010-09-01", "2010-10-01", "2010-11-01", "2010-12-01", "2011-01- 01", "2011-02-01", "2011-03-01", "2011-04-01", "2011-05-01", "2011-06-01" ", "2011-07-01", "2011-08-01", "2011-09-01", "2011-10-01", "2011-11-01", "2011-12-01", "2012-01-01", "2012-02-01", "2012-03-01", "2012-04-01", "2012-05-01", "2012-06-01", "2012 -07-01", "2012-08-01", "2012-09-01", "2012-10-01", "2012-11-01", "2012-12-01", "2013-01 -01", "2013-02-01", "2013-03-01", "2013-04-01", "2013-05-01", "2013-06-01", "2013-07-01" ", "2013-08-01", "2013-09-01", "2013-10-01", "2013-11-01", "2013-12-01", "2014-01-01", "2014-02-01", "2014-03-01", "2014-04-01", "2014-05-01", "2014-06-01", "2014-07-01", "2014 -08-01", "2014-09-01", "2014-10-01", "2014-11-01", "2014-12-01", "2015-01-01", "2015-02 -01", "2015-03-01", "2015-04-01", "2015-05-01", "2015-06-01", "2015-07-01", "2015-08-01" "", "2015-09-01", "2015-10-01", "2015-11-01", "2015-12-01", "2016-01-01", "2016-02-01" , "2016-03-01", "2016-04-01", "2016-05-01", "2016-06-01", "2016-07-01", "2016-08-01", " 2016 -09-01", "2016-10-01", "2016-11-01", "2016-12-01", "2017-01-01", "2017-02-01", "2017- 03-01", "2017-04-01", "2017-05-01", "20 17-06-01", "2017-07-01", "2017-08-01", "2017-09-01", "2017-10-01", "2017-11-01", "2017- 12-01", "2018-01-01", "2018-02-01", "2018-03-01", "2018-04-01", "2018-05-01", "2018-06- 01", "2018-07-01", "2018-08-01", "2018-09-01", "2018-10-01", "2018-11-01", "2018-12-01" , "2019-01-01", "2019-02-01", "2019-03-01", "2019-04-01", "2019-05-01", "2019-06-01", " 2019-07-01", "2019-08-01", "2019-09-01", "2019-10-01", "2019-11-01", "2019-12-01"); choď vytvoriť schému oddielov ps_TurnoverHour ako oddiel pf_TurnoverHour all to (); go No a klastrovaný index nám už známy iba vo vytvorenej schéme rozdelenia: vytvorte jedinečný klastrovaný index uix_TurnoverHour na dbo. TurnoverHour (StorehouseID, ProductID, dt) s (data_compression=page) na ps_TurnoverHour(dt); --- 19 min A teraz sa pozrime, čo máme. Samotná požiadavka: set dateformat ymd; deklarovať @start datetime = "2015-01-02", @finish datetime = "2015-01-03" deklarovať @start_month datetime = convert(datetime,convert(varchar(9),@start,120)+"1", 120) vyberte * z (vyberte dt, ID skladu, ID produktu, množstvo, súčet (množstvo) nad (rozdelenie podľa ID skladu, ID produktu poradie podľa dt) ako Zostatok z dbo.TurnoverHour with(noexpand) kde dt medzi @start_month a @finish) ako tmp kde dt >= @začiatok objednávky podľa StorehouseID, ProductID, dt option(recompile);
Prevádzková doba servera SQL:
CPU čas = 7860 ms, uplynutý čas = 1725 ms.
Čas analýzy a kompilácie servera SQL Server:
CPU čas = 0 ms, uplynutý čas = 0 ms.
Náklady na plán dopytov = 9,4
V skutočnosti sú údaje v jednom oddiele vyberané a skenované klastrovaným indexom pomerne rýchlo. Tu treba dodať, že pri parametrizácii požiadavky dochádza k nepríjemnému efektu sniffovania parametrov, je ošetrená voľba (recompile).
Toto je ďalší bežný problém. Základným princípom je akumulácia hodnôt jedného atribútu (agregátneho prvku) na základe zoradenia podľa iného atribútu alebo atribútov (prvku usporiadania), prípadne s riadkovými sekciami definovanými na základe ďalšieho atribútu alebo atribútov (prvok rozdelenia). V reálnom živote existuje veľa príkladov výpočtu priebežných súčtov, ako je napríklad výpočet bankových zostatkov, sledovanie toho, či sú položky na sklade alebo aktuálne čísla predaja atď.
Pred SQL Serverom 2012 boli riešenia založené na množinách používané na výpočet priebežných súčtov mimoriadne náročné na zdroje. Preto sa ľudia zvyčajne uchýlili k iteračným riešeniam, ktoré boli pomalé, no v niektorých situáciách stále rýchlejšie ako riešenia založené na množinách. S vylepšenou podporou funkcie okna v SQL Server 2012 je možné vypočítať priebežné súčty pomocou jednoduchého kódu založeného na množine, ktorý funguje oveľa lepšie ako staršie riešenia založené na Na báze T-SQL- množinové aj iteračné. Mohol by som ukázať nové riešenie a prejsť na ďalšiu časť; ale aby ste skutočne pochopili rozsah zmeny, opíšem staré spôsoby a porovnám ich výkonnosť s novým prístupom. Prirodzene, máte právo čítať len prvú časť, ktorá popisuje nový prístup a preskočte zvyšok článku.
Na demonštráciu rôznych riešení použijem zostatky na účtoch. Tu je kód, ktorý vytvorí a naplní tabuľku transakcií malým množstvom testovacích údajov:
SET NOCOUNT ON; POUŽÍVAŤ TSQL2012; IF OBJECT_ID("dbo.Transactions", "U") NIE JE NULL DROP TABLE dbo.Transactions; CREATE TABLE dbo.Transactions (actid INT NOT NULL, -- deliaci stĺpec tranid INT NOT NULL, -- objednávkový stĺpec val MONEY NOT NULL, -- merať OBMEDZENIE PK_Transactions PRIMARY KEY(actid, tranid)); GO -- malý testovací prípad INSERT INTO dbo.Transactions(actid, tranid, val) VALUES (1, 1, 4.00), (1, 2, -2.00), (1, 3, 5.00), (1, 4, 2.00) ), (1, 5, 1,00), (1, 6, 3,00), (1, 7, -4,00), (1, 8, -1,00), (1, 9, -2,00), (1, 10, -3,00), (2, 1, 2,00), (2, 2, 1,00), (2, 3, 5,00), (2, 4, 1,00), (2, 5, -5,00), (2, 6, 4,00), (2, 7, 2,00), (2, 8, -4,00), (2, 9, -5,00), (2, 10, 4,00), (3, 1, -3,00), (3, 2 , 3,00), (3, 3, -2,00), (3, 4, 1,00), (3, 5, 4,00), (3, 6, -1,00), (3, 7, 5,00), (3, 8 3,00), (3, 9, 5,00), (3, 10, -3,00);
Každý riadok tabuľky predstavuje bankovú transakciu na účte. Vklady sú označené ako transakcie s kladnou hodnotou v stĺpci val a výbery sú označené ako záporná hodnota transakcie. Našou úlohou je vypočítať zostatok na účte v každom okamihu akumuláciou súčtu transakcií v riadku val, zoradených podľa stĺpca tranid, a to je potrebné urobiť pre každý účet samostatne. Požadovaný výsledok by mal vyzerať takto:
Obe riešenia vyžadujú na testovanie viac údajov. Dá sa to urobiť pomocou dopytu, ako je tento:
DECLARE @num_partitions AS INT = 10, @rows_per_partition AS INT = 10000; TRUNCATE TABLE dbo.Transakcie; INSERT INTO dbo.Transakcie S (TABLOCK) (aktid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM() NEWID())%5)) FROM dbo.GetNums(1, @počet_oddielov) AS NP CROSS JOIN dbo.GetNums(1, @riadky_na_oddiel) AS RPP;
Svoje vstupy môžete nastaviť tak, aby zmenili počet oddielov (účtov) a riadkov (transakcií) v oddiele.
Set-Based riešenie pomocou funkcií okna
Začnem riešením založeným na množine, ktoré využíva funkciu okna agregácie SUM. Definícia okna je tu celkom jasná: musíte rozdeliť okno podľa actid, zoradiť podľa tranidu a filtrovať riadky v rámci od najnižšieho (NEBRANENÉ PREDCHÁDZAJÚCE) po aktuálny. Tu je príslušná žiadosť:
VYBERTE actid, tranid, val, SUM(val) NAD(PARTITION BY actid ORDER PODĽA tranidových RADOV MEDZI NEOZAMKNUTÝMI PREDCHÁDZAJÚCIMI A AKTUÁLNYMI RIADKAMI) AKO zostatok OD dbo.Transakcie;
Tento kód je nielen jednoduchý a priamočiary, ale aj rýchlo beží. Plán pre tento dotaz je znázornený na obrázku:
Tabuľka má klastrovaný index, ktorý je kompatibilný s POC a použiteľný funkciami okna. Konkrétne je zoznam kľúčov indexu založený na rozdeľovacom prvku (actid), po ktorom nasleduje prvok usporiadania (tranid), a na zabezpečenie pokrytia index zahŕňa všetky ostatné stĺpce v dotaze (val). Plán obsahuje usporiadané vyhľadávanie, po ktorom nasleduje interný výpočet čísla riadkov a potom súhrn okien. Keďže existuje index POC, optimalizátor nemusí do plánu pridať operátor triedenia. Toto je veľmi efektívny plán. Navyše sa lineárne škáluje. Neskôr, keď ukážem výsledky porovnania výkonu, uvidíte, o koľko je táto metóda efektívnejšia v porovnaní so starými riešeniami.
Pred SQL Serverom 2012 sa používali buď poddotazy alebo spojenia. Pri použití vnoreného dotazu sa priebežné súčty vypočítavajú filtrovaním všetkých riadkov s rovnakou hodnotou actid ako vonkajší riadok a tranidovou hodnotou menšou alebo rovnou hodnote vo vonkajšom riadku. Agregácia sa potom použije na filtrované riadky. Tu je príslušná žiadosť:
Podobný prístup je možné realizovať pomocou pripojení. Používa sa rovnaký predikát ako v klauzule WHERE poddotazu v klauzule ON spojenia. V tomto prípade pre N-tú transakciu toho istého účtu A v inštancii označenej ako T1 nájdete N zhodných v inštancii T2 s číslami transakcií od 1 do N. V dôsledku párovania sa riadky v T1 sa opakujú, takže potrebujete zoskupiť riadky podľa všetkých prvkov z T1, aby ste získali informácie o aktuálnej transakcii a aplikovali agregáciu na atribút val z T2 na výpočet priebežného súčtu. Vyplnená žiadosť vyzerá takto:
VYBERTE T1.aktid, T1.tranid, T1.val, SUM(T2.val) AS zostatok FROM dbo.Transakcie AS T1 PRIPOJTE dbo.Transakcie AS T2 ON T2.aktid = T1.aktid AND T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;
Na obrázku nižšie sú znázornené plány oboch riešení:
Všimnite si, že v oboch prípadoch sa na inštancii T1 vykoná úplné skenovanie klastrovaného indexu. Potom pre každý riadok v pláne existuje operácia vyhľadávania v indexe začiatku sekcie bežného účtu na poslednej strane indexu, pričom sa načítajú všetky transakcie, v ktorých je T2.tranid menší alebo rovný T1.tranid. . Miesto, kde dochádza k agregácii riadkov, je v plánoch mierne odlišné, ale počet prečítaných riadkov je rovnaký.
Aby ste pochopili, koľko riadkov sa zobrazuje, je potrebné vziať do úvahy počet dátových prvkov. Nech p je počet oddielov (účtov) a r je počet riadkov v oddiele (transakcia). Potom sa počet riadkov v tabuľke približne rovná p * r, ak predpokladáme, že transakcie sú rovnomerne rozdelené medzi účty. Skenovanie v hornej časti teda pokrýva p*r riadkov. Najviac nás však zaujíma, čo sa deje v iterátore Nested Loops.
V každej sekcii plán predpokladá čítanie 1 + 2 + ... + r riadkov, čo je celkovo (r + r*2) / 2. Celkový počet riadkov spracovaných v plánoch je p*r + p* (r + r2) / 2. To znamená, že počet operácií v pláne rastie na druhú s veľkosťou sekcie, to znamená, že ak sa veľkosť sekcie zväčší o f-krát, množstvo práce sa zvýši približne o f 2 krát. Je to zlé. Napríklad 100 riadkov zodpovedá 10 tisícom riadkov a tisíc riadkov zodpovedá miliónu atď. Jednoducho povedané, toto vedie k silnému spomaleniu vykonávania dotazu s pomerne veľkou veľkosťou sekcie, pretože kvadratická funkcia rastie veľmi rýchlo. Takéto riešenia fungujú uspokojivo pre niekoľko desiatok riadkov na sekciu, ale nie viac.
Kurzorové riešenia
Riešenia založené na kurzore sú implementované priamo. Kurzor je deklarovaný na základe dotazu, ktorý zoraďuje údaje podľa actid a tranid. Potom sa vykoná iteračný prechod záznamov kurzora. Keď sa nájde nový počet, premenná obsahujúca súhrn sa vynuluje. V každej iterácii sa do premennej pridá suma novej transakcie, po ktorej sa riadok uloží do premennej tabuľky s informáciami o aktuálnej transakcii plus aktuálna hodnota priebežného súčtu. Po prechode iterácie sa vráti výsledok z premennej tabuľky. Tu je kód dokončeného riešenia:
DECLARE @Result AS TABLE (aktid INT, tranid INT, val MONEY, zostatok MONEY); VYHLÁSIŤ @actid AKO INT, @prvactid AKO INT, @tranid AKO INT, @val AKO PENIAZE, @zostatok AKO PENIAZE; DECLARE C CURSOR FAST_FORWARD FOR SELECT actid, tranid, val FROM dbo.Transakcie ORDER BY actid, tranid; OTVORIŤ C VYŤAŽENIE ĎALEJ Z C DO @actid, @tranid, @val; SELECT @prvaktid = @aktid, @zostatok = 0; WHILE @@fetch_status = 0 ZAČIATOK AK @actid<>@prvaktid SELECT @prvaktid = @aktid, @zostatok = 0; SET @zostatok = @zostatok + @val; INSERT INTO @Result VALUES(@actid, @tranid, @val, @balance); NAVEDENIE ĎALEJ Z C DO @actid, @tranid, @val; KONIEC ZATVORENIE C; DEALOCATE C; SELECT * FROM @Result;
Plán dotazov pomocou kurzora je znázornený na obrázku:
Tento plán sa lineárne mení, pretože údaje z indexu sa skenujú iba raz v určitom poradí. Každá operácia získania riadka z kurzora má tiež približne rovnaké náklady na riadok. Ak vezmeme zaťaženie vytvorené spracovaním jedného riadku kurzora rovné g, náklady na toto riešenie možno odhadnúť ako p*r + p*r*g (pamätajte, p je počet sekcií a r je počet riadkov v sekcii). Ak teda zvýšite počet riadkov na partíciu f-krát, zaťaženie systému bude p*r*f + p*r*f*g, to znamená, že bude rásť lineárne. Náklady na spracovanie na riadok sú vysoké, ale vzhľadom na lineárny charakter škálovania sa toto riešenie od určitej veľkosti oddielu bude škálovať lepšie ako riešenia založené na vnorených dopytoch a spojeniach vďaka kvadratickému škálovaniu týchto riešení. Moje meranie výkonu ukázalo, že koľkokrát je riešenie kurzora rýchlejšie, je niekoľko stoviek riadkov na oddiel.
Napriek výkonnostným výhodám poskytovaným riešeniami založenými na kurzore by sa im vo všeobecnosti malo vyhnúť, pretože nie sú relačné.
Riešenia založené na CLR
Jedno možné riešenie založené na CLR (Common Language Runtime) je v podstate formou riešenia založeného na kurzore. Rozdiel je v tom, že namiesto použitia kurzora T-SQL, ktorý vyžaduje veľa prostriedkov na získanie ďalšieho riadku a iteráciu, sa používajú iterácie .NET SQLDataReader a .NET, ktoré sú oveľa rýchlejšie. Jednou z funkcií CLR, vďaka ktorej je táto možnosť rýchlejšia, je, že výsledný riadok nie je potrebný v dočasnej tabuľke – výsledky sa posielajú priamo volajúcemu procesu. Logika riešenia založená na CLR je podobná logike riešenia založenej na kurzore a T-SQL. Tu je kód C#, ktorý definuje uloženú procedúru rozhodnutia:
Používanie systému; pomocou System.Data; pomocou System.Data.SqlClient; pomocou System.Data.SqlTypes; pomocou Microsoft.SqlServer.Server; verejná čiastočná trieda StoredProcedures ( public static void AccountBalances() ( pomocou (SqlConnection conn = new SqlConnection("context connection=true;")) ( SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = @" " + "SELECT actid, tranid, val " + "FROM dbo.Transactions " + "ORDER BY actid, tranid;"; SqlMetaData columns = new SqlMetaData; columns = new SqlMetaData("actid" , SqlDbType.Int); columns = new SqlMetaData("tranid" , SqlDbType.Int); columns = new SqlMetaData("val" , SqlDbType.Money); columns = new SqlMetaData("balance", SqlDbType.Money); SqlDataRecordData record = new Sqddl; Pipe.SendResultsStart(record); conn.Open(); Čítačka SqlDataReader = comm.ExecuteReader(); SqlInt32 prvactid = 0; Zostatok SqlMoney = 0; while (reader.Read()) ( SqlInt32 actid =Int320Sql ;SqlMoney val = reader.GetSqlMoney(2);if (actid == prvactid) ( zostatok += val; ) else ( zostatok = val; ) prvactid = actid;rec ord.SetSqlInt32(0, reader.GetSqlInt32(0)); record.SetSqlInt32(1, reader.GetSqlInt32(1)); záznam.SetSqlMoney(2, val); záznam.SetSqlMoney(3, zostatok); SqlContext.Pipe.SendResultsRow(záznam); ) SqlContext.Pipe.SendResultsEnd(); )))
Aby ste mohli vykonať túto uloženú procedúru v SQL Serveri, musíte najprv vytvoriť zostavu s názvom AccountBalances založenú na tomto kóde a nasadiť ju do databázy TSQL2012. Ak nie ste oboznámení s nasadzovaním zostáv na serveri SQL Server, môžete si prečítať časť „Uložené procedúry a modul Common Language Runtime“ v článku "Uložené procedúry".
Ak ste zostavu nazvali AccountBalances a cesta k súboru zostavy je "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll", môžete zostavu načítať do databázy a zaregistrovať uloženú procedúru s nasledujúcim kódom:
VYTVORIŤ MONTÁŽ Zostatky účtov Z "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll"; GO CREATE PROCEDURE dbo.AccountBalances AKO EXTERNÝ NÁZOV AccountBalances.StoredProcedures.AccountBalances;
Po nasadení zostavy a registrácii procedúry ju môžete spustiť pomocou nasledujúceho kódu:
EXEC dbo.AccountBalances;
Ako som povedal, SQLDataReader je len iná forma kurzora, ale v tejto verzii je réžia na čítanie riadkov oveľa menšia ako pri použití tradičného kurzora v T-SQL. Taktiež v .NET sú iterácie oveľa rýchlejšie ako v T-SQL. Riešenia založené na CLR sa teda tiež lineárne škálujú. Testovanie ukázalo, že výkon tohto riešenia je vyšší ako výkon riešení pomocou poddotazov a spojení, keď počet riadkov v sekcii presiahne 15.
Po dokončení spustite nasledujúci čistiaci kód:
PROCEDURE DROP dbo.AccountSalances; DOP MONTÁŽ Účtovné zostatky;
Vnorené iterácie
Až do tohto bodu som ukázal iteratívne riešenia a riešenia založené na množinách. Nasledujúce riešenie je založené na vnorených iteráciách, čo je hybrid iteračného a množinového prístupu. Cieľom je najprv skopírovať riadky zo zdrojovej tabuľky (v našom prípade bankových účtov) do dočasnej tabuľky spolu s novým atribútom nazvaným rownum, ktorý sa vypočíta pomocou funkcie ROW_NUMBER. Čísla riadkov sú rozdelené podľa actid a usporiadané podľa tranidu, takže prvá transakcia na každom bankovom účte má priradené číslo 1, druhá transakcia má priradené číslo 2 atď. Potom sa v dočasnej tabuľke vytvorí klastrovaný index so zoznamom kľúčov (rownum, actid). Potom používa rekurzívny CTE alebo špeciálne vytvorený cyklus na spracovanie jedného riadka na iteráciu vo všetkých počtoch. Priebežný súčet sa potom vypočíta pripočítaním hodnoty zodpovedajúcej aktuálnemu riadku s hodnotou spojenou s predchádzajúcim riadkom. Tu je implementácia tejto logiky pomocou rekurzívneho CTE:
SELECT actid, tranid, val, ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum INTO #Transactions FROM dbo.Transactions; VYTVORIŤ JEDINEČNÝ KLUSTEROVÝ INDEX idx_rownum_actid ON #Transactions(rownum, actid); S C AS (VYBERTE 1 AKO rownum, actid, tranid, val, val AS sumqty FROM #Transactions WHERE rownum = 1 UNION ALL VYBERTE PRV.rownum + 1, PRV.actid, CUR.tranid, CUR.val, PRV.sumqty + CUR.val Z C AS PRV JOIN #Transactions AS CUR ON CUR.rownum = PRV.rownum + 1 AND CUR.actid = PRV.actid) SELECT actid, tranid, val, sumqty FROM C OPTION (MAXRECURSION 0); DROP TABLE #Transakcie;
A toto je implementácia pomocou explicitnej slučky:
SELECT ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum, actid, tranid, val, CAST(val AS BIGINT) AS sumqty INTO #Transactions FROM dbo.Transactions; VYTVORIŤ JEDINEČNÝ KLUSTEROVÝ INDEX idx_rownum_actid ON #Transactions(rownum, actid); DECLARE @rownum AKO INT; SET @rownum = 1; WHILE 1 = 1 BEGIN SET @rownum = @rownum + 1; AKTUALIZÁCIA CUR SET sumqty = PRV.sumqty + CUR.val FROM #Transactions AS CUR JOIN #Transactions AS PRV ON CUR.rownum = @rownum AND PRV.rownum = @rownum - 1 AND CUR.actid = PRV.actid; IF @@počet riadkov = 0 BREAK; END SELECT actid, tranid, val, sumqty FROM #Transactions; DROP TABLE #Transakcie;
Toto riešenie poskytuje dobrý výkon, keď je v oddieloch veľký počet oddielov s malým počtom riadkov. Potom je počet iterácií malý a hlavnú prácu vykonáva množinová časť riešenia, ktorá spája riadky spojené s jedným číslom riadku s riadkami spojenými s predchádzajúcim číslom riadku.
Viacriadková aktualizácia s premennými
Doposiaľ uvedené triky na výpočet priebežných súčtov zaručene poskytnú správny výsledok. Metodika opísaná v tejto časti je nejednoznačná, pretože je založená skôr na pozorovanom ako zdokumentovanom správaní systému a je tiež v rozpore s princípmi relativity. Jeho vysoká atraktivita je spôsobená vysokou rýchlosťou práce.
Táto metóda používa príkaz UPDATE s premennými. Príkaz UPDATE môže priradiť výrazy premenným na základe hodnoty stĺpca a tiež môže priradiť hodnoty v stĺpcoch výrazu s premennou. Riešenie začína vytvorením dočasnej tabuľky Transactions s atribútmi actid, tranid, val a balance a klastrovaného indexu so zoznamom kľúčov (actid, tranid). Dočasná tabuľka sa potom vyplní všetkými riadkami z pôvodnej databázy transakcií, pričom do stĺpca zostatku všetkých riadkov sa zadá hodnota 0,00. Potom sa zavolá príkaz UPDATE s premennými spojenými s dočasnou tabuľkou na výpočet priebežného súčtu a vloženie vypočítanej hodnoty do stĺpca zostatku.
Používajú sa premenné @prevaccount a @prevbalance a hodnota v stĺpci zostatok sa vypočíta pomocou nasledujúceho výrazu:
SET @prevbalance = zostatok = PRÍPAD, KEĎ actid = @prevaccount THEN @prevbalance + val ELSE val END
Výraz CASE skontroluje, či sú ID aktuálneho a predchádzajúceho účtu rovnaké, a ak sú rovnaké, vráti súčet predchádzajúcich a aktuálnych hodnôt v stĺpci zostatku. Ak sú ID účtu odlišné, vráti sa suma aktuálnej transakcie. Potom sa výsledok výrazu CASE vloží do stĺpca zostatok a priradí sa k premennej @prevbalance. V samostatnom výpise je premennej ©prevaccount priradené ID bežného účtu.
Po príkaze UPDATE riešenie zobrazí riadky z dočasnej tabuľky a vymaže ju. Tu je kód dokončeného riešenia:
VYTVORIŤ TABUĽKU #Transakcie (aktid INT, tranid INT, val PENIAZE, zostatok PENIAZE); VYTVORIŤ KLUSTEROVÝ INDEX idx_actid_tranid ON #Transactions(actid, tranid); INSERT INTO #Transactions WITH (TABLOCK) (actid, tranid, val, balance) SELECT actid, tranid, val, 0,00 FROM dbo.Transakcie ORDER BY actid, tranid; VYHLÁSIŤ @prevaccount AKO INT, @prevbalance AKO PENIAZE; UPDATE #Transactions SET @prevbalance = zostatok = CASE WHEN actid = @prevaccount THEN @prevbilance + val ELSE val END, @prevaccount = actid FROM #Transactions WITH(INDEX(1), TABLOCKX) OPTION (MAXDOP 1); SELECT * FROM #Transakcie; DROP TABLE #Transakcie;
Návrh tohto riešenia je znázornený na nasledujúcom obrázku. Prvá časť je príkaz INSERT, druhá časť je príkaz UPDATE a tretia časť je príkaz SELECT:
Toto riešenie predpokladá, že pri optimalizácii vykonávania UPDATE sa vždy vykoná objednané skenovanie klastrovaného indexu a riešenie obsahuje množstvo rád, aby sa predišlo okolnostiam, ktoré by tomu mohli narušiť, ako napríklad súbežnosť. Problém je v tom, že neexistuje žiadna oficiálna záruka, že optimalizátor bude vždy hľadať v poradí klastrovaného indexu. Na zabezpečenie logickej správnosti kódu sa nemôžete spoliehať na vlastnosti fyzických výpočtov, pokiaľ v kóde nie sú logické prvky, ktoré podľa definície môžu zaručiť takéto správanie. V tomto kóde nie sú žiadne logické funkcie, ktoré by mohli zaručiť presne toto správanie. Prirodzene, rozhodnutie, či túto metódu použijete alebo nie, leží výlučne na vašom svedomí. Myslím si, že je nezodpovedné to používať, aj keď ste to skontrolovali tisíckrát a „zdá sa, že všetko funguje tak, ako má“.
Našťastie v SQL Server 2012 sa táto voľba stáva takmer zbytočnou. Vďaka mimoriadne efektívnemu riešeniu využívajúcemu funkcie agregácie v okne nemusíte premýšľať o iných riešeniach.
meranie výkonnosti
Meral som a porovnával výkon rôznych techník. Výsledky sú uvedené na obrázkoch nižšie:
Výsledky som rozdelil do dvoch grafov, pretože metóda vnoreného dotazu alebo spojenia je taká pomalá, že som na to musel použiť inú mierku. V každom prípade si všimnite, že väčšina riešení vykazuje lineárny vzťah medzi množstvom práce a veľkosťou oddielu a iba riešenie založené na vnorenom dotaze alebo spojení vykazuje kvadratický vzťah. Je tiež jasné, o koľko efektívnejšie je nové riešenie založené na funkcii agregácie v okne. Riešenie na báze UPDATE s premennými je tiež veľmi rýchle, ale z už popísaných dôvodov ho neodporúčam používať. Riešenie CLR je tiež pomerne rýchle, ale vyžaduje, aby ste napísali celý tento .NET kód a nasadili zostavu do databázy. Bez ohľadu na to, ako sa na to pozeráte, uprednostňovaným riešením zostáva set-based riešenie s použitím okenných agregátov.
Syntax SQL 2003 je podporovaná na všetkých platformách.
PODLAHA (výraz)
Ak funkcii odovzdáte kladné číslo, potom funkcia odstráni všetko za desatinnou čiarkou.
SELECT FLOOR(100.1) FROM dual;
Pamätajte však, že v prípade záporných čísel zaokrúhlenie nadol zodpovedá zvýšeniu absolútnej hodnoty.
SELECT FLOOR(-100.1) FROM dual;
Ak chcete dosiahnuť opačný efekt funkcie FLOOR, použite funkciu CEIL.
LN
Funkcia LN vracia prirodzený logaritmus čísla, to znamená mocninu, na ktorú sa musí zvýšiť matematická konštanta e (približne 2,718281), aby sme dostali dané číslo.
Syntax SQL 2003
LN (výraz)
DB2, Oracle, PostgreSQL
Platformy DB2, Oracle a PostgreSQL podporujú pre funkciu LN syntax SQL 2003. DB2 a PostgreSQL tiež podporujú funkciu LOG ako synonymum pre LN.
MySQL a SQL Server
MySQL a SQL Server majú svoju vlastnú prirodzenú logovaciu funkciu LOG.
LOG (výraz)
Nasledujúci príklad Oracle vypočítava prirodzený logaritmus čísla, ktoré sa približne rovná matematickej konštante.
SELECT LN(2,718281) FROM duálny;
Ak chcete vykonať opačnú operáciu, použite funkciu EXP.
MOD
Funkcia MOD vráti zvyšok po vydelení dividendy deliteľom. Všetky platformy podporujú syntax príkazov SQL 2003 MOD.
Syntax SQL 2003
MOD (dividenda, deliteľ)
Štandardná funkcia MOD je navrhnutá tak, aby získala zvyšok po vydelení dividendy deliteľom. Ak je deliteľ nula, potom sa dividenda vráti.
Nasledujúci text ukazuje, ako môžete použiť funkciu MOD v príkaze SELECT.
SELECT MOD(12, 5) Z ČÍSEL 2;
POSITION
Funkcia POSITION vracia celé číslo označujúce počiatočnú pozíciu reťazca vo vyhľadávanom reťazci.
Syntax SQL 2003
POSITION(riadok 1 V riadku 2)
Štandardná funkcia POSITION je určená na získanie pozície prvého výskytu daného reťazca (reťazca!) v hľadanom reťazci (reťazec!). Funkcia vráti 0, ak reťazec! sa v reťazci nevyskytuje! a NULL, ak je niektorý z argumentov NULL.
DB2
DB2 má ekvivalentnú funkciu POSSTR.
MySQL
Platforma MySQL podporuje funkciu POSITION podľa štandardu SQL 2003.
MOC
Funkcia POWER sa používa na zvýšenie čísla na určený výkon.
Syntax SQL 2003
POWER (základ, exponent)
Výsledkom vykonania tejto funkcie je základ zvýšený na výkon určený indikátorom. Ak je základ záporný, exponent musí byť celé číslo.
DB2, Oracle, PostgreSQL a SQL Server
Všetci títo dodávatelia podporujú syntax SQL 2003.
Oracle
Oracle má ekvivalentnú funkciu INSTR.
PostgreSQL
Platforma PostgreSQL podporuje funkciu POSITION podľa štandardu SQL 2003.
MySQL
Platforma MySQL podporuje túto funkciu, nie toto kľúčové slovo POW.
P0W (základ, exponent)
Zvýšenie kladného čísla na mocninu je celkom zrejmé.
SELECT POWER(10.3) FROM dual;
Akékoľvek číslo umocnené na 0 je 1.
SELECT POWER(0.0) FROM dual;
Záporný exponent posúva desatinnú čiarku doľava.
SELECT POWER(10, -3) FROM dual;
TRIEDIŤ
Funkcia SQRT vráti druhú odmocninu čísla.
Syntax SQL 2003
Všetky platformy podporujú syntax SQL 2003.
SORT (výraz)
SELECT SQRT(100) FROM dual;
ŠÍRKA VEDRIČKA
Funkcia WIDTH BUCKET priraďuje hodnoty stĺpcom histogramu rovnakej šírky.
Syntax SQL 2003
V nasledujúcej syntaxi je výraz hodnota, ktorá je priradená stĺpcu histogramu. Typicky je výraz založený na jednom alebo viacerých stĺpcoch tabuľky vrátených dotazom.
WIDTH BUCKET(výraz, minimum, maximum, stĺpce histogramu)
Parameter stĺpce histogramu zobrazuje počet stĺpcov histogramu, ktoré sa majú vytvoriť, v rozsahu min až max. Hodnota parametra min je zahrnutá v rozsahu a hodnota max parameter nezapne sa. Hodnota výrazu je priradená jednému zo stĺpcov histogramu, po ktorom funkcia vráti číslo zodpovedajúceho stĺpca histogramu. Ak výraz nespadá do určeného rozsahu stĺpcov, funkcia vráti hodnotu 0 alebo max + 1 v závislosti od toho, či je výraz menší ako min alebo väčší alebo rovný max.
Nasledujúci príklad rozdeľuje celočíselné hodnoty od 1 do 10 medzi dva stĺpce histogramu.
Ďalší príklad je zaujímavejší. 11 hodnôt od 1 do 10 je rozdelených medzi tri stĺpce histogramu, aby ilustrovali rozdiel medzi minimálnou hodnotou, ktorá je zahrnutá v rozsahu, a maximálnou hodnotou, ktorá nie je zahrnutá v rozsahu.
SELECT x, WIDTH_BUCKET(x, 1.10.3) FROM pivot;
Venujte zvláštnu pozornosť výsledkom cX=, X= 9,9 a X-10. Ich hodnota vstupného parametra, t. j. v tomto príklade 1, spadá do prvého stĺpca, čo znamená spodný koniec rozsahu, pretože stĺpec č. definované ako x >= min. Vstupná hodnota parametra max však nie je zahrnutá v stĺpci maximum. V tomto príklade číslo 10 spadá do stĺpca pretečenia s číslom max + 1. Hodnota 9,9 spadá do stĺpca číslo 3, čo ilustruje pravidlo, že horná hranica rozsahu je definovaná ako x< max.