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

Откуда: Москва
Сообщений: 109
Помогите гуру. Что то подкинул бизнес задачку...
Запрос:
SELECT  TA.UNIQ, SA.STOREID, CONVERT(date, SA.DB, 4) AS DOC_DATE, SA.CASHCODE, SA.SHIFT
       ,TA.CHECKNUM, TA.BCODE, TA.ARTICUL
       ,TA.VATRATE1, TA.VATCODE1, va.NAME as VATCODENAME
       ,SUM(TA.VATSUM1 * OP.[SIGN]) AS VATSUM1 
       ,SUM(TA.BQUANT  * OP.[SIGN]) AS QTY_SUM
          ,SUM(TA.BQUANT * TA.PRICE * OP.[SIGN]) AS SUM_1
          ,SUM(TA.SUMB * OP.[SIGN]) AS SUMI
FROM dbo.ACS AS SA 
INNER JOIN  dbo.ACT AS TA 
       ON TA.CSID  = SA.UNIQ
INNER JOIN  dbo.OP AS OP 
       ON TA.OPCODE = OP.OPCODE
LEFT JOIN  dbo.VAT AS VA
       on VA.CODE = TA.VATCODE1
WHERE  (SA.DB >=  CONVERT(date,DATEADD(DAY, -3, getdate())) AND   SA.DB  < CONVERT(date, GETDATE())) 
AND   SA.STOREID > 3
GROUP BY  TA.UNIQ, SA.STOREID, CONVERT(date, SA.DB, 4), SA.CASHCODE, SA.SHIFT
         ,TA.CHECKNUM, TA.BCODE, TA.ARTICUL
         ,TA.VATRATE1, TA.VATCODE1, va.NAME;


Таблицы ACS, OP и VAT - маленькие. Таблица ACT - большая, более 1.3млрд записей. Задача выбрать данные по ней за 3 дня, ну от текущей даты скажем. Почему то происходит Table Scan этой большой таблицы ACT, хотя по идее - не должно. Этот скан, разумеется, дико замедляет запрос, перебрать миллиард записей то...
Куда рыть, не совсем понимаю, оптимизатор говорит про неоптимальный Hash Match, дескать Probe Residual, неявное преобразование и т.д. Но то неважно, соединить тысячи строк, это же не миллиард перебрать...
План запроса больше 150 кб, залил на гугл диск: https://drive.google.com/open?id=17wE-6Dtu-g_ehTgAk3SiWucVj1DgLXRF
22 май 18, 15:23    [21429306]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
отбери во времянку с ACT все подходящее под твое условие
WHERE (SA.DB >= CONVERT(date,DATEADD(DAY, -3, getdate())) AND SA.DB < CONVERT(date, GETDATE()))
AND SA.STOREID > 3
вот ето все обьяви в переменную до выборки
CONVERT(date,DATEADD(DAY, -3, getdate()))

,а уж патом джойнь и группируй

ЗЫ план не читал не дотсупен
22 май 18, 15:28    [21429334]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
982183
Member

Откуда: VL
Сообщений: 3349
Возьми MIN(SA.UNIQ) с правильными датами, а потом выбери все TA с CSID большим чем она.
22 май 18, 15:38    [21429375]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
JohnAl
Почему то происходит Table Scan этой большой таблицы ACT
Потому что hast match и таблица на внутреннем входе.
Индекс нужен подходящий для dbo.ACT
22 май 18, 16:00    [21429453]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
JohnAl
Member

Откуда: Москва
Сообщений: 109
Maxx, план расшарил.
А почему в переменную, в чем тут тайный смысл?
WHERE то работает по маленькой таблице
22 май 18, 16:06    [21429480]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
JohnAl
Member

Откуда: Москва
Сообщений: 109
invm, индексы для ACT есть по полю CSID и по полю UNIQ. Но оба - некластеризованные
22 май 18, 16:07    [21429491]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
vborets
Member

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

В АСТ есть дата? фильтруй по ней в джойне
22 май 18, 16:13    [21429527]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
JohnAl
Member

Откуда: Москва
Сообщений: 109
vborets, не вариант
Грубо схема. ACS - заголовок документа, ACT - строки документа. Поле даты в ACT есть, но строк в ACT 1,3млрд, в ACS - пару млн. Фильтровать выгодней в ACS...
22 май 18, 16:29    [21429611]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
JohnAl
invm, индексы для ACT есть по полю CSID и по полю UNIQ. Но оба - некластеризованные
Для начала статистику обновите. Желательно с fullscan.
22 май 18, 16:32    [21429630]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
JohnAl
vborets, не вариант
Грубо схема. ACS - заголовок документа, ACT - строки документа. Поле даты в ACT есть, но строк в ACT 1,3млрд, в ACS - пару млн. Фильтровать выгодней в ACS...

invm ответил почему.
ваши
автор
индексы для ACT есть по полю CSID и по полю UNIQ. Но оба - некластеризованные

не нравятся. Можете прибить по UNIQ но получите лукап
22 май 18, 16:33    [21429638]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
SFlash
Member

Откуда:
Сообщений: 143
Нужен индекс по ACT.CSID + хинт FORCESEEK (часто оптимизатор даже при нормальном индексе делает SCAN по индексу).
Но т.к. данных выбирается всего за 3 дня, лучше ему это указать явно.
22 май 18, 16:53    [21429738]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
JohnAl
Member

Откуда: Москва
Сообщений: 109
Ну так то автоматическая статистика обновляется в 16м скл неплохо... Статистику по индексам, да, давненько не обновлял...
22 май 18, 17:28    [21429923]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
JohnAl
Member

Откуда: Москва
Сообщений: 109
Ну и да, в итоге банальное устаревание статистики по индексу. Пересчитал частично за ночь с фулскан, план запроса сразу поменялся, индекс заработал.
Ох тыж, как бы еще придумать, чтобы за ночь обновление статистики проходило, а то ведь по одной этой большой таблице это почти на сутки, а по всей БД - двое...
Наверное стоит использовать sp_updatestats? Конечно, как там сервер решит, кто достоин обновления, вопрос, но работает быстрее. Или же, при ребилде индексов, статистика ведь тоже обновляется. Тогда выходит, выгодней ребилд индексов делать по отдельным таблицам.
23 май 18, 10:15    [21431206]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
TaPaK
Member

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

а auto update не включен что ли?
23 май 18, 10:17    [21431213]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
JohnAl
Ох тыж, как бы еще придумать, чтобы за ночь обновление статистики проходило
Возможно вам поможет секционирование и инкрементальное обновноление статистики.
JohnAl
Тогда выходит, выгодней ребилд индексов делать по отдельным таблицам.
Одумайтесь.
23 май 18, 10:52    [21431358]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
JohnAl
vborets, не вариант
Грубо схема. ACS - заголовок документа, ACT - строки документа. Поле даты в ACT есть, но строк в ACT 1,3млрд, в ACS - пару млн. Фильтровать выгодней в ACS...
Бред! Коллега, вы не правы! Любые фильтры напрямую по огромным таблицам всегда лучше чем какие либо фильтры через джойны, потому что:
а) При джойне данные из большой таблицы дергаются по ID единичными index seek. В то время как BETWEEN фильтр по дате по основной таблицы пройдет в один seek по первой дате + scan до конечной даты, с полным префетчем и максимально возможной (учитывая фрагментацию) скоростью вычитки с диска.
б) Погрешности статистики при джойне всегда хуже чем при прямом фильтре. В 16ом конечно стало лучше, но до совершенства далеко.

Вы про это думаете что серверу нужно отфильровать лишние 1,3млрд минус 5 тысяч строк, и это куча работы, а должны думать как серверу нужно выбрать 5 тысяч строк по индексу и пофигу из таблицы какого размера. На самом деле даже если бы там было 100 млрд строк, то скорость выборки по индексу изменилась бы максимум на 1-2 дополнительных чтения или несколько миллисекунд. Для этого собственно индексы и нужны.
23 май 18, 22:21    [21434124]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
invm
JohnAl
Тогда выходит, выгодней ребилд индексов делать по отдельным таблицам.
Одумайтесь.
Да можно и отдельные статистики с фуллсканом обновлять, в чем проблема то? Зачастую на таблице автоматом создается статистик как количество полей + индексов + если еще вручную кто сделал, большая часть из которых вообще никому не сдалась и зачем их все обновлять с фуллсканом? Каждая колоночная статистика это почти гарантированный table scan, в то время как индексная статистика это всего лишь index scan. Почти наверняка ТСу хватило бы обновить статистику по TA.CSID, SA.UNIQ, TA.OPCODE и OP.OPCODE. Там эстимейт выдает 227 миллионов строк при реальных 80.
23 май 18, 22:32    [21434145]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
982183
Member

Откуда: VL
Сообщений: 3349
Mind
Любые фильтры напрямую по огромным таблицам всегда лучше чем какие либо фильтры через джойны, потому что:

Совершенно верно.
Именно поэтому я и предлагал сначала вывести из большой таблице некий дамп, а потом уже этот дамп джойнить.
24 май 18, 02:46    [21434569]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
Mind
Да можно и отдельные статистики с фуллсканом обновлять, в чем проблема то?
Никаких.
Только ТС вместо этого хочет перестраивать индексы.
24 май 18, 09:28    [21434826]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
JohnAl
Member

Откуда: Москва
Сообщений: 109
Mind
invm
пропущено...
Одумайтесь.
Да можно и отдельные статистики с фуллсканом обновлять, в чем проблема то? Зачастую на таблице автоматом создается статистик как количество полей + индексов + если еще вручную кто сделал, большая часть из которых вообще никому не сдалась и зачем их все обновлять с фуллсканом? Каждая колоночная статистика это почти гарантированный table scan, в то время как индексная статистика это всего лишь index scan. Почти наверняка ТСу хватило бы обновить статистику по TA.CSID, SA.UNIQ, TA.OPCODE и OP.OPCODE. Там эстимейт выдает 227 миллионов строк при реальных 80.


Можно конечно. Просто вопрос автоматизации. Ручками выбрать статистики то - несложно, но таблиц в БД две сотни. Найду подходящий скрипт по апдейтам, тогда уж... Если кто подскажет, был бы благодарен.
А то вот нагуглил:
DECLARE @DateNow DATETIME
SELECT @DateNow = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
    SELECT '
	UPDATE STATISTICS [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] [' + s.name + ']
		WITH FULLSCAN' + CASE WHEN s.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';'
	FROM (
		SELECT 
			  [object_id]
			, name
			, stats_id
			, no_recompute
			, last_update = STATS_DATE([object_id], stats_id)
		FROM sys.stats WITH(NOLOCK)
		WHERE auto_created = 0
			AND is_temporary = 0 -- 2012+
	) s
	JOIN sys.objects o WITH(NOLOCK) ON s.[object_id] = o.[object_id]
	JOIN (
		SELECT
			  p.[object_id]
			, p.index_id
			, total_pages = SUM(a.total_pages)
		FROM sys.partitions p WITH(NOLOCK)
		JOIN sys.allocation_units a WITH(NOLOCK) ON p.[partition_id] = a.container_id
		GROUP BY 
			  p.[object_id]
			, p.index_id
	) p ON o.[object_id] = p.[object_id] AND p.index_id = s.stats_id
	WHERE o.[type] IN ('U', 'V')
		AND o.is_ms_shipped = 0
		AND (
			  last_update IS NULL AND p.total_pages > 0 -- never updated and contains rows
			OR
			  last_update <= DATEADD(dd, 
				CASE WHEN p.total_pages > 4096 -- > 4 MB
					THEN -2 -- updated 3 days ago
					ELSE 0 
				END, @DateNow)
		)
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL
--EXEC sys.sp_executesql @SQL

А оно выводит не все таблицы (((

Фильтры хороши, но только когда есть индексы. В большой таблице АСТ индекса по дате - нет, есть только по id. Добавлять индексы в эту таблицу не особо возможно. Очень много интенсивной вставки данных, как пакетной так и нет. Чтения - намного меньше. Тут есть опасения, что накладные расходы от нового индекса превысят выгоды более быстрой работы отчета. На чтение, для отчетов, сделал снапшот с зеркала на другом сервере. Вот его терзают этими запросами. На боевой БД такие запросы - редкость, точнее стараемся их вывести на зеркальный сервер.
24 май 18, 15:14    [21436815]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить