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

[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

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

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

[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