Производительность коллейшенов двух групп.
Напрашивается резонный вопрос — если SQL-коллейшены четко работают «в минус» как мы это видели до сих пор, почему бы их по простому не «выпилить» с просторов сервера? Разве жить от этого лучше не станет? Ну первая причина почему это не делается понятна: «тяжелое» наследие, иногда еще «царского режима» и необходимость его саппортить. От этого никуда не деться. Но есть и вторая причина — производительность. Да — Windows-правила лучше, ближе к естественным языкам и естественным же сортировкам, они обновляются и идут в ногу со временем подстраиваясь под изменения в синтаксисе того или иного языка, которые тоже не являются «застывшими в камне»... Но — за все надо платить. Алгоритмы которые приходится «прокачивать» процессору выполняя сортировку по Windows-коллейшену сложнее чем алгоритмы коллейшенов группы SQL.
Сказать что первые «безумно сложнее» — нет, это определенно сгущение красок. «Несомненно сложнее и затратнее для ресурсов CPU» — вот так будет ближе к объективной реальности. Насколько затратнее? Тут вопрос неоднозначный. Безусловно, есть и будут обстоятельства когда разница применения коллейшенов двух групп очевидна даже конечному пользователю системы и без всяких измерительно-диагностических утилит притом. Рассмотреть все возможные сценарии не представляется возможным — опирайтесь на свой опыт, знания (в т.ч. почерпнутые в данной статье) и здравый смысл. Однако, «генеральное правило», которое скорей всего вас (и ваших пользователей) устроит, будет таким: на современном (с точки зрения «железа») сервере дополнительные расходы ресурсов связанные с проведением сортировки по правилам Windows-набора следует скорее игнорировать, чем искать пути для снижения этих расходов. Главное тут, что повышенная сложность алгоритма становится дополнительной обузой исключительно для CPU. С диска ничего считывать не надо (помните, что выбор набора правил, как таковой, в принципе есть лишь тогда, когда строковое хранилище у нас только A-типа; так что вариант с юникодом отбрасываем, там все решено за нас). Дополнительного трафика в сети не появляется. Надо чуть больше оперативной памяти сервера для реализации более сложного алгоритма, но это вообще «пол-копейки», тем более у вас по определению должен быть приличный ее запас для «пиковых обстоятельств». Так что — CPU only. А как показывает практика этот компонент hardware весьма редко становится истинным узким местом системы. Обычно задолго до того все благополучно «упирается» в дисковую подсистему I/O.
Короткое резюме: если вы только не бьетесь над задачей «еще +0.75% производительности!» дополнительную нагрузку от применения Windows-коллейшенов можно во внимание не принимать. И, да, разумеется — весь этот короткий раздельчик относился только к лингвистическим алгоритмам. Любая BIN/BIN2-сортировка будет быстрее любой из не-числовых (правда, по причинам изложенным ранее, их преимущество над лингвистическим алгоритмом по SQL-правилам будет скорее номинальным, нежели реально ощутимым; однако формально — и здесь BIN-ы побеждают). Но учтите, что, как правило, BIN-варианты возвращают результаты которые не всегда совпадают с теми, что «интуитивно» ожидают ваши пользователи. Ну и плюс (точнее как раз «минус») отмеченный нами ранее — регистр букв будет учитываться вне зависимости хотим ли мы того. Зато по вопросам производительности к ним вот именно что никаких вопросов нет — абсолютно минимальная нагрузка на сервер.
Выбор группы коллейшена.
Хорошо, обобщая все узнанное ранее — каков «администраторско-разработческий» алгоритм выбора группы коллейшена? Ведь перед тем как мы выберем для нашей базы/колонки конкретный коллейшен надо определиться с его группой, просто потому, что скорей всего, подходящий есть и там, и тут. Однако, далеко не все осознают что выбор группы происходит зачастую раньше, чем поднимается этот вопрос. Потому что выбор типа строкового выражения часто может просто не оставить никакого выбора группы коллейшена. Поэтому первое что мы делаем — определяем: нужны нам N-символы или мы будем удовлетворены A-символами.
Нужно ли умышленно стремиться к юникоду просто потому что это «модно», «технологично», «перспективно» и т.д.? Да пожалуй что и нет — зачем не имея оснований вдвое увеличивать занимаемое место на диске, вдвое загружать дисковую же подсистему ввода/вывода, отбирать место в оперативной памяти и т.д.? Если вы совершенно уверены, что все символы нужные вам и вашим пользователям находятся в пределах одной кодовой страницы (а это, обычно, английский плюс еще один язык, например русский для кодовой страницы 1251) — берите на вооружение A-символы и наслаждайтесь большим свободным местом на диске и рядом прочих «профитов». Есть тут правда и одно НО — это вы сейчас совершенно уверены что ваша система будет навсегда русско-английской. А через пол-года? Пять лет? Нужно ли вам потом тратить довольно значительные усилия по переводу вашего поезда с А-рельс на N-рельсы? Увы, но ответить на эти вопросы способны только вы сами, вот тут автор готовых рецептов предложить не в состоянии. Не вызывает сомнений одно: если у вас есть потребность хотя бы в двух кодовых таблицах — юникод ваш выбор однозначно.
Далее, если вы после глубокого анализа выбрали N-путь для ваших строк, то с группой коллейшенов все решилось в тот же момент: Windows-коллейшены — ваше все. Делайте окончательный выбор в этой группе. Если же вы решили остановиться на «старых, добрых» однобайтовых символах, то, как ни странно, склоняйтесь сперва к той же самой группе, Windows. На первый взгляд для A-символов можно удовлетвориться и альтернативной группой, однако:
- SQL-коллейшены существуют практически исключительно для целей обратной совместимости с ранними версиями SQL Server. Причем это не лозунг, а официальная позиция разработчика движка, т.е. SQL Server team;
- Windows-коллейшены обеспечивают единообразный результат вне зависимости с колонкой какого типа (A-/N-) вы работаете. Более того, такой результат будет аналогичен результату работы любого прикладного приложения (не важно работает оно с базой или нет), при условии что последнее использует рекомендованные Windows API функции;
- наконец просто официальная же рекомендация от той же команды SQL Server: если у вас нет специфических требований по очень высокому уровню производительности сервера или по необходимости поддержки существующих приложений — предпочтите Windows-коллейшены
В заключении, я хочу вам показать какие «грабли» вас вполне могут ожидать при использовании SQL-коллейшена и почему вы должны предпочесть иную альтернативу. Для этого создадим тестовую базу с одним из таких коллейшенов:
1 | CREATE DATABASE [~DB~] COLLATE SQL_Latin1_General_Cp1251_CI_AS |
А в ней — тестовую таблицу с колонкой A-типа:
1 2 3 | USE [~DB~] go CREATE TABLE TT(ID int, Col1 char(50)) |
Наполните свою таблицу тестовыми данными. Например, если у вас есть учебная база AdventureWorks2008R2 (а если нет — срочно ее установите!), то можно выполнить команду:
1 2 | INSERT INTO TT SELECT EmailAddressID, EmailAddress FROM AdventureWorks2008R2.Person.EmailAddress |
Это обеспечит нас примерно 20 тысячами записей. Теперь создадим индекс по нашей A-колонке:
1 | CREATE NONCLUSTERED INDEX TTidx ON TT(Col1) |
И, наконец, вот он — «вопрос на 5»: какая из двух показанных ниже команд «легче» (в смысле ресурсозатратности) для сервера:
1 2 | SELECT * FROM TT WHERE Col1='админ@склКМД.ру' SELECT * FROM TT WHERE Col1=N'админ@склКМД.ру' |
Да, вы не ошиблись: вся разница у них — тип литерала, с которым движку нужно соотнести значения в колонке Col1. Ну как, какие мысли насчет поставленного вопроса? Одинаково? Тогда вызовите план исполнения для этого пакета из двух команд. Допустим у автора он выглядит так:
А вот так вот! Index Seek против Table Scan и, как следствие 4% стоимости плана против 96%!! Что случилось во втором случае? Куда индекс подевался? Все просто. Дело в приоритете типе данных, чей список декларирует: все N-символы имеют приоритет над всеми A-символами. И присмотритесь, во втором SELECT-е как раз эти типы и сошлись, первый справа (литерал), второй слева (колонка). А значит что? То, что все данные колонки сначала должны быть конвертированы к юникоду и лишь потом сравниваться с литералом. Да, было бы намного лучше для всех, если бы конвертация выполнялась в обратную сторону и юникодный литерал приводился бы к A-формату. Но если вы начнете прикидывать алгоритм такого перевода хотя бы мысленно, вы очень скоро наткнетесь на правильный вывод — в общем случае такое преобразование невозможно. В частных случаях — да. А в общем — нет. Невозможно гарантировать сопоставление абсолютно любому N-символу хоть что-нибудь из A-таблицы, да и даже из набора таких таблиц. Но вот обратное преобразование гарантируется, байт всегда можно расширить до двух. Поэтому — переводим колонку в N-формат. И вот тут-то мы и попадаем в ловушку коллейшенов разных типов! Индекс-то уже «заточен» под SQL-сравнение, а N-символы будут сопоставляться всегда только по Windows-правилам, понимаете? OK, поясню.
Как мы видели в примерах ранее SQL-коллейшен расставит строчки так:
New-Zel
NewYork
А Windows-коллейшен так:
New-York
New-Zel
Если хотите, можете считать что первый «засчитывает» знак «тире» за букву, а второй — нет. Как работает шаг плана исполнения Index Seek за применение которого мы, собственно, и бьемся тут? Очень просто: при поиске того или иного значения он бежит по отсортированному списку пока не встретит это самое искомое значение (вариант «нашел!») или любое значение больше его (вариант «не нашел!», возвращается пустой резалт-сет). Допустим мы ищем в нашем индексе именно слово NewYork без тире. Мы хотим именно такую форму и никакой иной. При SQL-правилах (по которым выстроен индекс) данное значение прекрасно обнаруживается. Нахождение выше его по списку слова New-York проблем не вызывает, SQL-коллейшен понимает, что искомое слово, возможно, будет обнаружено ниже. Но у Windows-коллейшена иная логика! Он «думает», что раз слово New-York обнаружено (а оно, напомню, выше в отсортированном списке который и есть наш созданный индекс!), а слово NewYork так и не встретилось — его нет, и поиск нужно заканчивать. Но оно-то есть! А поэтому Index Seek при таких раскладах никак не «прокатывает», Index Scan максимум. Ну или недалеко от него ушедший Table Scan.
Хорошо, скажете вы, «грабли» понятны. Но их корень в конвертации типов и наличии двух типов правил сопоставления символов, а автор вроде как ратует за повсеместное применение коллейшенов группы Windows? Ну, допустим — пересоздадим мы ту же базу с коллейшеном вроде Cyrillic_General_100_CI_AS, остальное трогать не будем, конвертация, надо думать, никуда не денется... И что же ваш «навороченный» коллейшен — поможет, что ли? А вот представьте себе!
Как вы уже поняли «магия» тут в том, что хотя первый «затык» (конверсия типов) и остается, убирается второй — «двойные» правила! Индекс изначально выстраивается по Windows-логике, а, как мы знаем, Windows-коллейшен будет применять такую логику (и только ее!) к любому типу колонки. Разумеется и в этом случае часть ресурсов и времени уйдет на конвертацию типов, но это несопоставимо с теми потерями что наблюдаются при использовании SQL-коллейшена.
Какие правила и выводы можно сделать для себя из всего вышеизложенного? Их будет два. Правило 1, «общего случая»:
Правило 2, «частного случая»:
Заключение
Мы достаточно подробно и в нюансах рассмотрели такую важную тему как «набор правил сопоставления двух строковых значений». Или, по простому, коллейшены. Автор далек от мысли что по теме сказано все и добавить более нечего, он сразу согласился что по коллейшенам можно отдельные книги писать, не то что статьи. Но то, что ничего существенного упущено не было ему хотелось бы надеяться.
Прежде чем попрощаться с вами до новых интересных (на что автор так же скромно надеется 
) заметок по SQL тематике, давайте составим краткое резюме изученного и узнанного в данной большой статье:
- коллейшены можно присвоить на 4-х уровнях, выстроенных в строгую иерархическую «лестницу»: сервер (экземпляр), база данных, колонка таблицы, отдельное выражение;
- коллейшены всех уровней (с небольшими оговорками) можно свободно назначать, менять и просматривать их текущие значения, причем делать все это можно как пользуясь интерфейсом студии, так и посредством T-SQL кода;
- если в одном запросе и в одном обособленном строковом выражении сталкиваются два или более коллейшенов, то согласно строгим, хотя и довольно запутанным, правилам из них выбирается один который и назначается финальным коллейшеном всего этого обособленного выражения;
- в случае если правилами предыдущего пункта «победитель» не выявлен запрос возвращает ошибку о неразрешимости конфликта имен коллейшенов;
- любой коллейшен принадлежит либо группе SQL-коллейшенов, либо группе Windows-коллейшенов;
- имена коллейшенов обеих групп формируются по заранее заданной схеме и, обычно, содержат полное описание основных характеристик данного коллейшена;
- как правило, подходящий нам коллейшен можно взять в любой из двух групп;
- почти всегда мы должны предпочесть вторую (Windows) группу;
- любой коллейшен может проводить сортировку двумя алгоритмами: лингвистическим и числовым;
- лингвистический алгоритм более затратен в плане ресурсов (особенно если это алгоритм коллейшена из группы Windows), но обеспечивает более «интуитивно-ожидаемые» результаты;
- числовые алгоритмы, в свою очередь, разделились на «старые» (для не-юникод символов, обозначаются суффиксом _BIN в конце имени коллейшена), и новые (для юникода, обозначаются суффиксом _BIN2 в конце имени коллейшена). Применять следует тот, что соответствует типу колонки по которой вы планируете осуществить такую сортировку (т.е. сортировку только на основании цифровых кодов символов);
- при переводе не-юникод символа из одной кодовой страницы в другую неизбежно «вмешательство» кодовых точек юникод. Причем вмешательство это полностью скрыто от SQL-программиста и не проявляет себя никак, кроме как влиянием на содержание выходного резалт-сета;
- выбирать правильный коллейшен в отрыве от типа (юникод или нет) строкового выражения/колонки к которым он будет применен бессмысленно. Зачастую тип вторых оказывается важнее первого;
- если мы все же остановились на применении коллейшена группы SQL, крайне важно тотальное исключение возможности применения юникод символов в вашем T-SQL коде.
Что же, автору осталось лишь поблагодарить читателей за тот солидный кусок времени что был ими посвящен изучению столь массивного объема информации и пожелать им оптимальных SQL-запросов. Увидимся, пока! 