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

Откуда:
Сообщений: 19
Есть база, в ней есть таблица, хранящая статистику. В таблице есть поля UID, PID, UCount, EventTime. PID - ID родителя, записи образуют дерево. Сама таблица хранит статистику числа UCount для каждого UID, каждый час в ней создаётся копия дерева из основной таблицы с выставленным EventTime и посчитанным UCount. Для вывода статистики для текущего и всех его сыновей был написан такой запрос (это запрос для корня дерева, UCount для него всегда равен 0):

WITH rec_c (UCount, PID, UID, EventTime) AS (
    SELECT c.UCount, c.PID, c.UID, c.EventTime FROM stats AS c WHERE c.PID = @UID AND c.EventTime BETWEEN @StartDay AND @EndDay 
    UNION ALL
    SELECT c.UCount, c.PID, c.UID, c.EventTime FROM stats AS c
        INNER JOIN rec_c ON rec_c.UID = c.PID AND rec_c.EventTime = c.EventTime
)
SELECT EventTime, 0 AS UCount, SUM(UCount) AS recUCount FROM rec_c GROUP BY EventTime ORDER BY EventTime DESC

План выполнения запроса:

            |--Compute Scalar(DEFINE:([Expr1015]=(0)))
                 |--Sort(ORDER BY:([Recr1013] DESC))
                      |--Hash Match(Aggregate, HASH:([Recr1013]), RESIDUAL:([Recr1013] = [Recr1013]) DEFINE:([Expr1014]=SUM([Recr1010])))
                           |--Index Spool(WITH STACK)
                                |--Concatenation
                                     |--Compute Scalar(DEFINE:([Expr1018]=(0)))
                                     |    |--Table Scan(OBJECT:([maindb].[dbo].[stats] AS [c]), WHERE:([maindb].[dbo].[stats].[PID] as [c].[PID]=[@UID] AND [maindb].[dbo].[stats].[EventTime] as [c].[EventTime]>=[@StartDay] AND [maindb].[dbo].[stats].[EventTime] as [c].[EventTime]<=[@EndDay]))
                                     |--Assert(WHERE:(CASE WHEN [Expr1020]>(100) THEN (0) ELSE NULL END))
                                          |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1020], [Recr1006], [Recr1007], [Recr1008], [Recr1009]))
                                               |--Compute Scalar(DEFINE:([Expr1020]=[Expr1019]+(1)))
                                               |    |--Table Spool(WITH STACK)
                                               |--Index Spool(SEEK:([c].[PID]=[Recr1008] AND [c].[EventTime]=[Recr1009]))
                                                    |--Table Scan(OBJECT:([maindb].[dbo].[stats] AS [c]))

Актуальный план выполнения сообщает, что 85% времени выполнения занимает Index Spool (Eager Spool). В таблице существует некластеризованный индекс по UID, в порядке эксперимента поочерёдно добавлялись кластеризованный индекс по EventTime, некластеризованный индекс по PID, некластеризованный индекс по PID + EventTime. Заметного прироста производительности не наблюдалось.
Итак, 2 вопроса:
1) какие индексы помогут быстрее выполнять данный запрос
2) (вытекает из первого) какие конкретно индексы нужны оптимизатору (он ведь перестраивает их для временной таблицы) и где это посмотреть
16 сен 09, 21:00    [7670116]     Ответить | Цитировать Сообщить модератору
 Re: Создание оптимальных индексов  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Попробуйте натравить DTA на свой запрос.
17 сен 09, 06:23    [7670831]     Ответить | Цитировать Сообщить модератору
 Re: Создание оптимальных индексов  [new]
Нектотам
Guest
si14
Итак, 2 вопроса:
1) какие индексы помогут быстрее выполнять данный запрос
2) (вытекает из первого) какие конкретно индексы нужны оптимизатору (он ведь перестраивает их для временной таблицы) и где это посмотреть

Попробуйте кластеризованный уникальный по UID и некластеризованный неуникальный по двум полям - PID, EventTime.
17 сен 09, 06:28    [7670834]     Ответить | Цитировать Сообщить модератору
 Re: Создание оптимальных индексов  [new]
Dan Khatskevich
Member

Откуда:
Сообщений: 324
tpg,

DTA не всегда правильно показывает. Недавно убедился сам, что созданые мной индексы работают бытрее чем его рекомендации.
17 сен 09, 08:33    [7670920]     Ответить | Цитировать Сообщить модератору
 Re: Создание оптимальных индексов  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Dan Khatskevich
tpg,

DTA не всегда правильно показывает. Недавно убедился сам, что созданые мной индексы работают бытрее чем его рекомендации.
Никто и не спорит.
Просто с помощью DTA можно быстрее набраться опыта в этом деле.
17 сен 09, 09:12    [7671009]     Ответить | Цитировать Сообщить модератору
 Re: Создание оптимальных индексов  [new]
si14
Member

Откуда:
Сообщений: 19
1) что такое DTA?
2) значение UID не является уникальным, я же писал об этом. Уникальной является пара значений UID - время, а сам UID повторяется каждый час. Кроме того, вариант с индексом по UID + индексом по EventTime, PID я пробовал, Index Spool по-прежнему осуществлялся.
17 сен 09, 09:16    [7671023]     Ответить | Цитировать Сообщить модератору
 Re: Создание оптимальных индексов  [new]
Dan Khatskevich
Member

Откуда:
Сообщений: 324
si14,

в MSSQL Studio есть кнопочка Analyze Query in Database Engine Tuning Advisor. Веделяеете свой запрос тыкаете на нее, там выбераее базу на корой этот запрос выполняется, и кликаете Start Analisis.
в результате он должен выдать на ксолько процентов можно убыстрить этот запрос и скрипт по созданию индексов и статистик.
17 сен 09, 09:36    [7671078]     Ответить | Цитировать Сообщить модератору
 Re: Создание оптимальных индексов  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
1. Database Engine Tuning Advisor - помощник по настройке ядра СУБД.

Просится индекс с ключем (PID, EventTime) и включенными столбцами (UCount, UID), а также индекс с ключем (UID, EventTime) с включенными (UCount, PID).
17 сен 09, 09:37    [7671079]     Ответить | Цитировать Сообщить модератору
 Re: Создание оптимальных индексов  [new]
si14
Member

Откуда:
Сообщений: 19
Создал индексы
tpg
с ключем (PID, EventTime) и включенными столбцами (UCount, UID), а также индекс с ключем (UID, EventTime) с включенными (UCount, PID)

Это помогло, теперь 97% времени занимает Index Seek. Однако, грустно, что время выполнения запроса упало всего на ~20%. В таблице 603 тысячи записей и она растёт. Запрос выполняется сейчас около 10 секунд и возвращает около 600 значений. Можно ли каким либо образом дополнительно ускорить выполнение запроса?
17 сен 09, 21:43    [7676136]     Ответить | Цитировать Сообщить модератору
 Re: Создание оптимальных индексов  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Вы DDL таблиц и всех их индексов покажите...
Да и план, после создания индексов тоже.
18 сен 09, 06:33    [7676568]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить