T-SQL, изчисляване на суми бягане
Това е още общи задачи. Основният принцип е в натрупването на стойностите на атрибут (aggregatable елемент) въз основа на подреждането на друг атрибут или атрибути (поръчване елемент), по избор в присъствието на реда на раздели, определени въз основа на друг атрибут или атрибути (потребителя дял). В живота има много примери изчисли, работещи суми, като например изчисляване на банковите баланси, мониторинга на наличието на продукти на склад и текущите данни за продажбите и т.н.
За да се покаже различните решения използвам счетоводните баланси. Ето кода, който създава и попълва таблица Сделки малко количество данни от изпитванията:
Всеки ред представлява банкова сделка по сметката. Депозити сделка маркиран като положителна стойност във Вал колона и тегления - като сделка отрицателна стойност. Нашата задача - да се изчисли салдото по сметката по всяко време от натрупването на суми от сделки във Вал линия при поръчка от колона tranid, и това трябва да се направи за всеки от тях поотделно. Желания резултат трябва да изглежда така:
За да тествате и двете решения се нуждаят от повече данни. Това може да стане с тази заявка:
Можете да настроите вашия вход, за да промените броя на секциите (сметки) и редове (транзакции) в секцията.
Въз основа на набор от решения с използването на прозорците функции
Ще започна историята с решения, базирани на наборите, която използва един прозорец функция SUM агрегация. Определяне прозорец тук съвсем ясно: трябва да се разделя с прозорец actid, подредени по tranid и филтър, за да изберете реда в рамките на дъното (неограничено ПРЕДИ) на ток. Тук е уместно запитването:
Този код е не само просто и ясно - и то е бързо. Планът на тази заявка е показан по-долу:
Подобен подход може да се осъществява чрез използване на съединенията. Той използва същия предикат, както и в подзаявка на клауза WHERE клауза в връзките нататък. В този случай, N-ти операция на същото в случая на профила А, определен като Т1, ще намерите съответствия в N T2 например, номерата на сделката се движат от 1 до N. В резултат Т1 линии в сравнение повтарят, следователно е необходимо групови редове на всички елементи на T1, за да се получи информация за текущото сделката и се прилагат на натрупването на атрибут Val от T2 да се изчисли натрупаната сума. Готов искане изглежда така:
На фигура планове двете решения са изброени по-долу:
Във всеки раздел на плана включва четене 1 + 2 +. + R редове, за общо (R + R * 2) / 2. Общият брой на редовете, обработвани в план е р * р + р * (R + R2) / 2. Това означава, че броят на операциите по отношение на повишаване на квадрата раздел с увеличаване на размера, т.е., ако увеличението на размера на времето на точка F, увеличението на натоварване е приблизително два пъти. Това е лошо. Например, 100 реда отговаря на 10 хиляди души. Strings и една хиляди редове съответства милиона и т.н. Просто казано, това води до значителен спад на инфлацията в заявка в размер на не толкова малко сечение, тъй като квадратна функция се разраства много бързо. Тези решения работят задоволително в десетки реда на раздел, но не повече.
Решения с помощта на курсора
Курсорът-базирани решения се изпълняват "на челото." Обявява на курсора на въпрос на базата на actid последователност на данните и tranid. След това, един повтарящ се премине записите курсора. При откриване на нова сметка се връща променлива, която съдържа единицата. Във всяка итерация, променливата се добавя към сумата на нова сделка, а след това низа се съхранява в променлива маса с информация за текущото сделката, както и текущата стойност на постепенното общия брой. След повтарящ преминаването връща резултата на променливата на маса. Тук е пълно код решение:
Планът за заявка с курсора е показано по-долу:
Планът се мащабира линейно, така че данните от индекса се търсят само веднъж в определен ред. Също така, всяка операция на прочитането на ред от курсора около една и съща стойност на всяка линия. Ако вземем натоварването на обработката на низ на курсор, равна на г, цената на това решение може да се определи като р * р + р * R * г (както може би си спомняте, стр - броя на секциите, както и R - броя на редовете в раздела). Така че, ако се увеличи броят на редовете в секция на F пъти, натоварването на системата ще бъде р * R * е + р * R * е * г, т.е. увеличава линейно. разходите за обработка на ред е висока, но поради естеството на линейни мащабиране, с определен размер, част от това решение ще бъде да се демонстрира по-добра скалируемост, отколкото решения, базирани на вложени заявки и връзки се дължи на квадратното мащабирането тези решения. Прекарано ме измерване на резултатите показва, че броят, когато решението на курсора по-бързо, равна на няколко стотици реда на раздел.
Въпреки нарастването на предоставените от решенията на базата на курсора, като цяло, те трябва да се избягва, тъй като те не са релационни.
Решения на базата на CLR
Едно възможно решение на базата на CLR (Common Language Runtime) е по същество форма на решения с помощта на курсора. Разликата е, че вместо да се използва T-SQL курсор, който прекарва много ресурси, за да получи следващия ред и повтаряме, повтаряме SQLDataReader .NET и .NET, които са много по-бързо. Една от характеристиките на CLR, която прави тази опция по-бързо е, че не е необходимо в резултат на низ в временна таблица - резултатите се изпращат директно към процеса на разговори. Логически решения, базирани на CLR е, подобно на логиката за решение с помощта на курсора и T-SQL. Тук е C # код, който идентифицира запомнена процедура за решение:
Ако се обадите AccountBalances събрание, както и на пътя към сборните файла - "C: \ Проекти \ AccountBalances \ бин \ Debug \ AccountBalances.dll", за да заредите събранието в базата данни и да се регистрирате съхранена процедура може да бъде следния код:
След внедряване, следния код може да изпълнява своите процедури за монтаж и регистрация:
Както вече казах, SQLDataReader е просто друга форма на курсора, но в тази версия на разходите за четене линии е значително по-малко, отколкото с традиционните курсора в T-SQL. Също така в .NET итерация е много по-бързо от T-SQL. Така решения, базирани на CLR също мащабирани линейно. Тестване показа, че изпълнението на вземането на решения става по-висока производителност и с използването на подзаявки съединения, в които броят на редовете в участъка 15, преминава през.
След приключване е необходимо да се изпълни следната код почистване:
вложени итерация
До този момент аз показа итеративни решения и решения, базирани на декорите. Следващото решение е основано на вложено повторения, които са повтарящи се и хибридни подходи, основани на комплекти. Идеята е първо да копирате редовете от таблицата източник (в този случай, банкови сметки) в временна таблица с нов атрибут наречен ROWNUM, който се изчислява с помощта на функцията за ROW_NUMBER. Номерата на редовете са разделени от actid и подредени по tranid, следователно, първата сделка за всяка зададена банкова сметка номер 1, втората сделка - 2 и т.н. След това, на временна таблица е създадена със списък на скупчени индексни ключове (ROWNUM, actid). След това той използва рекурсивно израз на КТР, или специално създадени цикъл за обработка на един ред на повторение на всички сметки. Тогава аритметичната се изчислява чрез добавяне на стойност, съответстваща на текущия ред, със стойност, свързана с предишния ред. Тук е изпълнението на тази логика, като се използва рекурсивна КТР:
Тази реализация с помощта изрично цикъл:
Това решение осигурява добра производителност, когато има голям брой секции с малък брой редове в разделите. Тогава броя на повторенията е малък, но основната работа се извършва въз основа на набор част от решението, който свързва линията, свързана с една линия номер, с редове, свързани с броя на предишния ред.
Многолинейно актуализация променлива
Показани до този момент техники изчисли, работещи суми са гарантирани за да се получи правилния резултат. Този раздел описва процедурата е неясна, тъй като се основава на наблюдаваните и не са документирани поведението на системата, освен това, че е в противоречие с принципите на относителността. Висока своята привлекателност поради високата скорост.
Този метод използва променлив UPDATE. изявление UPDATE да зададете променлива изразяване на базата на стойността на колоната, и определяне на стойностите в колони с разнообразни прояви. Решението започва със създаването на временна таблица на име Сделки с атрибути actid, tranid, Вал и баланс, както и списък на групи индексни ключове (actid, tranid). След това, на временна таблица е изпълнен с всички линии от оригиналните DB сделки, в която колона баланс във всички редове ценят 0.00 е вписано. UPDATE инструкция след това се нарича с променливите, свързани с масата време, за да се изчисли възлиза на текущите и вмъква изчислената стойност в колона баланс.
Използвани променливи и @prevaccount @prevbalance, както и стойността в колоната баланс се изчислява по следната формула:
След като изказа UPDATE решение е линия от временна таблица и премахва последната. Тук е пълно код решение:
Планът на това решение е показано на следващата фигура. Първата част е INSERT инструкция, а вторият - актуализацията, а третият - изберете:
Измерване на ефективността
Прекарах измерване и сравняване на ефективността на различните методи. Резултатите са показани по-долу:
I проби два от график се дължи на факта, че методът за използване на вложените заявка или по-бавно от другите съединения, така че трябва да се използва различен мащаб за него. Във всеки случай, се отбележи, че повечето от разтворите показват линейна зависимост от обема на работа на размера на раздел, и единственото решение въз основа на вложените заявка или съединения показват квадратичен зависимост. Също така ясно показва колко по-ефективно ново решение въз основа на прозорец обобщаващи функции. UPDATE на базата на променлива решение също е много бърз, но поради причините, описани, аз не препоръчвам да го използвате. Решение използвайки CLR е също доста бързо, но е необходимо да се напише всичко, което код и да разположи .NET монтаж в базата данни. Като един изглежда, и залязва базирано решение, като използвате прозореца единици е най-предпочитан.