Каковы преимущества и недостатки использования сортировки без учета регистра в SQL Server (с точки зрения производительности запросов)?
У меня есть база данных, которая в настоящее время использует сопоставление без учета регистра, и мне это не очень нравится. Я бы очень хотел изменить его на чувствительный к регистру. Что следует учитывать при изменении сопоставления?
(Я добавил это как отдельный ответ, потому что он существенно отличается от моего первого.) Хорошо, нашел кое-какую актуальную документацию. В этой статье MS KB говорится о существующих различиях в производительности между различными сопоставлениями, но не там, где вы думаете. Разница между сопоставлениями SQL (обратно совместимыми, но без поддержки Юникода) и сопоставлениями Windows (с поддержкой Юникода):
Как правило, степень разницы в производительности между сопоставлениями Windows и SQL не будет существенной. Разница проявляется только в том случае, если рабочая нагрузка привязана к ЦП, а не ограничена вводом-выводом или скоростью сети, и большая часть этой нагрузки на ЦП вызвана накладными расходами на манипуляции со строками или сравнения, выполняемые в SQL Server.
И SQL, и Windows имеют версии с учетом регистра и без учета регистра, поэтому похоже, что это не главная проблема.
Еще одна хорошая история "из окопов" в отличной статье Дэна под названием "Ад сортировки":
Я унаследовал смешанную среду сортировки с большим количеством сортировок, чем я могу сосчитать по пальцам одной руки. Различные сопоставления требуют обходных путей, чтобы избежать ошибок «невозможно разрешить конфликт сопоставления», и эти обходные пути снижают производительность из-за выражений, не подлежащих анализу. Работа со смешанной сортировкой — это настоящая боль, поэтому я настоятельно рекомендую вам стандартизировать единственную сортировку и отклоняться от нее только после тщательного обдумывания.
Он заключает:
Я лично не думаю, что производительность следует даже учитывать при выборе правильного сопоставления. Одна из причин, по которой я живу в аду сортировки, заключается в том, что мои предшественники выбрали бинарную сортировку, чтобы максимально использовать производительность наших систем OLTP с высокой степенью транзакций. За единственным исключением ведущего поиска по таблице с подстановочными знаками, я не обнаружил заметной разницы в производительности с нашими различными сопоставлениями. Настоящим ключом к производительности является настройка запросов и индексов, а не сопоставление. Если для вас важна производительность, я рекомендую вам выполнить тест производительности с реальными запросами приложения, прежде чем выбирать параметры сортировки на основе ожидаемой производительности.
Надеюсь это поможет.
Если вы измените параметры сортировки в базе данных, вам также придется изменить их для каждого столбца в отдельности — они сохранят настройку параметров сортировки, которая действовала при создании их таблицы.
create database CollTest COLLATE Latin1_General_CI_AI
go
use CollTest
go
create table T1 (
ID int not null,
Val1 varchar(50) not null
)
go
select name,collation_name from sys.columns where name='Val1'
go
alter database CollTest COLLATE Latin1_General_CS_AS
go
select name,collation_name from sys.columns where name='Val1'
go
Результат:
name collation_name
---- --------------
Val1 Latin1_General_CI_AI
name collation_name
---- --------------
Val1 Latin1_General_CI_AI
Я бы сказал, что самым большим недостатком перехода на сортировку с учетом регистра в производственной базе данных будет то, что многие, если не большинство, ваших запросов завершатся ошибкой, потому что в настоящее время они предназначены для игнорирования регистра.
Я не пытался изменить параметры сортировки в существующей базе данных, но подозреваю, что это может занять довольно много времени. Вам, вероятно, придется полностью заблокировать своих пользователей, пока происходит процесс. Не пытайтесь это сделать, если вы не провели тщательное тестирование на dev.
Select GivenName from Table where GivenName = 'Peter' вернет PETER, peter и peTer. Это отстой. И при таком сравнении должны возникать потери производительности.
- person Klaus Byskov Pedersen; 17.11.2010
WHERE UPPER(GivenName) = 'PETER' выполнит полное сканирование таблицы. См. sqlservercode.blogspot.com/2007/05. /
- person BradC; 17.11.2010
Я не могу найти ничего, чтобы подтвердить, что правильно построенные запросы работают быстрее в базе данных с учетом регистра или без учета регистра (хотя я подозреваю, что разница незначительна), но мне ясно несколько вещей:
Такой запрос:
... WHERE UPPER(GivenName) = 'PETER'
не будет использовать индекс для GivenName. Вы бы подумали что-то вроде:
... WHERE GivenName = 'PETER' COLLATE SQL_Latin1_General_CP1_CS_AS
будет работать лучше, и это работает. Но для максимальной производительности вам нужно сделать что-то вроде:
... WHERE GivenName = 'PETER' COLLATE SQL_Latin1_General_CP1_CS_AS
AND GivenName LIKE 'PETER'
(подробности см. в этой статье )
Если вы меняете параметры сортировки базы данных, но не параметры сортировки сервера (и в результате они не совпадают), будьте осторожны при использовании временных таблиц. Если иное не указано в их инструкции CREATE, они будут использовать параметры сортировки по умолчанию сервера, а не базы данных, что может привести к JOIN или другим сравнениям со столбцами вашей БД (при условии, что они также изменены на параметры сортировки БД, как указано Damien_The_Unbeliever) потерпеть неудачу.
