Кое е по-бързо: 0 или NULL?

Има три обобщени функции, които най-често се използват на практика: БРОЯ, СУММА и AVG. И ако първият вече е бил обсъден по-рано, тогава с останалите има интересни нюанси с изпълнението. Но нека поговорим за всичко по ред ...

Когато използвате обобщени функции в плана за изпълнение, в зависимост от входния поток, могат да се появят два оператора: Общ поток и Хеш мач.

За да се извърши първата, може да се изисква предварително сортиран набор от стойности и едновременно Структура на потока не блокира изпълнението на последващи изявления.

На свой ред, Хеш мач е блокиращ оператор (с редки изключения) и не изисква сортиране на входния поток. За работа Хеш мач използва се хеш таблица, която се създава в паметта и в случай на неправилна оценка на очаквания брой редове, операторът може да обедини резултатите в tempdb.

Като цяло се оказва, че Структура на потока работи добре на малки сортирани набори от данни, докато Хеш мач се справя добре с големи несортирани комплекти и се поддава добре на паралелна обработка.

След като преодоляхме теорията, нека започнем да разглеждаме как работят съвкупните функции.

Да кажем, че искаме да изчислим средната цена сред всички продукти:

Според таблица с доста проста структура:

Тъй като имаме скаларна агрегация, в плана за изпълнение, както се очаква, ще видим Общ поток:

Вътрешно този оператор извършва две операции по агрегиране COUNT_BIG и СУММА (въпреки че на физическо ниво това се прави като една операция) в графата Цена:

Не забравяйте, че средната стойност се изчислява само за НЕ Е НУЛНО, от операцията COUNT_BIG слиза надолу по колоната, а не със звездичка. Съответно, такова искане:

ще върне 6 вместо 4.

Сега нека разгледаме Изчисли Скалар, вътре в който има интересен израз за проверка на разделяне на нула:

Нека се опитаме да изчислим общата сума:

Планът за изпълнение ще остане същият:

Но ако погледнете операциите, извършени от Общ поток...

можете да бъдете малко изненадани. За какво SQL Server брои сумата, ако имам нужда само от сумата? Отговорът се крие в Изчисли Скалар:

Ако не вземете под внимание БРОЯ, след това според семантиката на езика T-SQL, когато във входния поток няма редове, тогава трябва да се върнем НУЛА, но не 0. Това поведение работи както за скаларни, така и за векторни агрегации:

Освен това такава проверка се прави и за НУЛА, така че за НЕ Е НУЛНО колони. Сега нека разгледаме примери, в които описаните по-горе функции ще бъдат полезни. СУММА и AVG.

Ако искаме да изчислим средната стойност, тогава не е нужно да използваме БРОЙ + СУМА:

Тъй като такава заявка би била по-малко ефективна, отколкото да се използва изрично AVG.

Напред ... Изрично предаване НУЛА няма нужда от обобщена функция:

Тъй като в такава конструкция:

Оптимизаторът прави заместването автоматично:

Но какво, ако искам да получа 0 в резултатите вместо НУЛА? Много често се използва ИНАЧЕ и не мислете:

Очевидно в този случай ще постигнем това, което искаме ... и едно предупреждение ще престане да бъде проблясващо:

Въпреки че е най-добре да напишете заявката си така:

И това не е добре, защото операторът СЛУЧАЙ ще работи по-бързо. Вече знаем, че оптимизаторът замества там ИНАЧЕ НИЩО автоматично ... И така, какви са предимствата на последния вариант?

Както се оказа, операциите по агрегиране доминират от НУЛА стойностите се обработват по-бързо.

Екзекуцията ме взе:

И го правим отново:

Не толкова важно, но въпреки това дава основание за оптимизация в определени ситуации.

Краят на шоуто и завесата? Не. Това не е всичко ...

Както каза един мой приятел: „Няма нито черно, нито бяло ... Светът е многоцветен“ и затова в крайна сметка ще дам интересен пример, когато НУЛА може да навреди.

Нека създадем бавна функция и тестова таблица:

И нека изпълним заявката:

Сега нека опитаме резултата от израза, на който е предаден СУММА, увийте ОТНОСНО:

Скоростта на изпълнение беше намалена с 2 пъти. Трябва да кажа веднага, че това не е магия ... Но грешка в двигателя SQL Server-а това Microsoft вече е "коригиран" в SQL Server 2012 CTP.

Същността на проблема е следната: резултатът от израз в рамките на функции СУММА или AVG може да работи два пъти, ако оптимизаторът смята, че може да се върне НУЛА.

Благодаря на всички за вниманието.

Всичко беше тествано на Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).
Взех плановете за изпълнение от dbForge Studio.

Сега нека кажа няколко думи по друга тема.

Дълго време имах идеята да събера на едно място хора, които се интересуват от бази данни и всичко свързано с тях. В края на този месец планирам да организирам малка среща на потребителската група на SQL Server. В рамките на него се планират 2 доклада. Единият ще бъде от мен, в рамките на който ще се опитам да ви разкажа за „клопките“ при работа с XML и XQuery, докоснете се до проблеми с изпълнението и покажете няколко интересни трика. Има и втори доклад, но той все още е под въпрос ... следователно, ако има желаещи да говорят и споделят нещо интересно SQL Server пишете ми на пощата, посочена в профила.

Пълно участие Безплатно, но пространството е ограничено.
Поради тази причина трябва да се регистрирате предварително, за да мога да знам колко хора планират да присъстват на срещата.