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

Откуда:
Сообщений: 165
Добрый день
можно ли как-то ускорить запрос вида

select COUNT(*) from table1 t1
where exists
(
select * from table2 t2
where (t2.dep = t1.dep or t2.dep = '')
and (t2.acc = t1.acc or t2.acc = '')
)

если сделать запрос ниже, то все выполняется быстро, но одно из полей (t2.dep или t2.acc) может быть пустым, т.е. из первой таблицы нужны все данные, если в таблице 2 это поле пустое. Запрос выше отрабатывает в 2 раза дольше.

select COUNT(*) from table1 t1
where exists
(
select * from table2 t2
where t2.dep = t1.dep
and t2.acc = t1.acc
)
26 сен 17, 11:09    [20822996]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
Добрый Э - Эх
Guest
PetrIII,

переделать exists на join
26 сен 17, 11:14    [20823038]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
PetrIII
Добрый день
можно ли как-то ускорить запрос вида

select COUNT(*) from table1 t1
where exists
(
select * from table2 t2
where (t2.dep = t1.dep or t2.dep = '')
and (t2.acc = t1.acc or t2.acc = '')
)

если сделать запрос ниже, то все выполняется быстро, но одно из полей (t2.dep или t2.acc) может быть пустым, т.е. из первой таблицы нужны все данные, если в таблице 2 это поле пустое. Запрос выше отрабатывает в 2 раза дольше.

select COUNT(*) from table1 t1
where exists
(
select * from table2 t2
where t2.dep = t1.dep
and t2.acc = t1.acc
)
Надо смотреть план и строить индексы. Но, сначала переписать нормально запрос с использованием JOIN. Есть подозрение, что приведённая автором форма записи принудительно навязывает LOOP JOIN.
26 сен 17, 11:18    [20823075]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
PetrIII
Member

Откуда:
Сообщений: 165
было через join, переделал на exists, быстрее стало
26 сен 17, 11:19    [20823084]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
PetrIII
Member

Откуда:
Сообщений: 165
в плане поиск ключа (clustered) на table1 в первом варианте 36%, во втором варианте почти в 2 раза больше
26 сен 17, 11:22    [20823109]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
PetrIII
в плане поиск ключа (clustered) на table1 в первом варианте 36%, во втором варианте почти в 2 раза больше
Далеко не всегда проценты показываются правильно. Значение также имеет, сколько раз этот поиск делается. Если миллионы раз, то вот и тормоза. В данном случае может помочь индекс по dep и acc по обоим таблицам. Будет супер, если он уникальный в обоих случаях. Есть шанс на Merge join. Да и логических чтений будет многократно меньше, если изначально в таблицах много больших полей.
26 сен 17, 11:34    [20823191]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3681
Добрый Э - Эх
PetrIII,

переделать exists на join


Я бы наоборот переделывал
26 сен 17, 12:14    [20823354]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Можно подзапрос в EXISTS переделать в UNION ALL из 4-х SELECTов для возможных комбинаций значений dep и acc.
Чтобы избавиться от гремучей смеси AND и OR (сервер жуть как этого не любит, зато UNION съест с радостью).
26 сен 17, 13:31    [20823679]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
PetrIII
Member

Откуда:
Сообщений: 165
да, пробовал тоже через юнионы, но там возможных условий много, в итоге медленней оказалось.

зато после вот такого варианта

select count(*) from table1 t1
join table2 t2 on t2.field = t1.field

where (t1.field1 = t2.field1 or t2.field1 = '')
and (t1.field2 = t2.field2 or t2.field2 = '')
and (t1.field3 = t2.field3 or t2.field3 = '')

и наката индексов на t1 и t2 по связующим полям запрос стал отрабатывать секунду на 1.7 млн записей

не знаю, можно ли еще ускорить
26 сен 17, 13:55    [20823804]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
aleks222
Guest
PetrIII
да, пробовал тоже через юнионы, но там возможных условий много, в итоге медленней оказалось.

зато после вот такого варианта

select count(*) from table1 t1
join table2 t2 on t2.field = t1.field

where (t1.field1 = t2.field1 or t2.field1 = '')
and (t1.field2 = t2.field2 or t2.field2 = '')
and (t1.field3 = t2.field3 or t2.field3 = '')

и наката индексов на t1 и t2 по связующим полям запрос стал отрабатывать секунду на 1.7 млн записей

не знаю, можно ли еще ускорить


Конечно можно.
Поскольку те явно пофиг на "что под запросом" - просто удали запрос совсем.
Быстрее - не бывает.

ЗЫ. Последний запрос неэквивалентен запросу из первого поста. Ваще никак.
26 сен 17, 14:04    [20823833]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
PetrIII
Member

Откуда:
Сообщений: 165
ну у тебя одна отговорка по всем постам - удалить запрос ))
а вот предложить что-нить дельное фиг там, только пос...ть в комментах можем, да? )
26 сен 17, 14:39    [20824002]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
PetrIII
да, пробовал тоже через юнионы, но там возможных условий много, в итоге медленней оказалось.

зато после вот такого варианта

select count(*) from table1 t1
join table2 t2 on t2.field = t1.field

where (t1.field1 = t2.field1 or t2.field1 = '')
and (t1.field2 = t2.field2 or t2.field2 = '')
and (t1.field3 = t2.field3 or t2.field3 = '')

и наката индексов на t1 и t2 по связующим полям запрос стал отрабатывать секунду на 1.7 млн записей

не знаю, можно ли еще ускорить
Вот, уже хорошо. Дальше надо ФАКТИЧЕСКИЙ план смотреть. В идеале должен быть скан двух индексов и merge join. Важна уникальность индекса по t1.field,t1.field1,t1.field2,t1.field3. Он уникален? Если нет, то merge join свалится в спулинг и будет большой оверхед по I/O. Возможно, смысла в merge join а итоге не будет, и в плане будет loop join со всеми вытекающими.

Правда, вот это or t2.field = '' скорее всего не даст эффективно использовать индекс по t2 и добиться merge join не получится.
27 сен 17, 10:08    [20825850]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Dmitry V. Liseev,

автор
Важна уникальность индекса по t1.field,t1.field1,t1.field2,t1.field3. Он уникален? Если нет, то merge join свалится в спулинг и будет большой оверхед по I/O.

это вообще откуда такое взялось? merge и уникальность...

автор
Возможно, смысла в merge join а итоге не будет, и в плане будет loop join со всеми вытекающими.

ну и возможно в плане смысла в loop не будет и будет hash, все варианты перебрали?
27 сен 17, 10:11    [20825858]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
TaPaK
автор
Важна уникальность индекса по t1.field,t1.field1,t1.field2,t1.field3. Он уникален? Если нет, то merge join свалится в спулинг и будет большой оверхед по I/O.

это вообще откуда такое взялось? merge и уникальность...
http://www.t-sql.ru/post/JoinHints.aspx
MERGE JOIN "один ко многим" всегда будет эффективнее слияния "многие ко многим", поскольку для него не требуется временная таблица. Для того, что бы задействовать слиянием "один ко многим", оптимизатор должен иметь возможность определить, что один из входных потоков состоит из уникальных строк. Как правило, это означает, что у такого входного потока существует уникальный индекс или в плане запроса присутствует явным образом оператор (например, сортировка при DISTINCT или группировка), который гарантирует, что строки на входе будут уникальны.

TaPaK
автор
Возможно, смысла в merge join а итоге не будет, и в плане будет loop join со всеми вытекающими.
ну и возможно в плане смысла в loop не будет и будет hash, все варианты перебрали?
Может и будет. Надо план смотреть, количество записей в обеих таблицах, размер таблиц в мегабайтах, типы данных в полях, по которым индекс, и статистику.
27 сен 17, 10:23    [20825911]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Dmitry V. Liseev,

основной смысл merge это одинаково остортированные поля идексов, уникальность это обычное желание для любого слияния

автор
размер таблиц в мегабайтах

действительно важная характеристика
27 сен 17, 10:30    [20825946]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
TaPaK
основной смысл merge это одинаково остортированные поля идексов, уникальность это обычное желание для любого слияния
Это обычное желание разработчик должен явно обозначать, задавая уникальность индексов. Что помогает серверу выбрать оптимальный план. А если индексы по природе задачи уникальны, но разработчик забил болт сказать об этом серверу, то сервер выберет не оптимальный план. Именно для merge join неуникальность приводит к спулингу в tempdb что сильно увеличивает I/O. Для других слияний я не помню, чтобы уникальность была критична.
TaPaK
автор
размер таблиц в мегабайтах
действительно важная характеристика
Да, безусловно, когда речь идёт про I/O. В таблице все данные подряд, а в индексе только нужные поля. Даже если индекс неправильно отсортирован и речь про его фулскан, чтение индекса и чтение всей таблицы - это совсем разное количество логических чтений. И разница тем больше, чем больше жирных столбцов в таблице. Потому я всегда оцениваю размер таблицы и размер индекса, чтобы понять, даст ли выигрыш создание покрывающего индекса.

В данной задаче индекс по t2 скорее всего использован быть не может в плане поиска по нему из-за этих or t2.field = ''. Потому не важно, как он отсортирован и отсортирован ли вообще. Там можно запросто
CREATE INDEX IX_t2 ON t2 (PK) INCLUDE (field1, field2, field3)
и это ничего не изменит. Тем не менее, с точки зрения I/O всё-таки он меньше места займёт и меньше логических чтений потребует, чем чтение таблицы t2 целиком.
27 сен 17, 10:50    [20826045]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Dmitry V. Liseev,

и таки откуда там возьмётся ваш merge?
27 сен 17, 10:53    [20826063]     Ответить | Цитировать Сообщить модератору
 Re: sql server 2008 r2 Запрос  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
TaPaK
Dmitry V. Liseev,

и таки откуда там возьмётся ваш merge?
Вы пост читали? 20825850
В идеале должен быть скан двух индексов и merge join.
Правда, вот это or t2.field = '' скорее всего не даст эффективно использовать индекс по t2 и добиться merge join не получится.
27 сен 17, 11:14    [20826144]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить