перевод SQL 2000 на SQL 2005

[glibs]

Забавно. У меня запрос сваливается в сканирование таблицы InventDim если не стоит OPTION(FAST 1). В противном случае план запроса хороший. Из формы запрос уходит с OPTION(FAST 1) у меня. А у вас?

Вы не игрались с хинт-флагами в конфигурационной утилите случайно?

Поэкспериментировал. Насколько я могу понимать 2005-й не находит Аксаптовский DimIdIdx селективным и пытается сканировать таблицу. Принудительное указание хинта на этот индекс у меня проблему сканирования таблицы решает.

Приложение Аксапта 3.0 сп3, клиент 3.0 сп5 кр3. База на порядки меньше вашей, правда. Но другой под рукой нет.

В общем, для начала выясните, уходит ли у вас OPTION(FAST 1). Если да, то поэкспериментируйте с индекс-хинтами в SQLQuery.

[SHiSHok]

начал осуществлять перевод базы с 2000sp4 -> 2005sp4 и сразу же проблема тормозов при открытии формы. немого поковырял, пока выяснил вот что:
открываем форму SysTraceSQL и делаем сортировку по полю Дата (CreatedDate). Сортировка выполняется в таблице SysTraceTableSQL с кластерным индексом CreatedTypeIdx (первое поле индекса createdDate).
Имеем: в sql2000 - сортировка занимает до 1 сек (в таблице порядка 77000 записей), в sql2005 - не менее 4 сек (при том что железо sql2005 значительно превосходит железо sql2000 - 5 лет разницы).
Идем в SQL Profiler. имеем следующий сценарий исполнения:

PHP код:

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
set @p5=0x5010
declare @p6 int
set @p6=0x2001
declare @p7 int
set @p7=0

exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(1000)',N'SELECT A.TRACETIME,A.CATEGORY,A.CODE,A.CONNECTIONTYPE,A.CONNECTIONSPID,A.CONNECTIONID,A.ISLOBSINCLUDED,A.ISMOREDATAPENDING,A.ROWSAFFECTED,A.ROWSIZE,A.ROWSPERFETCH,A.ISSELECTEDFORUPDATE,A.ISSTARTEDWITHINTRANSACTION,A.STATEMENTTYPE,A.STATEMENTID,A.STATEMENTREUSECOUNT,A.DETAILTYPE,A.CREATEDDATE,A.CREATEDTIME,A.CREATEDBY,A.RECVERSION,A.RECID,A.TRACECONTAINER,A.STATEMENT,A.CALLSTACK,A.TEXT,A.TEXTDETAILS FROM SYSTRACETABLESQL A WHERE (DATAAREAID=@P1) ORDER BY A.DATAAREAID DESC,A.CREATEDDATE DESC OPTION(FAST 1)',@p5 output,@p6 output,@p7 output,'dat'

select @p1 as prepared_handle, @p2 as [cursor], @p5 as scrollopt, @p6 as ccopt, @p7 as [rowcount] 


прошу заметить что никаких хинтов, только orderby по полям индекса и fast 1. исполняем на движке 2000 и 2005, результат тот же: 2000- мгновенно, 2005- 4 сек.
Разбираемся дальше:

запускаем запрос из stmt:

PHP код:

declare @P1 varchar(1000)
set @p1 = 'dat'
SELECT A.TRACETIME,A.CATEGORY,A.CODE,A.CONNECTIONTYPE,A.CONNECTIONSPID,A.CONNECTIONID,A.ISLOBSINCLUDED,A.ISMOREDATAPENDING,A.ROWSAFFECTED,A.ROWSIZE,A.ROWSPERFETCH,A.ISSELECTEDFORUPDATE,A.ISSTARTEDWITHINTRANSACTION,A.STATEMENTTYPE,A.STATEMENTID,A.STATEMENTREUSECOUNT,A.DETAILTYPE,A.CREATEDDATE,A.CREATEDTIME,A.CREATEDBY,A.RECVERSION,A.RECID,A.TRACECONTAINER,A.STATEMENT,A.CALLSTACK,A.TEXT,A.TEXTDETAILS FROM SYSTRACETABLESQL A WHERE (DATAAREAID=@P1) ORDER BY A.DATAAREAID DESC,A.CREATEDDATE DESC OPTION(FAST 1) 


конечно же новый sql 2005 выполняет его гораздо быстрее. Но вот планы у них совсем разные:
sql2000:

PHP код:

  |--Compute Scalar(DEFINE:([A].[TEXTDETAILS]=[A].[TEXTDETAILS], [A].[TEXT]=[A].[TEXT], [A].[CALLSTACK]=[A].[CALLSTACK], [A].[STATEMENT]=[A].[STATEMENT], [A].[TRACECONTAINER]=[A].[TRACECONTAINER]))
       |--Clustered Index Seek(OBJECT:([FramDEV].[dbo].[SYSTRACETABLESQL].[I_1608CREATEDTYPEIDX] AS [A]), SEEK:([A].[DATAAREAID]=[@P1]) ORDERED BACKWARD) 


sql2005:

PHP код:

  |--Sort(ORDER BY:([A].[CREATEDDATE] DESC, [Expr1002] DESC))
       |--Compute Scalar(DEFINE:([Expr1002]=tertiary_weights([FramMAIN].[dbo].[SYSTRACETABLESQL].[DATAAREAID] as [A].[DATAAREAID])))
            |--Clustered Index Seek(OBJECT:([FramMAIN].[dbo].[SYSTRACETABLESQL].[I_1608CREATEDTYPEIDX] AS [A]), SEEK:([A].[DATAAREAID]=[@P1]) ORDERED FORWARD) 


в плане sql2005 наблюдаем оператор tertiary_weights который конвертит varchar(3) поле из коллейшна "SQL_Ukrainian_CP1251_CI_AS" (у нас такой стоит). Как я понимаю sql2005 будет конвертить коллейшены указанные в доке по tertiary_weights при работы с текстовыми полями.
Если коллейшн базы(таблицы) изменить, например, на "Ukrainian_CI_AS" (я создал новую базу и перегнал данные), то по запросу имеем план:

PHP код:

  |--Clustered Index Seek(OBJECT:([TEST].[dbo].[SYSTRACETABLESQL].[I_1608CREATEDTYPEIDX] AS [A]), SEEK:([A].[DATAAREAID]=[@P1]) ORDERED BACKWARD) 


возвращается к сценарию с sp_cursorprepexec: время исполнения также как и sql2000 - менее секунды.

так что вывод: перед переводом базы на 2005й проверьте свой collation по списку в tertiary_weights.

[Vadik]

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

[SHiSHok]

статья и скрипт для смены collation БД

[EVGL]

Цитата:

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

статья и скрипт для смены collation БД

Чтоб я сдох... Этот Коваленко - просто супер-рюх!