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

Есть две таблицы

create table A (id1 int not null, id2 int not null, valueA numeric(15, 2), 
                    PRIMARY KEY CLUSTERED(Id1, Id2)
)

create table B (id1 int not null, id2 int not null, valueB int, 
                    PRIMARY KEY CLUSTERED(Id1, Id2)
)


В таблице A около 30000 записей, в таблице B - 50 000 000

Пишу

select 
       B.id1
     , B.id2
     , sum(A.valueA * B.value2)
from 
       A
       inner join B on A.id1 = B.id1 and A.id2 = B.id2     
group by 
       B.id1, B.id2
order by
       B.id1, B.id2


Если строчки sum(A.valueA * B.value2) нет, выполняется где-то за 30 сек, если ее добавить - 12 минут. В первом случае работает поиск по индексу, во втором - индекс скан и в плане вижу что 77% приходится на "вложенные циклы" с диким количеством записей.

Вопрос: что это и как бороться?
27 дек 16, 20:43    [20054192]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20581
Добавьте поля с валуями в индекс.
27 дек 16, 20:59    [20054245]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
НиколайМ2
Guest
Akina,

а зачем их индексить, если поиск не по ним? джойн идет по ид, валуи должны только перемножаться и суммироваться, уже найденные
27 дек 16, 21:44    [20054424]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
НиколайМ2
В первом случае работает поиск по индексу, во втором - индекс скан и в плане вижу что 77% приходится на "вложенные циклы" с диким количеством записей.
Поиск где? Что-то вы недоговариваете.
В общем, планы нужно показывать (в формате sqlplan), а не пересказывать своими словами.

Зачем группировка по (id1, id2) и суммирование, если эти столбцы PK в обеих таблицах?
27 дек 16, 22:12    [20054545]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
НиколайМ2
Guest
invm,

поиск по большой таблице
план не могу сейчас, он остался на работе :)
27 дек 16, 22:50    [20054716]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
НиколайМ2
поиск по большой таблице
Информативно...

А гадать на кофейной гуще непродуктивно.
Например, для запроса без суммирования, оптимизатор может задействовать самый узкий индекс из B. А при суммировании не может и вынужден использовать самый узкий, где есть valueB. Если такового нет, то берется кластерный. И если B широкая, - можно получить существенную разницу в производительности.

Группировку с агрегированием, кстати, оптимизатор из вашего запроса выкинет.
27 дек 16, 23:14    [20054769]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
НиколайМ2
Guest
invm
НиколайМ2
поиск по большой таблице
Информативно...

А гадать на кофейной гуще непродуктивно.
Например, для запроса без суммирования, оптимизатор может задействовать самый узкий индекс из B. А при суммировании не может и вынужден использовать самый узкий, где есть valueB. Если такового нет, то берется кластерный. И если B широкая, - можно получить существенную разницу в производительности.



Так зачем все же индекс по valueB, если нужные строки ищутся по id1, id2?
valueA, valueB в индекс действительно не входят

invm
Группировку с агрегированием, кстати, оптимизатор из вашего запроса выкинет.


почему?
27 дек 16, 23:26    [20054783]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
НиколайМ2
Так зачем все же индекс по valueB, если нужные строки ищутся по id1, id2?
По valueB самостоятельный индекс не нужен.
Исходя из вашего примера вообще никакие дополнительные индексы не нужны. Но в реале таблицы-то у вас наверняка другие.
НиколайМ2
почему?
Потому что (id1, id2) PK в обеих таблицах. Группировать нечего.
28 дек 16, 00:02    [20054854]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
НиколайМ2
Guest
invm
НиколайМ2
Так зачем все же индекс по valueB, если нужные строки ищутся по id1, id2?
По valueB самостоятельный индекс не нужен.
Исходя из вашего примера вообще никакие дополнительные индексы не нужны. Но в реале таблицы-то у вас наверняка другие.


В реале да. Сейчас посмотрел.
Таблица А - индекс по id1 и еще паре полей (но не id2)

Таблица B - индекс по id1, id2

invm
НиколайМ2
почему?
Потому что (id1, id2) PK в обеих таблицах. Группировать нечего.


Понятно, затупил.
28 дек 16, 09:09    [20055191]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
НиколайМ2
Guest
Включил valueB в индекс. План поменялся, тепер по B index seek и время выполнения стало 30 секунд.

Может кто-нибудь потратить минутку времени и объяснить все-таки, почему агрегируемое поле нужно в индексе, если join по нему не происходит?
28 дек 16, 09:21    [20055220]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31427
НиколайМ2
Может кто-нибудь потратить минутку времени и объяснить все-таки, почему агрегируемое поле нужно в индексе, если join по нему не происходит?
Ну его же нужно взять для агрегирования, правильно? Откуда его взять?
Если его в индексе нет, то либо нужно сделать лукап, то есть отдельным запросом его получить, либо сервер вообще будет считать, что нужно сделать скан таблицы.

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

Кстати, включить в индекс лучше сделать, используя INCLUDE, а не добавление в список полей инедкса.
28 дек 16, 09:39    [20055254]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
НиколайМ2
Guest
alexeyvg
НиколайМ2
Может кто-нибудь потратить минутку времени и объяснить все-таки, почему агрегируемое поле нужно в индексе, если join по нему не происходит?
Ну его же нужно взять для агрегирования, правильно? Откуда его взять?
Если его в индексе нет, то либо нужно сделать лукап, то есть отдельным запросом его получить, либо сервер вообще будет считать, что нужно сделать скан таблицы.

Спасибо, теперь понял.

alexeyvg
Кстати, включить в индекс лучше сделать, используя INCLUDE, а не добавление в список полей инедкса.


Почитал msdn, но так и не понял: если значения этих include-столбцов все равно копируются в индекс, чем они физически отличаются от обычных столбцов?
28 дек 16, 10:30    [20055409]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
НиколайМ2,

индекс не зависит от полей include и на сам индекс не влиеят
28 дек 16, 10:33    [20055416]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
написал конечно достаточно грубо :) а то сейчас все поля в include засунете.... читайте
https://sqlperformance.com/2014/07/sql-indexes/new-index-columns-key-vs-include
28 дек 16, 10:36    [20055429]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
НиколайМ2
Guest
TaPaK, спасибо, посмотрю

Все бы не включил :) Здесь https://msdn.microsoft.com/ru-ru/library/ms190806(v=sql.90).aspx
в рнкомендациях четко сказано, что столбцы, участвующие в поиске, должны быть в индексе
28 дек 16, 10:51    [20055484]     Ответить | Цитировать Сообщить модератору
 Re: Добавление агрегатной функции тормозит group by  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31427
НиколайМ2
alexeyvg
Кстати, включить в индекс лучше сделать, используя INCLUDE, а не добавление в список полей инедкса.


Почитал msdn, но так и не понял: если значения этих include-столбцов все равно копируются в индекс, чем они физически отличаются от обычных столбцов?

Они будут в индексе на последнем уровне, но их не будет в дереве, т.е. искать по этим данным нельзя, но сервер для других целей их будет брать прямо из индекса.
28 дек 16, 17:53    [20057752]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить