Сомнение возникло в рекомендации "нужно использовать collation, который позволяет хранить в юникоде (например, Cyrilic_General_CI_AS)"

[VitaliyK]

Уважаемые знатоки!

Вопрос возник по настройкам SQLServer'а 2005 под Axapta (3) в связи с тем, что неоднократно видел рекомендации хранить текстовые данные в collation Cyrilic_General_CI_AS.

Если вкратце - ситуация возникла такая -
ставятся эксперименты по повышению производительности

Исторически сложилось, что collation таблиц рабочей БД - General_Latin1_1251_CI_AS.
Как и описано на форумах, при этом иногда возникает ситуация, когда из-за этого не используются имеющиеся индексы и вместо index seek в плане - table scan + compute scalar по тесктовому полю с collation General_Latin1_1251_CI_AS

Если перенести данные в таблицы с рекомендуемой гуру collation Cyrilic_General_CI_AS ситуация исправляется, и время выполнения запросов select top 1 ... по индексам с текстовыми полями уменьшается на пару порядков.

Но есть одно неприятное "НО"...

При этом время выполнения большинства остальных запросов увеличивается раза в 2

И, субъективно, общая усредненная скороость выполенения запросов падает на 20 - 30%

пара примеров

Пример 1.
по преобразованной к Cyrilic_General_CI_AS таблице делается 2 select'а (причем, эксперименты ставились на разных экземплярах сервера и БД с разными collation сервера и БД по умолчанию и результаты опытов от этих параметров не зависят, как оказалось)

первый селект - в родном collation Cyrilic_General_CI_AS

Цитата:

declare @d datetime
set @d =getdate()
select *
from
SalesPickingListJournalLine sl with ( nolock)
where charindex ( ltrim ( rtrim ( sl . PickingListId ) ) , ',01Н0002320') > 0
select db_name(),getdate(), @d, datediff(ms,@d,getdate())

результат - 630 мс, чистый table scan

второй селект - с преобразованием collation выражения к General_Latin1_1251_CI_AS

Цитата:

declare @d datetime
set @d =getdate()
select *
from
SalesPickingListJournalLine sl with ( nolock)
where charindex ( ltrim ( rtrim ( sl . PickingListId collate SQL_Latin1_General_CP1251_CI_AS) ) , ',01Н0002320' ) > 0
select db_name(),getdate(), @d, datediff(ms,@d,getdate())

результат - уже 270 мс, точно такойже чистый table scan с таким же cost'ом выполнения

Пример 2.
сравнение времени выполнения более сложного запроса в оригинальной БД (collate SQL_Latin1_General_CP1251_CI_AS) и в преобразованной к collation Cyrilic_General_CI_AS на том же железе

текст запроса (профайлер помог)

Цитата:

SELECT A.ITEMID
FROM INVENTTABLE A WITH( NOLOCK),INVENTSUM B WITH( NOLOCK),INVENTDIM C WITH( NOLOCK)
WHERE ((A.DATAAREAID='DAT') AND (A.A_LINECODE ='CIS'))
AND ((B.DATAAREAID='DAT') AND (((B.CLOSED=0) AND (B.CLOSEDQTY=0)) AND (A.ITEMID =B.ITEMID )))
AND ((C.DATAAREAID='DAT') AND ((C.INVENTPROJECTID ='ОЧЕРЕДЬ') AND (B.INVENTDIMID=C.INVENTDIMID)))
GROUP BY A.ITEMID
ORDER BY A.ITEMID
OPTION(FAST 3)

при collation SQL_Latin1_General_CP1251_CI_AS среднее время выполнения - 200мс
при collation Cyrilic_General_CI_AS среднее время выполнения - 700мс
(перестройки индексов, статистик, танцы с бубном и пр. - все делалось)

Причем такая ситуация с большинством отловленных профайлером запросов- если не в 2-3 раза, то в 1.5 раза медленнее Cyrilic_General_CI_AS (хотя косты запросовв планах - одинаковые)

И, выходит, если оставить старый collation SQL_Latin1_General_CP1251_CI_AS - то
можно хотя бы обойти неиспользование индексов вводом вычисляемых полей вида
TERTIARY_WEIGHTS(TEXT_FIELD1) (это то, на что заменается compute scalar'ом в плане использование текстового поля) и постройке индексов по ним, хоть это и очень "кривое" решение, а вот сделать с гарантированным падением скорости в 1.5 раза при смене collation на Cyrilic_General_CI_AS ничего не получается.

В чем я мог ошибиться при экспериментах?
Или - пусть хоть и медленнее, но технологичней с Cyrilic_General_CI_AS?

С уважением,
Виталий

PS:
SQL2005 SP1
немного доп. информации по экспериментам - http://www.sql.ru/forum/actualthread...6&pg=2#4663453

[VitaliyK]

Провели замеры на типичных пользовательских операциях из клиента.

Усредненно выборки по таблицам с General_Latin1_1251_CI_AS быстрее чем Cyrilic_General_CI_AS на 30%.

если неиспользование table scan вместо индекса при General_Latin1_1251_CI_AS по описаниям должен побеждаться вводом на сервере вычисляемого поля вида TERTIARY_WEIGHTS(TEXT_FIELD1) и индексами по нему,

то для таблиц в Cyrilic_General_CI_AS помогает ввод вычисляемых доп. полей вида InventDimID_fast = (InventDimID collate General_Latin1_1251_CI_AS), и использования их в Like и т.п.

например,

Цитата:

select *
from SalesPickingListJournalLine (nolock)
where TST_01 like '%01Д0002320%'

быстрее чем

Цитата:

select *
from SalesPickingListJournalLine (nolock)
where PickingListId like '%01Д0002320%'

в 7 раз (40-50мс против 330-340)

при

Цитата:

[dbo].[SALESPICKINGLISTJOURNALLINE](
...
[PICKINGLISTID] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT DEFAULT (''),
...
[TST_01] AS ([PickingListId] collate SQL_Latin1_General_CP1251_CI_AS)
) ON [PRIMARY]

Да и JOIN'ы при SQL_Latin1_General_CP1251_CI_AS быстрее, например,

Цитата:

select top 1 *
from SalesPickingListJournalLine l (nolock) join SalesPickingListJournalTable t (nolock) ON l.TST_01 = t.TST_01
order by t.TST_01

быстрее чем

Цитата:

select top 1 *
from SalesPickingListJournalLine l (nolock) join SalesPickingListJournalTable t (nolock) ON l.PickingListId = t.PickingListId
order by t.PickingListId

вдвое (без order by тоже быстрее, но на 15%, в SalesPickingListJournalTable тоже добавлено вычисляемое поле [TST_01] AS ([PickingListId] collate SQL_Latin1_General_CP1251_CI_AS))


Жаль, что только до первой синхронизации только помогут эти методы, конечно...

Вот такой вот неординарный способ повышения производительности, получается - хранить данные в устаревшем collation...

Жаль, никто не повторил опытов... да и на sql.ru тоже интереса не проявилось пока - то ли все про это давно знают, то ли одно из двух

[mazzy]

Цитата:

Сообщение от VitaliyK

Жаль, никто не повторил опытов...

Почему же не повторил?
Многие на это натыкались и обсуждений было несколько.

Эффект от перехода на Cyrilic_General_CI_AS на SQL2005 ошеломительный.
Те формы, которые открывались несколько минут - открываются мгновенно.

Вы посмотрите на план запроса при выполнении запроса с сортировкой/группировкой при General_Latin1_1251_CI_AS.
Создайте хотя бы пару миллионов записей и сравните время выполнения, а также как задействуется tempdb.

В общем, пилите дальше.

[VitaliyK]

Цитата:

Сообщение от mazzy

Почему же не повторил?
Многие на это натыкались и обсуждений было несколько.

Эффект от перехода на Cyrilic_General_CI_AS на SQL2005 ошеломительный.
Те формы, которые открывались несколько минут - открываются мгновенно.

Вы посмотрите на план запроса при выполнении запроса с сортировкой/группировкой при General_Latin1_1251_CI_AS.
Создайте хотя бы пару миллионов записей и сравните время выполнения, а также как задействуется tempdb.

В общем, пилите дальше.

Понятно.
У нас тоже есть пара форм, которые ошеломилельно быстрее открываются.
Но все остальные, где лишнего table scan не было - медленнее...

Пойду пилу точить, а то зубья под 0 уже...


PS: все же не 2 млн. строк, но более 1 млн

Цитата:

select
top 10
*
from LedgerTrans (nolock)
where AccountNum like '%01%'
order by AccountNum

в General_Latin1_1251_CI_AS на 30% быстрее, чем Cyrilic_General_CI_AS
(тут законный Table scan - в обоих вариантах)

а тут в обоих вариантах нет table scan

Цитата:

SELECT A.ITEMID
FROM INVENTTABLE A WITH( NOLOCK),INVENTSUM B WITH( NOLOCK),INVENTDIM C WITH( NOLOCK)
WHERE ((A.DATAAREAID='DAT') AND (A.A_LINECODE ='CIS'))
AND ((B.DATAAREAID='DAT') AND (((B.CLOSED=0) AND (B.CLOSEDQTY=0)) AND (A.ITEMID =B.ITEMID )))
AND ((C.DATAAREAID='DAT') AND ((C.INVENTPROJECTID ='ОЧЕРЕДЬ') AND (B.INVENTDIMID=C.INVENTDIMID)))
GROUP BY A.ITEMID
ORDER BY A.ITEMID
OPTION(FAST 3)

и разница еще больше.

tempdb могла бы повлиять, но имхо, это бы проявилось при серверном collation отличающимся от Cyrilic_General_CI_AS, но на тестовом сервере специально был выставлен Cyrilic_General_CI_AS и на сервере и на БД - и все то же самое...

[mazzy]

Цитата:

Сообщение от VitaliyK

Если перенести данные в таблицы с рекомендуемой гуру collation Cyrilic_General_CI_AS ситуация исправляется, и время выполнения запросов select top 1 ... по индексам с текстовыми полями уменьшается на пару порядков.

Но есть одно неприятное "НО"...

При этом время выполнения большинства остальных запросов увеличивается раза в 2

И, субъективно, общая усредненная скороость выполенения запросов падает на 20 - 30%

В общем, я не знаю как вы это делали. Я не знаю пересчитывали ли вы статистику после подмены и как замеряете время выполнения "остальных запросов".

Я знаю по своему опыту, что общая усредненная скорость выполнеия запросов ВОЗРАСТАЕТ процентов на 20%-30% на SQL2005.

[mazzy]

Цитата:

Сообщение от VitaliyK

и разница еще больше.

А можете план в обоих случаях показать?

[VitaliyK]

Цитата:

Сообщение от mazzy

А можете план в обоих случаях показать?

конечно, вот план с cyrillic (заменил квадратные скобки на фигурные и заменил кое-где двоеточия на дефисы, т.к. иначе форум не дает отправить сообщение - говорит слишком много картинок)

Цитата:

|--Stream Aggregate(GROUP BY-({A}.{ITEMID}))
|--Nested Loops(Inner Join, OUTER REFERENCES-({B}.{INVENTDIMID}, {Expr1007}) WITH ORDERED PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES-({A}.{ITEMID}, {Expr1006}) WITH ORDERED PREFETCH)
| |--Clustered Index Seek(OBJECT-({db_cyr}.{dbo}.{INVENTTABLE}.{I_175ITEMIDX} AS {A}), SEEK-({A}.{DATAAREAID}={@0}), WHERE-({db_cyr}.{dbo}.{INVENTTABLE}.{A_LINECODE} as {A}.{A_LINECODE}={@1}) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT-({db_cyr}.{dbo}.{INVENTSUM}.{I_174ITEMDIMIDX} AS {B}), SEEK-({B}.{DATAAREAID}={@2} AND {B}.{ITEMID}={db_cyr}.{dbo}.{INVENTTABLE}.{ITEMID} as {A}.{ITEMID}), WHERE-({db_cyr}.{dbo}.{INVENTSUM}.{CLOSED} as {B}.{CLOSED}={@3} AND {db_cyr}.{dbo}.{INVENTSUM}.{CLOSEDQTY} as {B}.{CLOSEDQTY}={@4}) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT-({db_cyr}.{dbo}.{INVENTDIM}.{I_698DIMIDIDX} AS {C}), SEEK-({C}.{DATAAREAID}={@5} AND {C}.{INVENTDIMID}={db_cyr}.{dbo}.{INVENTSUM}.{INVENTDIMID} as {B}.{INVENTDIMID}), WHERE-({db_cyr}.{dbo}.{INVENTDIM}.{INVENTPROJECTID} as {C}.{INVENTPROJECTID}={@6}) ORDERED FORWARD)

а вот с 1251, который должен быть медленнее, но почему-то быстрее

Цитата:

|--Compute Scalar(DEFINE-({Expr1006}=tertiary_weights({db_1251}.{dbo}.{INVENTTABLE}.{ITEMID} as {A}.{ITEMID})))
|--Stream Aggregate(GROUP BY-({A}.{ITEMID}))
|--Nested Loops(Inner Join, OUTER REFERENCES-({B}.{INVENTDIMID}, {Expr1008}) WITH ORDERED PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES-({A}.{ITEMID}, {Expr1007}) WITH ORDERED PREFETCH)
| |--Clustered Index Seek(OBJECT-({db_1251}.{dbo}.{INVENTTABLE}.{I_175ITEMIDX} AS {A}), SEEK-({A}.{DATAAREAID}={@0}), WHERE-({db_1251}.{dbo}.{INVENTTABLE}.{A_LINECODE} as {A}.{A_LINECODE}={@1}) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT-({db_1251}.{dbo}.{INVENTSUM}.{I_174ITEMDIMIDX} AS {B}), SEEK-({B}.{DATAAREAID}={@2} AND {B}.{ITEMID}={db_1251}.{dbo}.{INVENTTABLE}.{ITEMID} as {A}.{ITEMID}), WHERE-({db_1251}.{dbo}.{INVENTSUM}.{CLOSED} as {B}.{CLOSED}={@3} AND {db_1251}.{dbo}.{INVENTSUM}.{CLOSEDQTY} as {B}.{CLOSEDQTY}={@4}) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT-({db_1251}.{dbo}.{INVENTDIM}.{I_698DIMIDIDX} AS {C}), SEEK-({C}.{DATAAREAID}={@5} AND {C}.{INVENTDIMID}={db_1251}.{dbo}.{INVENTSUM}.{INVENTDIMID} as {B}.{INVENTDIMID}), WHERE-({db_1251}.{dbo}.{INVENTDIM}.{INVENTPROJECTID} as {C}.{INVENTPROJECTID}={@6}) ORDERED FORWARD)

PS: на счет обновления статистики и пр. - перед экспериментами выполнялся sp_createstat, sp_updatestat и на всех таблицах делался REBUILD ALL INDEX с одними и теми же параметрами
Время выполнения определял двумя способами
либо селектами вида

Цитата:

declare @d datetime
set @d =getdate()

select ...
from ...

select db_name(),getdate(), @d, datediff(ms,@d,getdate())

при выключенном вычислении actual-плана
либо включением

SET STATISTICS TIME ON
(давала почти идентичные результаты, что и select db_name(),getdate(), @d, datediff(ms,@d,getdate())

PPS: я тоже был уверен, что скорость должна возрасти, особенно когда первая проверка показала рост скорости в 1000 раз, но вот усредненная реальность подставила подножку... Да и сейчас надеюсь еще найти "волшебный переключатель"

[VitaliyK]

вот еще планы из примеров выше

~300ms

Цитата:

select
*
from SalesPickingListJournalLine isum (nolock)
where PickingListId like '%01Н0002320%'
select db_name(),getdate(), @d, datediff(ms,@d,getdate())
go

Цитата:

|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER REFERENCES-([Bmk1000]))
|--Index Scan(OBJECT-([db_cyr].[dbo].[SALESPICKINGLISTJOURNALLINE].[I_806DELIVERYREQUESTIDX] AS [isum]), WHERE-([db_cyr].[dbo].[SALESPICKINGLISTJOURNALLINE].[PICKINGLISTID] as [isum].[PICKINGLISTID] like '%01Н0002320%'))
|--RID Lookup(OBJECT-([db_cyr].[dbo].[SALESPICKINGLISTJOURNALLINE] AS [isum]), SEEK-([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

~30-40ms:

Цитата:

select
*
from SalesPickingListJournalLine isum (nolock)
where TST_01 like '%01Н0002320%'

Цитата:

|--Compute Scalar(DEFINE-([isum].[TST_01]=[db_cyr].[dbo].[SALESPICKINGLISTJOURNALLINE].[TST_01] as [isum].[TST_01]))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER REFERENCES-([Bmk1000]))
|--Compute Scalar(DEFINE-([isum].[TST_01]=CONVERT(varchar(100),[db_cyr].[dbo].[SALESPICKINGLISTJOURNALLINE].[PICKINGLISTID] as [isum].[PICKINGLISTID],0)))
| |--Index Scan(OBJECT-([db_cyr].[dbo].[SALESPICKINGLISTJOURNALLINE].[I_806DELIVERYREQUESTIDX] AS [isum]), WHERE-(CONVERT(varchar(100),[db_cyr].[dbo].[SALESPICKINGLISTJOURNALLINE].[PICKINGLISTID] as [isum].[PICKINGLISTID],0) like '%01Н0002320%'))
|--RID Lookup(OBJECT-([db_cyr].[dbo].[SALESPICKINGLISTJOURNALLINE] AS [isum]), SEEK-([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

TST_01:
[TST_01] AS ([PickingListId] collate SQL_Latin1_General_CP1251_CI_AS)

[VitaliyK]

вот еще наблюдение

4 селекта:

поиск в короткой строке в родном collation - длительность 630мс

Цитата:

select *
from SalesPickingListJournalLine isum (nolock)
where charindex ( ltrim ( rtrim ( isum.PickingListId ) ) , ',01Н0002320') > 0

поиск в короткой строке в чужем collation - 260мс

Цитата:

select *
from SalesPickingListJournalLine isum (nolock)
where charindex ( ltrim ( rtrim ( isum.PickingListId collate SQL_Latin1_General_CP1251_CI_AS) ) , ',01Н0002320') > 0

поиск в длинной (в 10 раз длиннее) строке в родном collation - 2840мс (рост длительности в 4.5 раза)

Цитата:

select *
from SalesPickingListJournalLine isum (nolock)
where charindex ( ltrim ( rtrim ( isum.PickingListId ) ) , ',01Н0002320,01Н0002321,01Н0002322,01Н0002323,01Н0002324,01Н0002325,01Н0002326,01Н0002327,01Н0002328,01Н0002329') > 0

поиск в длинной (в 10 раз длиннее) строке в чужем collation - 430мс (рост длительности - в 1.5 раза)

Цитата:

select *
from SalesPickingListJournalLine isum (nolock)
where charindex ( ltrim ( rtrim ( isum.PickingListId collate SQL_Latin1_General_CP1251_CI_AS) ) , ',01Н0002320,01Н0002321,01Н0002322,01Н0002323,01Н0002324,01Н0002325,01Н0002326,01Н0002327,01Н0002328,01Н0002329') > 0

--collation сервера, базы и поля таблицы PickingListId - Cyrillic_General_CI_AS

т.е. чем сложнее вычисления в Cyrillic_General_CI_AS тем хуже он в моих экспериментах по сравнению с SQL_Latin1_General_CP1251_CI_AS

[Vadik]

Comparing SQL collations to Windows collations
я все-таки склоняюсь к тому, что не к месту вызванный table scan - бОльшее зло, чем увеличение времени отклика на некоторых запросах

[VitaliyK]

Цитата:

Сообщение от Vadik

Comparing SQL collations to Windows collations
я все-таки склоняюсь к тому, что не к месту вызванный table scan - бОльшее зло, чем увеличение времени отклика на некоторых запросах

Большое спасибо за ссылку!
К сожалению, она подтвердила некоторые предположения и опасения, о том что устаревшие SQL-collation'ы - быстрее Windows-collation'ов при сравнениях и вычислениях за счет более простых правил обработки.

Тему можно считать закрытой.

Жаль, что в моем частном случае суммарный выигрыш от отсутствия table scan оказался меньше, чем проигрыш от более изощренной unicode-обработки строк.