PHP DM Gate v0.9 » MSSQL. Посоветуйте индексы на таблицу <span class="vd">[D7, MSSQL]</span>

AV © (19.10.12 11:31) [0]

Таблицы большая. Широкая и длинная. Индексов сейчас никаких.
Запрос к ней один, ниже.
Каждый день добавляются строки (несколько десятков тысяч)
Каждый день получаю письмо, где написано время начала и конца запроса.
В первый день запрос идет несколько минут
Потом по нарастающей. К концу месяца не укладывается в ночь.
В начале следующего месяца таблица очищается до 0.
Задача - уложится в ночь.
Запрос не мой, что он делает - не знаю.
Как думаю - надо "правильно" навесить индексы.

SELECT
MAX(t.[ANI]) Calling,
RIGHT(MAX(case when t.[CallDisposition] = 15 then t.[DigitsDialed] else null end), 10) Called,
(select
max(ct.[TalkTime])
from filials_PureDD ct
where ct.RecoveryKey = MAX(t.RecoveryKey))
- SUM(case
when t.[CallDisposition] = 15 then (t.[TalkTime]+t.[DelayTime])
when t.[CallDisposition] in (28,29) then t.[TalkTime]
else 0 end
) Duration,
MAX(case when t.[CallDisposition] = 15 then t.[DateTime] else null end) BeginCall,
(select top 1 id_sct from ccc.dbo.sct where CallTypeID = MAX(t.[CallTypeID])) ID_SCT,
MAX(t.[CallTypeID]) [CallTypeID],
1 as StringForWrite
FROM filials_PureDD t
join filials_PureDD tt
on tt.RouterCallKeyDay = t.RouterCallKeyDay and tt.RouterCallKey = t.RouterCallKey

where t.[DateTime] >= :DTF
and tt.[DateTime] <= :DTS
and t.RouterCallKey <> 0
and t.RouterCallKeyDay <> 0
and tt.RouterCallKey <> 0
and tt.RouterCallKeyDay <> 0
and tt.CallDisposition=15
group by t.RouterCallKeyDay, t.RouterCallKey

<Цитата>
AV © (19.10.12 11:52) [1]

DateTime datetime
RouterCallKey int
RouterCallKeyDay int
CallTypeID int
TalkTime datetime
ANI varchar(32)

<Цитата>
RWolf © (19.10.12 11:56) [2]

ну, напрашиваются индексы на t.RouterCallKeyDay, t.RouterCallKey, раз уж группировка по ним идёт.
индекс на CallDisposition тоже не помешает.

<Цитата>
AV © (19.10.12 12:13) [3]

>> напрашиваются индексы на t.RouterCallKeyDay, t.RouterCallKey,
тоже думаю
а какие? простые?

+ я пока на DateTime простой ASC повесил

проблема еще в том, что сейчас почти "начало месяца"(там свое исчисление) и таблица почти пуста, едва сотня тысяч записей есть

<Цитата>
AV © (19.10.12 12:24) [4]

повесил
что имеею (если облажается - не виноват)
|--Compute Scalar(DEFINE:([Expr1012]=right([Expr1007],(10)), [Expr1018]=[Expr1016]-[Expr1009], [ccc].[dbo].[sct].[id_sct]=[ccc].[dbo].[sct].[id_sct], [Expr1024]=(1)))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([Expr1011]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([Expr1008]))
| |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [Expr1045]=(0) THEN NULL ELSE [Expr1046] END))
| | |--Stream Aggregate(GROUP BY:([t].[RouterCallKey], [t].[RouterCallKeyDay]) DEFINE:([Expr1006]=MAX([ccc].[dbo].[filials_PureDD].[ANI] as [t].[ANI]), [Expr1007]=MAX([Expr1027]), [Expr1008]=MAX([ccc].[dbo].[filials_PureDD].[RecoveryKey] as [t].[RecoveryKey]), [Expr1045]=COUNT_BIG([Expr1028]), [Expr1046]=SUM([Expr1028]), [Expr1010]=MAX([Expr1029]), [Expr1011]=MAX([ccc].[dbo].[filials_PureDD].[CallTypeID] as [t].[CallTypeID])))
| | |--Sort(ORDER BY:([t].[RouterCallKey] ASC, [t].[RouterCallKeyDay] ASC))
| | |--Filter(WHERE:([ccc].[dbo].[filials_PureDD].[RouterCallKeyDay] as [tt].[RouterCallKeyDay]=[ccc].[dbo].[filials_PureDD].[RouterCallKeyDay] as [t].[RouterCallKeyDay] AND [ccc].[dbo].[filials_PureDD].[DateTime] as [tt].[DateTime]<=getdate()-'1900-01-02 00:00:00.000' AND [ccc].[dbo].[filials_PureDD].[CallDisposition] as [tt].[CallDisposition]=(15) AND [ccc].[dbo].[filials_PureDD].[RouterCallKeyDay] as [tt].[RouterCallKeyDay]<>(0)))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003], [Expr1044]) WITH UNORDERED PREFETCH)
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[RouterCallKey]) OPTIMIZED)
| | | |--Compute Scalar(DEFINE:([Expr1027]=CASE WHEN [ccc].[dbo].[filials_PureDD].[CallDisposition] as [t].[CallDisposition]=(15) THEN [ccc].[dbo].[filials_PureDD].[DigitsDialed] as [t].[DigitsDialed] ELSE NULL END, [Expr1028]=CASE WHEN [ccc].[dbo].[filials_PureDD].[CallDisposition] as [t].[CallDisposition]=(15) THEN [ccc].[dbo].[filials_PureDD].[TalkTime] as [t].[TalkTime]+[ccc].[dbo].[filials_PureDD].[DelayTime] as [t].[DelayTime] ELSE CASE WHEN [ccc].[dbo].[filials_PureDD].[CallDisposition] as [t].[CallDisposition]=(29) OR [ccc].[dbo].[filials_PureDD].[CallDisposition] as [t].[CallDisposition]=(28) THEN [ccc].[dbo].[filials_PureDD].[TalkTime] as [t].[TalkTime] ELSE (0) END END, [Expr1029]=CASE WHEN [ccc].[dbo].[filials_PureDD].[CallDisposition] as [t].[CallDisposition]=(15) THEN [ccc].[dbo].[filials_PureDD].[DateTime] as [t].[DateTime] ELSE NULL END))
| | | | |--Filter(WHERE:(([ccc].[dbo].[filials_PureDD].[RouterCallKey] as [t].[RouterCallKey]<(0) OR [ccc].[dbo].[filials_PureDD].[RouterCallKey] as [t].[RouterCallKey]>(0)) AND ([ccc].[dbo].[filials_PureDD].[RouterCallKeyDay] as [t].[RouterCallKeyDay]<(0) OR [ccc].[dbo].[filials_PureDD].[RouterCallKeyDay] as [t].[RouterCallKeyDay]>(0))))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
| | | | |--Index Seek(OBJECT:([ccc].[dbo].[filials_PureDD].[IX_filials_PureDD_DateTime] AS [t]), SEEK:([t].[DateTime] >= getdate()) ORDERED FORWARD)
| | | | |--RID Lookup(OBJECT:([ccc].[dbo].[filials_PureDD] AS [t]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([ccc].[dbo].[filials_PureDD].[IX_filials_PureDD_RouterCallKey] AS [tt]), SEEK:([tt].[RouterCallKey]=[ccc].[dbo].[filials_PureDD].[RouterCallKey] as [t].[RouterCallKey]), WHERE:([ccc].[dbo].[filials_PureDD].[RouterCallKey] as [tt].[RouterCallKey]<>(0)) ORDERED FORWARD)
| | |--RID Lookup(OBJECT:([ccc].[dbo].[filials_PureDD] AS [tt]), SEEK:([Bmk1003]=[Bmk1003]) LOOKUP ORDERED FORWARD)
| |--Stream Aggregate(DEFINE:([Expr1016]=MAX([ccc].[dbo].[filials_PureDD].[TalkTime] as [ct].[TalkTime])))
| |--Table Scan(OBJECT:([ccc].[dbo].[filials_PureDD] AS [ct]), WHERE:([ccc].[dbo].[filials_PureDD].[RecoveryKey] as [ct].[RecoveryKey]=[Expr1008]))
|--Top(TOP EXPRESSION:((1)))
|--Index Seek(OBJECT:([ccc].[dbo].[sct].[IX_CallType]), SEEK:([ccc].[dbo].[sct].[CallTypeID]=[Expr1011]) ORDERED FORWARD)

<Цитата>
картман © (19.10.12 19:49) [5]

- SUM(case
when t.[CallDisposition] = 15 then (t.[TalkTime]+t.[DelayTime])
when t.[CallDisposition] in (28,29) then t.[TalkTime]
else 0 end
) Duration

я б вычисляемое поле сделал

<Цитата>
знайка (19.10.12 19:56) [6]

А Advisor что говорит?

<Цитата>
Kerk © (19.10.12 22:15) [7]

http://sqloptimizersqlserver.inside.quest.com/ попробуй :)

<Цитата>
AV © (22.10.12 08:57) [8]

> Advisor что говорит?

Да тоже и говорит, только больше индексов хочет
НО, имхо, пока нет его смысла слушать. Данных мало, и что он выберет, то не будет оптимальным к концу месяца.

и по той же причине, не доверяю

> Kerk © (19.10.12 22:15) [7]

Тут надо эмпирически понять, прочувствовать объемы вперед

пока имею
старт всегда в 0:25:00
20.10.2012 0:28:59 Экспорт закончен
21.10.2012 0:29:37 Экспорт закончен
22.10.2012 - сегодня данных нет, БД отвалилась
(PureDD - это данные с удаленной БД, сегодня линк свалился)

Пока результаты сравнимы с тем, что было

<Цитата>
AV © (22.10.12 09:02) [9]

> PureDD - это данные с удаленной БД,

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

<Цитата>
AV © (22.10.12 09:04) [10]

> - SUM(case
> when t.[CallDisposition] = 15 then (t.[TalkTime]+t.
> [DelayTime])
> when t.[CallDisposition] in (28,29) then t.[TalkTime]
> else 0 end
> ) Duration
>
> я б вычисляемое поле сделал

меня смущают магические цифры
что-то поменяется - потом не вспомнишь
а пока их видно явно

<Цитата>
MsGuns © (22.10.12 10:46) [11]

Если еще не все забыл.. Мы вроде избегали конструкции типа Select ...,select
заменяя их на select ... join (select ...) ...

<Цитата>
AV © (22.10.12 12:49) [12]

> MsGuns © (22.10.12 10:46) [11]

да..
и вообще тяжело понимать конструкции типа Select ...,select
но работает, заказчика устраивает - лучше не трогать..
сейчас не дай bug что сломается
..табла вообще 3 раза сама с собой пересекается, это не нормально, по-логике то

и вообще боюсь начинать разбираться что там написано..
> select ... from ccc.dbo.sct
> Эт куда, что там? дока есть?
> Вот только ничего не спрашивай
> Я не знаю
> запрос их программа выполняет.
> писал им Костя перед увольнением
> Свяжись с ним, может пояснит

Проще подойти к вопросу механически и выполнить тз в рамках постановки - экспорт должен заканчиваться до 8-00. Пусть так и будет. Не будет - буду разбираться далее.

<Цитата>
stone (22.10.12 14:57) [13]

> from filials_PureDD ct
> where ct.RecoveryKey = MAX(t.RecoveryKey))
> - SUM(case
> when t.[CallDisposition] = 15 then (t.[TalkTime]+t.
> [DelayTime])
> when t.[CallDisposition] in (28,29) then t.[TalkTime]
>
> else 0 end

С такими конструкциями индекс не поможет

<Цитата>
AV © (23.10.12 03:42) [14]

Stop at 23.10.2012 0:32:10
в таблице 1182684 записей

<Цитата>
AV © (24.10.12 08:24) [15]

Stop at 24.10.2012 0:32:42
в таблице 1437130 записей

А может и пойдет все
+30 секунд, на +250 000 записей

тз из двух частей, правда, было
минимум озвучен
максимум - всегда в таблице есть записи за последний месяц + неделя
т.е. за последние примерно 40 дней.
Сегодня день 7ой, запрос идет примерно 7 минут

<Цитата>
wicked © (24.10.12 11:09) [16]

я бы подошел так:
1 - в начале, середине и в конце "периода" сделать копию таблицы - для того, чтобы можно было прогнать запросы на разных обьемах

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

3 - оптимизировать запрос до одурения
- убрать лишний join на tt (или обосновать для себя, зачем он нужен);
- поубирать выражения и вызовы функций в секции where - по ним оптимизатор не может применить индекс, даже если он есть;
- проставить индекса на все поля, которые участвуют в join-ах и where
- возможно, проставить filtered index (что то такое в mssql 2008 выдало по запросу conditional index) для тех полей, где много неиспользованых значений (нули в RouterCallKey и RouterCallKeyDay)

<Цитата>
stone (24.10.12 11:49) [17]

> - проставить индекса на все поля, которые участвуют в join-
> ах и where

Есть такая полезная вещь - план выполнения запроса. Хорошо бы ее изучить, прежде, чем создавать индексы на все поля что ни попадя.

<Цитата>
AV © (24.10.12 12:00) [18]

а в сабже, скорее расчет по средневзвешенной
есть такая.
Хотя, имхо, спорная. Но так работают.

<Цитата>
AV © (24.10.12 12:09) [19]

не сюда :(

Спасибо.

Придется, видимо, понимать что там и зачем.

Или выделить еще одну таблицу, с данными 2-3 дней, по которой будет идти запрос этого отчета.
А все остальные данные, обозвать архивные, и хранить в другой таблице.
И сделать вьюшку, как union этих таблиц

<Цитата>
AV © (24.10.12 12:18) [20]

т.е. мысль разовью.

where t.[DateTime] >= :DTF
and tt.[DateTime] <= :DTS

:DTF и :DTS
по логу, постоянно требуется за последние сутки
реже, двое суток.
Пусть это будет оперативная таблица
Или Оперативная пусть будет как сейчас, неделя.

Остальное пусть в архивной будет.
Вьюшку обозвать как таблица называется сейчас. Кто захочет покопаться - пусть копается. Внешне разницы не будет.
Так даже можно за 2-3 месяца хранить.

<Цитата>