Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Здравствуйте !!!

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4319.0 (X64)
Jun 17 2014 18:50:01
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


Есть запрос T-SQL формирующий данные для отчёта. Отчёт вызывается около 50 раз за день и его формирование занимает ~8мин. и потребляет много серверных аппаратных ресурсов. Стоит задача оптимизировать время выполнение, сделать его быстрее. Ниже привожу T-SQL код и прикрепляю действительный план выполнения.

З.Ы.: естественно я понимаю что это в моём случае очень трудоёмкий процесс и такие работы стоят денег, я просто прошу чтобы гуру опытным глазом не сильно напрягаясь указали мне на проблемные места и я слил эту инфу нашим программистам, сам я не особо сведущ во всём этом ибо ДБА, а программист непосредственно разрабатывавший этот отчёт уволился.
+ T-SQL
declare @StartDate datetime, @EndDate datetime
set @StartDate = '20130101'
set @EndDate = '20150825'

create table #note
(
       co_num nvarchar(20),
       note nvarchar(4000)
)
    DECLARE SelCoCrs CURSOR LOCAL STATIC FOR
select distinct co_num,RowPointer
from
       (
             select order_date, credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, rusdrop_num, rusdrop_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer 
             from co with (nolock) 
             union all 
             select order_date, 0 credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, cust_num, cust_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer 
             from coh with (nolock) 
       ) co
WHERE co.order_date >= @StartDate and co.order_date <= @EndDate and co.type <> 'E'

    OPEN SelCoCrs
       declare @Severity int, @CoNum nvarchar(20), @RowPointer nvarchar(50), @Note nvarchar(4000)
       set @Severity = 0

    WHILE @Severity = 0
    BEGIN
             set @Note = ''
        FETCH SelCoCrs INTO
                    @CoNum,
                    @RowPointer

             IF @@FETCH_STATUS = -1
                    BREAK

             SELECT @Note = @Note + ', ' +
                    case 
                           when Note like 'ATTACHMENT:%' then Description 
                           else [Description] + ' ' + cast([Note] as nvarchar(2000)) 
                    end 
             FROM [zip_work].[dbo].[ReportNotesView] where TableName = 'co' and [RefRowPointer] = @RowPointer
             insert into #note
             select @CoNum co_num, substring(@Note,3,len(@Note)) note
       END

declare @baseitem TABLE 
(
       [item] [nvarchar](15) NOT NULL,
       [Description] [nvarchar](100) NOT NULL,
       [isbase] [int] NOT NULL
)

insert into @baseitem
SELECT [_Code] collate Cyrillic_General_100_CI_AS item
      ,[_Description] [Description]
      ,1 isbase
FROM [s01-sq03].[ui].[dbo].[_Reference171]
where _Folder = 0x01
       and _Fld384 <> 0x00
       and [_OwnerIDRRef] = 0xB636852BC90F1C0541826510D7F6B034 --ЗИП
       and [_Marked] = 0x00
select
       co.order_date [Дата заказа]
,      ci.due_date [Плановая дата]
,      ci.uf_contract_date [Контр. дата]
,      co.CreatedBy [Ответственный]
,      ci.item [Код изделия]
,      (select string from sl_zip.forms_work.dbo.russianstrings where name = 'sItemStatus=' + i.stat) [Статус изделия]
,      i.description [Изделие]
,      ISNULL(t.description,'Реализация') [Тип сделки]
,      ISNULL(ci.lot,'') [Партия]
,      ISNULL(logo.description,'') [Логотип]
,      (select string from sl_zip.forms_work.dbo.russianstrings where name = 'sCoItemStatus=' + ci.stat) [Статус ЗК]
,      ci.co_num [co_num]
,       cust_lcr.confrm_num [Договор]
,      ca.name [Клиент]
,      div_mgr.div_name [Департамент]
,      dept.description [Отдел]
,      new_dept.description [Подразделение]
,      ci.qty_ordered [Кол-во заказано]
,      ci.qty_ordered * ci.price_conv [Сумма заказано]
,      iw.qty_reorder [Страховой запас]
,      ci.qty_shipped [Кол-во отгружено]
,      ci.qty_shipped * ci.price_conv [Сумма отгружено]
,      ci.ship_date [Дата отгружки]
,      ci.qty_ordered - ci.qty_shipped [Колво не отружено]
,      (ci.qty_ordered - ci.qty_shipped) * ci.price_conv [Сумма не отгружено]
,      iw.qty_on_hand [Кол-во в наличии]
,      iw.qty_alloc_co [Кол-во в заказах]
,      iw.qty_on_hand - iw.qty_alloc_co [Наличие с учетом потребности]
,      iw.qty_wip [В производстве]
,      CASE WHEN iw.qty_alloc_co > iw.qty_on_hand + iw.qty_wip THEN iw.qty_alloc_co - iw.qty_on_hand - iw.qty_wip ELSE 0 END [К производству]
,      shipcode.description [Условия поставки]
,      CASE
             WHEN qty_ordered = qty_shipped and qty_packed = qty_shipped THEN 'Отфактурован'
             WHEN qty_ordered = qty_shipped THEN 'Отгружен'
             WHEN qty_shipped > 0 THEN 'Частично'
             WHEN exists (select co_num from   ruscocomplect where co.co_num = ruscocomplect.co_num) THEN 'Выписан'
             ELSE 'Создан'
       END [Факт отгрузки]
,      CASE WHEN co.order_date > CAST('2010-01-01' as datetime) THEN
             DATEDIFF(day, ISNULL(ci.uf_contract_date, ci.due_date), ISNULL(ci.ship_date, GETDATE()))
       ELSE
             DATEDIFF(day, ci.due_date, ISNULL(ci.ship_date, GETDATE()))
       END AS [Просрочено дней]
--,    DATEDIFF(day, ci.due_date, ISNULL(ci.ship_date, GETDATE())) AS [Просрочено дней]
,      co.credit_hold [Заблокирован]
,      ca.credit_hold [Заблокирован клиент]
,      (SELECT TOP 1 case when Note like 'ATTACHMENT:%' then Description else [Description] + ' ' + cast([Note] as nvarchar(2000)) end Note FROM [zip_work].[dbo].[ReportNotesView] where TableName = 'co' and [RefRowPointer] = co.RowPointer) Note
,   n.Note all_note
,ISNULL(ca_drop.name, ca_shipto.name) [Грузополучатель]
,ISNULL(co.est_num,'') [Смета]
,hi.description [Род]
,hi.PT_descr [Вид]
,hi.Class_descr [Класс]
,isnull([isbase],0) isbase
,pp.[UDFINT1] ispp
,пр.Наименование [Проект]
from
       (
             select due_date, item, co_num, qty_ordered, qty_shipped, qty_packed, ship_date, price_conv, stat, uf_contract_date, lot 
             from coitem with (nolock) 
             union all 
             select due_date, item, co_num, qty_ordered, qty_shipped, qty_packed, ship_date, price_conv, stat, due_date, NULL 
             from citemh with (nolock)
       ) ci
join
       item i with (nolock)
on ci.item = i.item
join
       (
             select order_date, credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, rusdrop_num, rusdrop_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer 
             from co with (nolock) 
             union all 
             select order_date, 0 credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, cust_num, cust_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer 
             from coh with (nolock) 
       ) co
on ci.co_num = co.co_num
join
       custaddr ca with (nolock) 
on co.cust_num = ca.cust_num and ca.cust_seq = 0
join
       custaddr ca_shipto with (nolock)
on co.cust_num = ca_shipto.cust_num and ca_shipto.cust_seq = co.cust_seq
left join
       custaddr ca_drop with (nolock)
on co.rusdrop_num = ca_drop.cust_num and co.rusdrop_seq = ca_drop.cust_seq
--***
JOIN
       UserNames with (nolock) 
ON co.CreatedBy = UserNames.username
JOIN
       user_local with (nolock) 
ON UserNames.UserId = user_local.UserId
LEFT JOIN
       employee with (nolock) 
ON user_local.emp_num = employee.emp_num
LEFT JOIN
       dept with (nolock) 
ON employee.dept = dept.dept
LEFT JOIN
       div_mgr with (nolock) 
ON dept.div_num = div_mgr.div_num
LEFT JOIN
       shipcode with (nolock) 
ON co.ship_code = shipcode.ship_code
LEFT JOIN
(
       select
             item
       ,      SUM(qty_reorder) qty_reorder
       ,      SUM(qty_on_hand) qty_on_hand
       ,      SUM(qty_alloc_co) qty_alloc_co
       ,      SUM(qty_wip) qty_wip
       from
             itemwhse with (nolock)
       group by
             item
) iw
ON ci.item = iw.item
left join
       dept new_dept with (nolock) 
on co.charfld1 = new_dept.dept
left join
       lot with (nolock) 
on ci.lot = lot.lot and ci.item = lot.item
left join
       userdefinedtypevalues logo with (nolock) 
on logo.value = lot.charfld2 and logo.typename = 'Логотип'
left join
       trans_nature t with (nolock)
on co.trans_nat = t.trans_nat
left join
       cust_lcr  with (nolock) ON co.cust_num = cust_lcr.cust_num AND co.lcr_num = cust_lcr.lcr_num
left join
       (
             select itemtype 
                       ,max(Par_Class) Par_Class
                       ,max(PC_descr) PC_descr 
                       ,max(Class_descr) Class_descr
                       ,max(PT_descr)  PT_descr
                      ,max(description) description
             from [s01-sq03].[WHSE].[dbo].[H_Item]
             group by itemtype
       ) hi on left(ci.item,9) = hi.itemtype 
left join 
       @baseitem bi on bi.item = i.item collate Cyrillic_General_CI_AS left join
(
       SELECT [RowId]
               ,[UDFINT1]
       FROM [zip_work].[dbo].[UserDefinedFields] with (nolock)
       where TableName = 'co'
             and [UDFINT1] = 1
) pp on pp.[RowId] = co.RowPointer
left join
(
       SELECT пр.Наименование, кс.КодSL, кс.Номер
       FROM [S01-SQ03].[ui].[dbo].Документ_КарточкаСделки AS кс LEFT OUTER JOIN
             [S01-SQ03].[ui].[dbo].Справочник_Проекты AS пр ON кс.Справочник_Проект_Проект = пр.GUIDЭлемента
       where кс.Справочник_Проект_Проект <> 0x00000000000000000000000000000000
             and кс.КодSL <> ''
) пр on пр.КодSL = ci.co_num
left join #note n on n.co_num collate Cyrillic_General_100_CI_AS = ci.co_num
WHERE co.order_date >= @StartDate and co.order_date <= @EndDate and co.type <> 'E'
/*     and co.cust_num LIKE REPLACE(@Customer, '*', '%')
       and ci.item LIKE REPLACE(@Item, '*', '%')
       and new_dept.description in (@dept)
*/
order by
       ci.co_num
,      ci.item

drop table #note



К сообщению приложен файл (ExecutionPlan1.zip - 29Kb) cкачать
25 авг 15, 13:28    [18064413]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2414
stavgreengo,

просто навскидку - убрать курсор, добавить индексы
25 авг 15, 14:00    [18064657]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
вангую.... потому что довольно большой у Вас кусок кода, да и не понятно на каких данных он крутится. может Вам просто нужно на план глянуть и в нужном месте тупо OPTION(RECOMPILE) влепить. статистика тоже решает. а может и такое:

select due_date, item, co_num, ...
from coitem
where ...
union all 
select due_date, item, co_num, ...
from citemh
where ...


с учетом фильтра во временную таблицу залить... а потом из нее все данные и выгребать.

вариантов много, а какой Вам поможет - чистое творчество.
25 авг 15, 14:32    [18064937]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
StarikNavy
stavgreengo,

просто навскидку - убрать курсор, добавить индексы

прогнал запрос через "помощника по настройке ядра СУБД" он выдал рекомендации с предполагаемым общим улучшением в 1%. Я решил что оно того не стоит чтобы изменять структуру индексов и статистики и ничего не предпринял.
25 авг 15, 14:35    [18064960]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
если Вы верите этому тулу, то дальше нет смысла продолжать... вот подумайте, если бы он хорошо работал, зачем нужны были бы специалисты? только зная логику отчета и распределение данные в таблицах можно что-то вразумительное сделать. запустите запрос на выполнение. сделайте одну итерацию в курсоре. посмотрите на план. на больших таблицах из каких индексов читает, если из кластерных, то навскидку, может не стоит из них... а лучше добавить индекс другой, более легкий.

временные таблицы тоже решают. уже об этом написал выше.
25 авг 15, 14:41    [18065007]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
o-o
Guest
когда много соединений, может помочь обработка по частям.
сджойнить сначала только половину, а то и вовсе одну треть, вставить это во временную таблицу.
потом остальное присоединить.

еще вот это дважды вычитывается:
       (
             select order_date, credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, rusdrop_num, rusdrop_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer 
             from co with (nolock) 
             union all 
             select order_date, 0 credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, cust_num, cust_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer 
             from coh with (nolock) 
       ) co
WHERE co.order_date >= @StartDate and co.order_date <= @EndDate and co.type <> 'E'

можно это во временную таблицу прочесть,
из нее сначала для курсора вычитать,
а потом к этой временной таблице и присоединять остальное
25 авг 15, 15:06    [18065225]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
stavgreengo
Ниже привожу T-SQL код и прикрепляю действительный план выполнения.
План невалидный.

Приведите правильный план (и со статистикой выполнения).
25 авг 15, 15:09    [18065238]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
o-o
можно это во временную таблицу прочесть,
Я бы даже сделал временную таблицу из
from
       (
             select due_date, item, co_num, qty_ordered, qty_shipped, qty_packed, ship_date, price_conv, stat, uf_contract_date, lot 
             from coitem with (nolock) 
             union all 
             select due_date, item, co_num, qty_ordered, qty_shipped, qty_packed, ship_date, price_conv, stat, due_date, NULL 
             from citemh with (nolock)
       ) ci
join
       item i with (nolock)
on ci.item = i.item
join
       (
             select order_date, credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, rusdrop_num, rusdrop_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer 
             from co with (nolock) 
             union all 
             select order_date, 0 credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, cust_num, cust_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer 
             from coh with (nolock) 
       ) co
on ci.co_num = co.co_num
join
       custaddr ca with (nolock) 
on co.cust_num = ca.cust_num and ca.cust_seq = 0
join
       custaddr ca_shipto with (nolock)
on co.cust_num = ca_shipto.cust_num and ca_shipto.cust_seq = co.cust_seq
+ с условием по дате, причём блок co написать первым, а условия по дате переместить внутрь запросов union all

Если этот запрос будет медленный, то смотрел бы, почему (можно кусками), и пробовал варианты его изменения.

И в любом случае нужен актуальный план, а не оценочный.
25 авг 15, 15:40    [18065494]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
alexeyvg
stavgreengo
Ниже привожу T-SQL код и прикрепляю действительный план выполнения.
План невалидный.

Приведите правильный план (и со статистикой выполнения).

Сорри, прикрепляю правильный. Под статистикой выполнения не совсем понял. Её надо собрать profiler, как именно ?

К сообщению приложен файл (ExecutionPlan.zip - 29Kb) cкачать
25 авг 15, 15:50    [18065574]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
JOIN должен делаться по ID пользователя, а не по имени

ON co.CreatedBy = UserNames.username
25 авг 15, 15:54    [18065620]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
o-o
Guest
не вижу особо выдающихся таблиц,
по-моему, просто "тонет" в куче соединений.
еще там remote query:
       (
             select itemtype 
                       ,max(Par_Class) Par_Class
                       ,max(PC_descr) PC_descr 
                       ,max(Class_descr) Class_descr
                       ,max(PT_descr)  PT_descr
                      ,max(description) description
             from [s01-sq03].[WHSE].[dbo].[H_Item]
             group by itemtype
       ) hi 

это [s01-sq03].[WHSE].[dbo].[H_Item] у вас постоянно меняется?
а то скачайте это на свой сервер, зачем лезть еще куда-то.
или хотя бы 1 раз в день себе это закачивайте, а все 50 отчетов пусть смотрят на эту
раз в день обновляемую локальную таблицу

еще там настоятельно советуют создать индекс
USE [zip_work]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[co] ([type],[order_date])
INCLUDE ([co_num],[RowPointer])
GO
25 авг 15, 16:23    [18065953]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
o-o
не вижу особо выдающихся таблиц,
по-моему, просто "тонет" в куче соединений.
еще там remote query:
       (
             select itemtype 
                       ,max(Par_Class) Par_Class
                       ,max(PC_descr) PC_descr 
                       ,max(Class_descr) Class_descr
                       ,max(PT_descr)  PT_descr
                      ,max(description) description
             from [s01-sq03].[WHSE].[dbo].[H_Item]
             group by itemtype
       ) hi 

это [s01-sq03].[WHSE].[dbo].[H_Item] у вас постоянно меняется?
а то скачайте это на свой сервер, зачем лезть еще куда-то.
или хотя бы 1 раз в день себе это закачивайте, а все 50 отчетов пусть смотрят на эту
раз в день обновляемую локальную таблицу

еще там настоятельно советуют создать индекс
USE [zip_work]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[co] ([type],[order_date])
INCLUDE ([co_num],[RowPointer])
GO

Индекс добавил. Таблицы с другого сервера перенёс в базу zip_work, но напоролся на
Сообщение 468, уровень 16, состояние 9, строка 205
Не удалось разрешить конфликт параметров сортировки между "Cyrillic_General_100_CI_AS" и "Cyrillic_General_CI_AS" в операции equal to.

в строчке
...) hi on left(ci.item,9) = hi.itemtype 
left join 
       @baseitem bi on bi.item = i.item collate Cyrillic_General_CI_AS left join
( ...

видно по этому запрос был к удалённому серверу. Оба сервера подключены к одной циске по гигабитному каналу.
С одним лишь вновь добавленным индексом никакого увеличения производительности не наблюдаю, кэш естественно чистил перед запросом
25 авг 15, 17:03    [18066277]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
stavgreengo
Под статистикой выполнения не совсем понял. Её надо собрать profiler, как именно ?
Вот видите, у вас везде в плане везде Estimated? Это значит, что в плане не показаны результаты выполнения отдельных операторов.
Допустим, Estimated Number of Rows равен 1000000, а реально будет одна строка.

Нужно, что бы кроме Estimated, появились строки Actual (данные о реальном исполнении шагов плана, становятся известны серверу после выполнения запроса)

Для этого нужно в профайлер добавить событие Showplan XML Statististics Profile
o-o
не вижу особо выдающихся таблиц,
по-моему, просто "тонет" в куче соединений.
Ну, там непонятно, на чём тонет.
Может как раз из за удалённого запроса, может, где то один из джойнов кривой (не по тому полю, или индекс отсутствует)

Вот и посмотреть бы актуальный план, плюс разбиение запроса на куски...
25 авг 15, 17:05    [18066296]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
stavgreengo
Таблицы с другого сервера перенёс в базу zip_work, но напоролся на
Сообщение 468, уровень 16, состояние 9, строка 205
Не удалось разрешить конфликт параметров сортировки между "Cyrillic_General_100_CI_AS" и "Cyrillic_General_CI_AS" в операции equal to
Ну, можно привести коллейшен к правильному в запросе (тем более, что джойн у вас по LEFT), можно преобразовывать при импорте.

Вот у вас уже есть такое преобразование, в следующем джойне.

Но между прочим такие преобразования, равно как и функции, уменьшают производительность, т.к. становится невозможным использовать индексы.
25 авг 15, 17:09    [18066316]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
o-o
Guest
stavgreengo
Индекс добавил. Таблицы с другого сервера перенёс в базу zip_work, но напоролся на
Сообщение 468, уровень 16, состояние 9, строка 205
Не удалось разрешить конфликт параметров сортировки между "Cyrillic_General_100_CI_AS" и "Cyrillic_General_CI_AS" в операции equal to.

в строчке
...) hi on left(ci.item,9) = hi.itemtype 
left join 
       @baseitem bi on bi.item = i.item collate Cyrillic_General_CI_AS left join
( ...

видно по этому запрос был к удалённому серверу. Оба сервера подключены к одной циске по гигабитному каналу.

нет, у вас ошибка в соединении не с тем, что c удаленного идет запросом (hi),
а в соединении i (item) с @baseitem, к-ому насильно прописали Cyrillic_General_100_CI_AS.

зачем прописали это Cyrillic_General_100_CI_AS?
у вас в базе какой коллэйшен, Cyrillic_General_CI_AS?
insert into @baseitem
SELECT [_Code] collate Cyrillic_General_100_CI_AS item
      ,[_Description] [Description]
      ,1 isbase
FROM [s01-sq03].[ui].[dbo].[_Reference171]
where _Folder = 0x01
       and _Fld384 <> 0x00
       and [_OwnerIDRRef] = 0xB636852BC90F1C0541826510D7F6B034 --ЗИП
       and [_Marked] = 0x00

сразу приведя к нужному Cyrillic_General_100_CI_AS item,
a из запроса уберите collate Cyrillic_General_CI_AS
25 авг 15, 17:22    [18066398]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Здравствуйте, коллеги !!!

Итак, что было сделано, убрана работа через временную таблицу #note. Средние выполнение скрипта уменьшилось с 7:49мин. до 7:10мин.(что всё равно не достаточно ) Так же были эксперименты с переносом всей информации с удалённого сервера s01-sq03 в одну локальную БД zip_work в которой и происходила вся работа, абсолютно никаких существенных результатов это не дало, поэтому вернул всё как было(плодить избыточную информацию мне ни к чему). Ниже прилагаю запрос в том виде в котором запускаю его сейчас. И план выполнения с Showplan XML Statistics Profile
+ T-SQL
declare @StartDate datetime, @EndDate datetime
set @StartDate = '20130101'
set @EndDate = '20150827'

declare @baseitem TABLE 
(
       [item] [nvarchar](15) NOT NULL,
       [Description] [nvarchar](100) NOT NULL,
       [isbase] [int] NOT NULL
)

insert into @baseitem
SELECT [_Code] item
      ,[_Description] [Description]
      ,1 isbase
FROM [s01-sq03].[ui].[dbo].[_Reference171]
where _Folder = 0x01
       and _Fld384 <> 0x00
       and [_OwnerIDRRef] = 0xB636852BC90F1C0541826510D7F6B034 --ЗИП
       and [_Marked] = 0x00

select
       co.order_date [Дата заказа]
,      ci.due_date [Плановая дата]
,      ci.uf_contract_date [Контр. дата]
,      co.CreatedBy [Ответственный]
,      ci.item [Код изделия]
,      (select string from sl_zip.forms_work.dbo.russianstrings where name = 'sItemStatus=' + i.stat) [Статус изделия]
,      i.description [Изделие]
,      ISNULL(t.description,'Реализация') [Тип сделки]
,      ISNULL(ci.lot,'') [Партия]
,      ISNULL(logo.description,'') [Логотип]
,      (select string from sl_zip.forms_work.dbo.russianstrings where name = 'sCoItemStatus=' + ci.stat) [Статус ЗК]
,      ci.co_num [co_num]
,       cust_lcr.confrm_num [Договор]
,      ca.name [Клиент]
,      div_mgr.div_name [Департамент]
,      dept.description [Отдел]
,      new_dept.description [Подразделение]
,      ci.qty_ordered [Кол-во заказано]
,      ci.qty_ordered * ci.price_conv [Сумма заказано]
,      iw.qty_reorder [Страховой запас]
,      ci.qty_shipped [Кол-во отгружено]
,      ci.qty_shipped * ci.price_conv [Сумма отгружено]
,      ci.ship_date [Дата отгружки]
,      ci.qty_ordered - ci.qty_shipped [Колво не отружено]
,      (ci.qty_ordered - ci.qty_shipped) * ci.price_conv [Сумма не отгружено]
,      iw.qty_on_hand [Кол-во в наличии]
,      iw.qty_alloc_co [Кол-во в заказах]
,      iw.qty_on_hand - iw.qty_alloc_co [Наличие с учетом потребности]
,      iw.qty_wip [В производстве]
,      CASE WHEN iw.qty_alloc_co > iw.qty_on_hand + iw.qty_wip THEN iw.qty_alloc_co - iw.qty_on_hand - iw.qty_wip ELSE 0 END [К производству]
,      shipcode.description [Условия поставки]
,      CASE
             WHEN qty_ordered = qty_shipped and qty_packed = qty_shipped THEN 'Отфактурован'
             WHEN qty_ordered = qty_shipped THEN 'Отгружен'
             WHEN qty_shipped > 0 THEN 'Частично'
             WHEN exists (select co_num from   ruscocomplect where co.co_num = ruscocomplect.co_num) THEN 'Выписан'
             ELSE 'Создан'
       END [Факт отгрузки]
,      CASE WHEN co.order_date > CAST('2010-01-01' as datetime) THEN
             DATEDIFF(day, ISNULL(ci.uf_contract_date, ci.due_date), ISNULL(ci.ship_date, GETDATE()))
       ELSE
             DATEDIFF(day, ci.due_date, ISNULL(ci.ship_date, GETDATE()))
       END AS [Просрочено дней]
--,    DATEDIFF(day, ci.due_date, ISNULL(ci.ship_date, GETDATE())) AS [Просрочено дней]
,      co.credit_hold [Заблокирован]
,      ca.credit_hold [Заблокирован клиент]
,      (SELECT TOP 1 case when Note like 'ATTACHMENT:%' then Description else [Description] + ' ' + cast([Note] as nvarchar(2000)) end Note FROM [zip_work].[dbo].[ReportNotesView] where TableName = 'co' and [RefRowPointer] = co.RowPointer) Note
,   n.Note all_note
,ISNULL(ca_drop.name, ca_shipto.name) [Грузополучатель]
,ISNULL(co.est_num,'') [Смета]
,hi.description [Род]
,hi.PT_descr [Вид]
,hi.Class_descr [Класс]
,isnull([isbase],0) isbase
,pp.[UDFINT1] ispp
,пр.Наименование [Проект]
from
       (
             select due_date, item, co.co_num, qty_ordered, qty_shipped, qty_packed, ship_date, price_conv, co.stat, uf_contract_date, lot 
             from coitem with (nolock) join
                    co with (nolock) on co.co_num = coitem.co_num
             where co.order_date >= @StartDate and co.order_date <= @EndDate and co.type <> 'E'
             union all 
             select due_date, item, coh.co_num, qty_ordered, qty_shipped, qty_packed, ship_date, price_conv, coh.stat, due_date, NULL 
             from citemh with (nolock) join
                    coh with (nolock)  on coh.co_num = citemh.co_num
             where coh.order_date >= @StartDate and coh.order_date <= @EndDate and coh.type <> 'E'
       ) ci
join
       item i with (nolock)
on ci.item = i.item
join
       (
             select order_date, credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, rusdrop_num, rusdrop_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer 
             from co with (nolock) 
             where co.order_date >= @StartDate and co.order_date <= @EndDate and co.type <> 'E'
             union all 
             select order_date, 0 credit_hold, co_num, lcr_num, est_num, cust_num, cust_seq, cust_num, cust_seq, createdby, ship_code, charfld1, type, trans_nat, RowPointer 
             from coh with (nolock) 
             where coh.order_date >= @StartDate and coh.order_date <= @EndDate and coh.type <> 'E'
       ) co
on ci.co_num = co.co_num
join
       custaddr ca with (nolock) 
on co.cust_num = ca.cust_num and ca.cust_seq = 0
join
       custaddr ca_shipto with (nolock)
on co.cust_num = ca_shipto.cust_num and ca_shipto.cust_seq = co.cust_seq
left join
       custaddr ca_drop with (nolock)
on co.rusdrop_num = ca_drop.cust_num and co.rusdrop_seq = ca_drop.cust_seq
--***
JOIN
       UserNames with (nolock) 
ON co.CreatedBy = UserNames.username
JOIN
       user_local with (nolock) 
ON UserNames.UserId = user_local.UserId
LEFT JOIN
       employee with (nolock) 
ON user_local.emp_num = employee.emp_num
LEFT JOIN
       dept with (nolock) 
ON employee.dept = dept.dept
LEFT JOIN
       div_mgr with (nolock) 
ON dept.div_num = div_mgr.div_num
LEFT JOIN
       shipcode with (nolock) 
ON co.ship_code = shipcode.ship_code
LEFT JOIN
(
       select
             item
       ,      SUM(qty_reorder) qty_reorder
       ,      SUM(qty_on_hand) qty_on_hand
       ,      SUM(qty_alloc_co) qty_alloc_co
       ,      SUM(qty_wip) qty_wip
       from
             itemwhse with (nolock)
       group by
             item
) iw
ON ci.item = iw.item
left join
       dept new_dept with (nolock) 
on co.charfld1 = new_dept.dept
left join
       lot with (nolock) 
on ci.lot = lot.lot and ci.item = lot.item
left join
       userdefinedtypevalues logo with (nolock) 
on logo.value = lot.charfld2 and logo.typename = 'Логотип'
left join
       trans_nature t with (nolock)
on co.trans_nat = t.trans_nat
left join
       cust_lcr  with (nolock) ON co.cust_num = cust_lcr.cust_num AND co.lcr_num = cust_lcr.lcr_num
left join
       (
             select itemtype 
                       ,max(Par_Class) Par_Class
                       ,max(PC_descr) PC_descr 
                       ,max(Class_descr) Class_descr
                       ,max(PT_descr)  PT_descr
                      ,max(description) description
             from [s01-sq03].[WHSE].[dbo].[H_Item]
             group by itemtype
       ) hi on left(ci.item,9) = hi.itemtype 
left join 
       @baseitem bi on bi.item = i.item collate Cyrillic_General_CI_AS left join
(
       SELECT [RowId]
               ,[UDFINT1]
       FROM [zip_work].[dbo].[UserDefinedFields] with (nolock)
       where TableName = 'co'
             and [UDFINT1] = 1
) pp on pp.[RowId] = co.RowPointer
left join
(
       SELECT пр.Наименование, кс.КодSL, кс.Номер
       FROM [S01-SQ03].[ui].[dbo].Документ_КарточкаСделки AS кс LEFT OUTER JOIN
             [S01-SQ03].[ui].[dbo].Справочник_Проекты AS пр ON кс.Справочник_Проект_Проект = пр.GUIDЭлемента
       where кс.Справочник_Проект_Проект <> 0x00000000000000000000000000000000
             and кс.КодSL <> ''
) пр on пр.КодSL = ci.co_num
left join [co_notes] n on n.co_num collate Cyrillic_General_CI_AS = ci.co_num
WHERE co.order_date >= @StartDate and co.order_date <= @EndDate and co.type <> 'E'
/*     and co.cust_num LIKE REPLACE(@Customer, '*', '%')
       and ci.item LIKE REPLACE(@Item, '*', '%')
       and new_dept.description in (@dept)
*/
order by
       ci.co_num
,      ci.item

Над чем работать дальше ?

К сообщению приложен файл (trace.zip - 13Kb) cкачать
26 авг 15, 11:44    [18069282]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
stavgreengo
Над чем работать дальше ?
Ну, шаги ранее расписали.

Например, выделить основной кусок, и попробовать его выполнять отдельно, со вставкой во временную таблицу, как я выше написал. С ним будет проще разбираться, да и вероятность, что сервер запутается в планах, меньше.

Вообще, можно увидеть, что по некоторым таблицам (и по "co" в частности!) плохая статистика - сервер оценивает количество записей в "co" как 1 (по условию), а потом получает 20338.

Таблица #note и курсор у вас времени не занимало, так что можно было на это силы и не тратить.
26 авг 15, 12:33    [18069672]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли какие-нибудь варианты оптимизации запроса T-SQL ???  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
stavgreengo,

Краткое резюме по вашему плану:

1. 37 (!) соединений в плане (может, обсчитался, но порядок ясен)
2. Преобразования типов в условиях соединений
3. Несколько таблиц совсем без индексов
4. Запросы к удаленным серверам
5. Явно недостает индексов
6. Возможно устаревшая статистика
7. Около 1.2 ГБ грант памяти

Все это приводит к тормозам.

Что можно сделать.

1. Однозначно разбирать запрос на части! Как это сделать лучше виднее вам, посмотрите, какие части можно поместить во временные таблицы, потому, что даже если удастся другими средствами «вытянуть» план, то не факт, что он будет оставаться таким же и дальше. Может наблюдаться очень нестабильное поведение. Даже человеку трудно читать текст этого запроса.
2. Сделайте правильные коллэйшны на этапе создания таблиц, а не на этапе соединения.
3. На таблицах UserDefinedFields, @baseitem, co_notes, NoteHeaders – добавьте правильные индексы (например, по полям соединения или еще как-то, возможно кластерные или просто покрывающие – сами смотрите – запрос сложный анализировать индексы за вас вряд ли кто-то будет)
4. При запросах к удаленным серверам сервер часто не может использовать статистику, посмотрите самый первый RemoteQuery, там ожидается 562 247 строк, а реально 159 строк. От этого, весь дальнейший план может иметь неправильную форму и плюс огромный грант памяти.
5. Посмотрите, какие поля достаются при помощи Key Lookup, добавьте их в индексы, вообще, посмотрите, у вас куча недооцененных сортировок (ожидается 1 431 строка, реально 84 079) – каждая такая сортировка требует памяти, которая выделяется исходя из числа предполагаемых строк, если реально их больше и памяти не хватило – может происходить слив данных в tempdb. Подумайте, можно ли добавить индексы где-то по полям сортировки, чтобы ее избежать. Сортировки нужны для Merge Join у вас в основном, может ыть – это не самый удачный тип соединения? Хорошие оценки и разбиение запроса также помогут это исправить.
6. Как минимум обновите статистику по таблице co, а еще лучше по всем таблицам. Разберитесь, почему предикат co.order_date >= @StartDate and co.order_date <= @EndDate and co.type <> 'E' дает оценку в 367 строк, тогда как реально их 20338. Статистика? Локальные переменные? Может быть, поместить этот поиск во временную таблицу? Может быть option(recompile).
7. Грант памяти станет меньше, как только уйдет переоценка строк, сортировки и неправильные типы соединений.

Кроме того, кругом nolock – это не хорошая практика. Кроме того есть Assert-ы, которые проверяют что подзапрос вернул одно значение, на производительность это может не оказывает существенного влияния, но это тоже дурной тон.

В общем, самый правильный путь – это сделать полный рефакторинг запроса. Да, это нужно будет разбираться с логикой и вникнуть, что, наверное, лениво и не хочется – зато даст самый хороший результат. Запрос ужасен не только с точки зрения оптимизатора, но и с точки зрения простого разработчика. Как такое чудо поддерживать, как искать ошибки, как модифицировать логику, если потребуется. А на месте оптимизатора, я бы просто отказался оптимизировать такой запрос =)
26 авг 15, 12:47    [18069785]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить