zřetězení dvou nebo více datových sad je nejčastěji vyjádřena v T-SQL pomocí UNION ALL klauzule. Vzhledem k tomu, že optimalizátor Serveru SQL může často změnit pořadí věcí, jako spoje a agregátů s cílem zlepšit výkon, to je docela rozumné očekávat, že SQL Server by také zvážit pořadí zřetězení vstupů, kde by to mohlo poskytnout výhodu. Optimalizátor by například mohl zvážit výhody přepisování A UNION ALL B jako B UNION ALL A.

ve skutečnosti to optimalizátor serveru SQL Server nedělá. Přesněji řečeno, tam byla nějaká omezená podpora pro zřetězení vstupu přeskupení v SQL Server uvolní až do 2008 R2, ale to bylo odstraněno v SQL Server 2012, a není zabrousil od.

SQL Server 2008 R2

intuitivně je pořadí spojovacích vstupů důležité pouze v případě, že existuje cíl řádku. Ve výchozím nastavení SQL Server optimalizuje plány provádění na základě toho, že všechny kvalifikační řádky budou vráceny klientovi. Když je v platnosti cíl řádku, optimalizátor se pokusí najít plán provádění, který rychle vytvoří několik prvních řádků.

Řádek cíle mohou být stanoveny v několika způsoby, například pomocí TOPFAST n nápovědu pro dotaz, nebo pomocí EXISTS (které ze své podstaty potřebuje najít nanejvýš jeden řádek). Tam, kde není cíl řádku (tj. klient vyžaduje všechny řádky), obecně nezáleží na tom, v jakém pořadí se odečítají vstupy zřetězení: každý vstup bude nakonec v každém případě plně zpracován.

omezená podpora ve verzích až do SQL Server 2008 R2 platí tam, kde je cíl přesně jeden řádek. V této konkrétní situaci, SQL Server bude pořadí zřetězení vstupů na základě očekávaných nákladů.

to se neděje během optimalizace založené na nákladech (jak by se dalo očekávat), ale spíše jako přepsání normálního optimalizačního výstupu na poslední chvíli po optimalizaci. Toto uspořádání má tu výhodu, že nezvyšuje náklady-na základě plánu prostor hledání (potenciálně jeden alternativa pro každé možné pořadí), zatímco stále vyrábí plán, který je optimalizován pro návrat první řadě rychle.

Příklady

následující příklady používají dvě tabulky s identickým obsahem: milion řady čísel od jedné do milionu. Jedna tabulka je halda bez neuzavřených indexů; druhý má jedinečný seskupený index:

CREATE TABLE dbo.Expensive( Val bigint NOT NULL); CREATE TABLE dbo.Cheap( Val bigint NOT NULL, CONSTRAINT UNIQUE CLUSTERED (Val));GOINSERT dbo.Cheap WITH (TABLOCKX) (Val)SELECT TOP (1000000) Val = ROW_NUMBER() OVER (ORDER BY SV1.number)FROM master.dbo.spt_values AS SV1CROSS JOIN master.dbo.spt_values AS SV2ORDER BY ValOPTION (MAXDOP 1);GOINSERT dbo.Expensive WITH (TABLOCKX) (Val)SELECT C.ValFROM dbo.Cheap AS COPTION (MAXDOP 1);

Žádný Řádek Cíl

následující dotaz hledá stejné řádky v každé tabulce, a vrací zřetězení dvou sad:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005;

provádění plánu produkován optimalizátor dotazu je:

upozornění na root SELECT provozovatel je nás upozorňují, že je zřejmé, chybějící index na haldy tabulky. Varování na operátoru skenování tabulky přidává Průzkumník Sentry One Plan Explorer. Upozorňuje nás na I / O náklady na zbytkový predikát skrytý ve skenování.

pořadí vstupů do zřetězení zde nezáleží, protože jsme nestanovili cíl řádku. Oba vstupy budou plně přečteny, aby se vrátily všechny řádky výsledků. Zajímavé (i když to není zaručeno) všimněte si, že pořadí vstupů se řídí textovým pořadím původního dotazu. Všimněte si také, že pořadí řádků konečného výsledku není specifikováno, protože jsme nepoužili klauzuli nejvyšší úrovně ORDER BY. Budeme předpokládat, že je záměrné a konečné objednání je bezvýznamné pro daný úkol.

Pokud obrátíme pořadí tabulek v dotazu takto:

SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 UNION ALL SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005;

plnění plánu vyplývá, změně, přístupu k seskupený tabulky nejprve (opět, to není zaručeno):

Oba dotazy, lze očekávat, že mají stejné funkční vlastnosti, jako oni provádět stejné operace, jen v jiném pořadí.

S řadou Cíl

Jasně, nedostatek indexování na haldě tabulka bude normálně nalezení konkrétní řádky dražší, ve srovnání s stejné operace na seskupený tabulka. Pokud požádáme optimalizátor o plán, který rychle vrátí první řádek, očekáváme, že SQL Server změní pořadí spojovacích vstupů, takže je nejprve konzultována levná clusterová tabulka.

pomocí dotazu, který nejprve zmiňuje tabulku haldy, a pomocí nápovědy rychlého dotazu 1 k určení cíle řádku:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005OPTION (FAST 1);

odhadované provádění plánu vyrábí na instanci serveru SQL Server 2008 R2 je:

Všimněte si, že zřetězení vstupů byly doobjednat ke snížení odhadované náklady na vrácení první řadě. Všimněte si také, že chybějící index a zbytkové I/o varování zmizely. S tímto tvarem plánu nemá žádný problém, pokud je cílem co nejrychleji vrátit jeden řádek.

stejný dotaz proveden na SQL Serveru 2016 (buď pomocí mohutnost odhad modelu) je:

SQL Server 2016 není doobjednat zřetězení vstupů. I/o varování Plan Explorer se vrátilo, ale bohužel optimalizátor tentokrát nevytvořil chybějící varování indexu (i když je to relevantní).

Obecné přeskupování

Jak již bylo zmíněno, přepis po optimalizaci, který přepisuje zřetězení vstupů, je účinný pouze pro:

• SQL Server 2008 R2 a dřívějších
• řádek cíl přesně jeden

Pokud jsme opravdu jen jeden řádek se vrátil, spíše než plán optimalizované vrátit první řadě rychle (ale který bude nakonec ještě vrátí všechny řádky), můžeme použít TOP doložka odvozené tabulky nebo společné tabulka výraz (CTE):

SELECT TOP (1) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UA;

Na SQL Server 2008 R2 nebo starší, to vytváří optimální doobjednat-zadání plánu:

Na SQL Serveru 2012, 2014 a 2016 žádné post-optimalizace uspořádání děje:

chceme-Li více než jeden řádek se vrátil, například pomocí TOP (2), požadovaný přepsat nebude aplikován na SQL Server 2008 R2, i když FAST 1 tip je také používán. V této situaci se musíme uchýlit k trikům, jako je použití TOP s proměnnou a OPTIMIZE FOR Nápověda:

DECLARE @TopRows bigint = 2; -- Number of rows actually needed SELECT TOP (@TopRows) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UAOPTION (OPTIMIZE FOR (@TopRows = 1)); -- Just a hint

dotaz náznak je dostatečné k nastavení řadě gól, zatímco runtime hodnotu proměnné zajišťuje požadovaný počet řádků (2) je vrácena.

skutečné plán spuštění na serveru SQL Server 2008 R2 je:

Oba řádky, které jsou vráceny pocházejí z doobjednat usilovat o vstup, a Tabulku se neprovede vůbec. Průzkumník plánu zobrazuje počty řádků červeně, protože odhad byl pro jeden řádek (kvůli nápovědě), zatímco dva řádky se vyskytly v době běhu.

bez spojení vše

tento problém se také neomezuje na dotazy napsané explicitně s UNION ALL. Jiné stavby, například EXISTSOR může také vyústit v optimalizaci zavádí operátor zřetězení, které mohou trpět nedostatkem vstupních pořadí. Tam byla nedávná otázka na správce databáze Stack Exchange s přesně tento problém. Transformace dotazu z tu otázku použít náš příklad tabulky:

SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 ) OR EXISTS ( SELECT 1 FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 ) THEN 1 ELSE 0 END;

plán spuštění na serveru SQL Server 2016 má haldy tabulky na první vstup:

Na SQL Server 2008 R2 pořadí vstupů je optimalizován tak, aby odrážely jeden řádek cíl semi připojit:

V další optimální plán, heap scan je nikdy provedeny.

Řešení

V některých případech, bude zřejmé, na dotaz spisovatel, který se jedné z zřetězení vstupů bude vždy levnější než ostatní. Pokud je to pravda, je zcela platné přepsat dotaz tak, aby se levnější vstupy zřetězení objevily nejprve v písemném pořadí. Samozřejmě to znamená, že spisovatel dotazů si musí být vědom tohoto omezení optimalizátoru a je připraven spoléhat se na nezdokumentované chování.

složitější problém nastává, když se náklady na zřetězení vstupů liší podle okolností, možná v závislosti na hodnotách parametrů. Použití OPTION (RECOMPILE) nepomůže na serveru SQL Server 2012 nebo novějším. Tato možnost může pomoci na serveru SQL Server 2008 R2 nebo starší, ale pouze v případě, že je splněn požadavek na cíl jednoho řádku.

pokud existují obavy ohledně spoléhání se na pozorované chování (vstupy zřetězení plánu dotazu odpovídající textovému pořadí dotazu), lze k vynucení tvaru plánu použít průvodce plánem. Tam, kde jsou různé vstupní příkazy optimální pro různé okolnosti, lze použít více průvodců plánu, kde lze podmínky předem přesně kódovat. To je však stěží ideální.

závěr

SQL Server optimalizace dotazů se ve skutečnosti obsahují náklady-na základě průzkumu pravidlo, UNIAReorderInputs, který je schopen generovat zřetězení vstupní pořadí variant a objevování alternativ během cost-based optimalizace (ne jako single-shot post-optimalizace přepsat).

toto pravidlo není v současné době povoleno pro obecné použití. Pokud mohu říct, je aktivován pouze tehdy, když je přítomen průvodce plánem nebo USE PLAN Nápověda. To umožňuje motoru úspěšně vynutit plán, který byl vygenerován pro dotaz, který se kvalifikoval pro přepis přeskupování vstupu, i když aktuální dotaz nesplňuje podmínky.

Můj pocit je, že tento průzkum pravidlo je záměrně omezen pouze na toto použití, protože dotazy, které by měly prospěch z zřetězení vstupní pořadí jako součást nákladů na základě optimalizace jsou považovány za dostatečně časté, nebo snad proto, že existuje obava, že další úsilí by se nevyplatilo. Můj vlastní názor je, že zřetězení vstupu operátora by mělo být vždy prozkoumáno, když je v platnosti řádek.

je také škoda, že (omezenější) přepis po optimalizaci není účinný v SQL Server 2012 nebo novějším. Mohlo to být způsobeno jemnou chybou,ale v dokumentaci, znalostní bázi nebo na Connect jsem o tom nic nenašel. Přidal jsem novou položku Connect zde.

aktualizace 9. srpna 2017: Toto je nyní opraveno pod vlajkou trasování 4199 pro SQL Server 2014 a 2016, viz KB 4023419:

Oprava: Dotaz s UNION ALL a řádek cíl může běžet pomaleji v SQL Server 2014 nebo novější verze, když je ve srovnání s SQL Server 2008 R2