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

Откуда: Екб
Сообщений: 1206
Доброго времени суток!
В новой процедуре, после уже проведенной оптимизации вижу, что остается один запрос стоимостью порядка 75 % от всей процедуры. В плане данного запроса основная стоимость (80%) - это джойн к моей временной выборке таблицы с техпроцессом.

-- примерная упрощенная структура
create table #Temp ( ord_id int, tree_id int, tp_id int, .... ) -- времянка, в ней несколько тыс. позиций.

create table TP( tp_id int identity, ord_id int, tree_id int, oper_id int .... ) -- тут около пары десятков млн записей.

В качестве условия в джойне #Temp.ord_id=TP.ord_id and #Temp.tp_id=TP.tp_id, в выборку беру oper_id.

Смутило то, что при наличии кластерного индекса на TP по полям (ord_id, tp_id) оптимизатор выполняет "просмотр диапазона строк в некластерном индексе" внутри другого индекса, где индексировано по ord_id, а tp_id, oper_id и несколько неключевых полей в разделе include.
Поставил хинт для поиска по кластерному индексу - получил практически то же время выполнения и процент в плане. Причем в плане "просмотр диапазона строк в кластерном индексе". Джойн идет именно по тем полям, по которым кластерный индекс, почему просмотр диапазона? Внутри ord_id может быть довольно много tp_id и я ожидал некоего прироста при использовании кластерного индекса, чего я не учитываю, почему оптимизатор оказался прав?



P.S. Есть какое то средство позволяющее при сохранении удобочитаемости обезобразить план запроса, когда не уверен, что стр-у базы можно показывать?

P.S.S. Есть ли возможность быстро оценить, что некоторый код оптимизировать в дальнейшем не имеет смысла, т.е. при данной архитектуре быстрее уже не будет (естественно когда подсказок об отсутствующих индексах, фуллсканов в плане и т.д. уже нет) ?
1 июн 16, 15:40    [19246262]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Сохраните актуальный план выполнения в sqlplan формате и покажите всем нам. Тогда и будет понятно в какую сторону двигаться.

Обезображивать план запроса не нужно, если хотите чтобы Вам помогли (в противном случае бесплатный Plan Explorer).
1 июн 16, 15:50    [19246345]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
если у вас кластерный по (ord_id, tp_id) а в соединяете по tp_id, то кластерный подхватит только если другие не влезут(с)Иа :)
структуру индексов не пересказом можно?
1 июн 16, 15:55    [19246379]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
denis_viktorovich
Member

Откуда: Екб
Сообщений: 1206
TaPaK
если у вас кластерный по (ord_id, tp_id) а в соединяете по tp_id, то кластерный подхватит только если другие не влезут(с)Иа :)
структуру индексов не пересказом можно?


Джойн по обоим полям, хотя [TP_ID] уникален и в общем достаточно только по нему.

-- игнорируемый или частично используемый
ALTER TABLE [dbo].[TP] ADD CONSTRAINT [PK_TP] PRIMARY KEY CLUSTERED
(
[TP_ID] ASC,
[ORD_ID] ASC
)

-- предпочитаемый оптимизатором
CREATE NONCLUSTERED INDEX [IX_TP_23] ON [dbo].[TP]
(
[ORD_ID] ASC
)
INCLUDE (
[TP_ID],
[TR_ID],
[STP_ID],
[OPERPOS],
[OPER_ID],
[OBOR_ID],
[TRUD1],
[TRUD2]
)
1 июн 16, 16:26    [19246592]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
denis_viktorovich
[TP_ID] уникален
А зачем тогда
denis_viktorovich
ALTER TABLE [dbo].[TP] ADD  CONSTRAINT [PK_TP] PRIMARY KEY CLUSTERED 
(
	[TP_ID] ASC,
	[ORD_ID] ASC
)
1 июн 16, 20:41    [19247613]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
denis_viktorovich
Member

Откуда: Екб
Сообщений: 1206
iap
denis_viktorovich
[TP_ID] уникален
А зачем тогда
denis_viktorovich
ALTER TABLE [dbo].[TP] ADD  CONSTRAINT [PK_TP] PRIMARY KEY CLUSTERED 
(
	[TP_ID] ASC,
	[ORD_ID] ASC
)


Не мной создавалось, но могу предположить, что поскольку во многих режимах выборка из клиента идет именно по [ORD_ID], его добавили в кластерный индекс. Правда не уверен есть ли смысл в таком решении, ведь по каждому значению [TP_ID] всегда только один [ORD_ID].
2 июн 16, 09:14    [19248403]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
denis_viktorovich,

обновите статистики и посмотрите поменялся ли план. И да без плана можно гадать сколько угодно
2 июн 16, 09:20    [19248418]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
denis_viktorovich
ведь по каждому значению [TP_ID] всегда только один [ORD_ID].
Мало того, если поиск только по [ORD_ID], то этот индекс использоваться не будет вовсе!
А вот сделать FK на эту таблицу неоправданно сложнее. Всюду тащить этот [ORD_ID] придётся.
2 июн 16, 10:18    [19248648]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
denis_viktorovich
Member

Откуда: Екб
Сообщений: 1206
AlanDenton
Сохраните актуальный план выполнения в sqlplan формате и покажите всем нам. Тогда и будет понятно в какую сторону двигаться.

Обезображивать план запроса не нужно, если хотите чтобы Вам помогли (в противном случае бесплатный Plan Explorer).


Воспользовался PlanExplorer, первый запрос это просто наполнение таблички для подзапроса, а второй - проблемный.
Названия конечно меняются не в сторону читаемости, но то что в разделе Seek Predicate названо ScalarString26 и ScalarString23, в оригинале выглядело как Scalar Operator(ORD_ID) и Scalar Operator(TP_ID).
Смутило появление Scalar Operator, т.к. поле ORD_ID в обеих таблицах определено как (Int Not null), а TP_ID в первой таблице как (Int, Not null) а во второй (Int, null).

К сообщению приложен файл (измененный план_1.zip - 7Kb) cкачать
3 июн 16, 08:42    [19252902]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
С оценкой бардак, ошибка на порядок...вам бы статистику всё-таки обновить...тогда логичнее делать сканы на ваших мульонах, а не поиск по индексу, который сиквел делает ожидая пару тысяч записей
3 июн 16, 09:53    [19253131]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
denis_viktorovich
Member

Откуда: Екб
Сообщений: 1206
Knyazev Alexey,

Ну да, ожидаемое количество строк 18 000 а реальное 800 000. После обновления статистики проверил, расхождение то же. Практически та же цифра....
3 июн 16, 12:57    [19254437]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
denis_viktorovich
Member

Откуда: Екб
Сообщений: 1206
Knyazev Alexey,
выполнилось, за 18 минут, но картинка не поменялась

UPDATE STATISTICS TP WITH FULLSCAN
3 июн 16, 13:00    [19254448]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
option (recompile)


не меняет поведение?
3 июн 16, 13:07    [19254505]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
denis_viktorovich
Member

Откуда: Екб
Сообщений: 1206
Knyazev Alexey,

Меняет, ожидаемое стало больше предполагаемого, процент по стоимости остался тот же.

К сообщению приложен файл (измененный план_2.zip - 7Kb) cкачать
3 июн 16, 13:39    [19254744]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
denis_viktorovich
процент по стоимости остался тот же.

вы особо на этот показатель не ориентируйтесь, гораздо интереснее, как изменяется время выполнения запроса и утилизация ресурсов
3 июн 16, 14:19    [19255039]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
denis_viktorovich
Member

Откуда: Екб
Сообщений: 1206
Knyazev Alexey,

Общее время выполнения сократилось. Я проделал следующее, добавил некластерный индекс на ORD_ID, TP_ID с полем OPER_ID в секции INCLUDE. Оптимизатор сам его подхватил, стоимость данного поиска упала до 12% от всего запроса, IO упало 56->43, CPU - выросло с 2.9 до 23.8, при этом общее время выросло на пару секунд и появилась затратная сортировка(23%). Попытаюсь осмыслить, пока не понял почему так.
3 июн 16, 14:34    [19255135]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
denis_viktorovich
Member

Откуда: Екб
Сообщений: 1206
Собственно почему я прицепился к этому запросу. Там много проблемных мест включая выборку нарядов по некоему параметру представляющему из себя дату , хранящуюся в VARCHAR(50), по диапазону дат и т.д. Все это удалось привести к довольно быстрому варианту. А основная задержка получается, когда уже все выбрано и практически надо приджойнить табличку по ключевым полям, по которым она фактически и упорядочена.
3 июн 16, 14:45    [19255174]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить