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

Откуда:
Сообщений: 163
Приветствую.
Имеется таблица, которая показывает приходы / убытия товаров на склады и в магазины следующего вида (на самом деле данные для нее берутся на лету из нескольких таблиц, но в результате получается именно такая структура).
CREATE TABLE [dbo].[_a](
	[EventDate] [datetime] NOT NULL, --дата события
	[ProductID] [int] NOT NULL, --товар
	[IsComplex] [bit] NULL, --собран/не собран
	[WarehouseID] [int] NULL, --склад
	[ShopID] [int] NULL, --магазин
	[Quan] [int] NULL --количество (положительное при приходе, отрицательное при убытии)
) ON [PRIMARY]


Также имеется процедура, задача которой убедиться, что нет никаких отрицательных остатков (т.е. ни в одном складе / магазине никогда не получается отрицательное значение товаров). Она подсчитывает промежуточный итог с помощью частичного суммирования.

DECLARE @dt DATETIME = GETDATE()

DECLARE @ErrMsg VARCHAR(2000) = ''
SELECT @ErrMsg = @ErrMsg + CAST(t.EventDate AS VARCHAR(20)) + '	' + p.Title + '	' + CAST(t.SumQuan AS VARCHAR(10)) + '
'
FROM
(
	SELECT 
		ProductID,
		EventDate,
		SUM(Quan) OVER(PARTITION BY WarehouseID, ShopID, ProductID, IsComplex 
				ORDER BY WarehouseID, ShopID, ProductID, IsComplex, EventDate, Quan DESC  
				ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumQuan
	FROM _a
) t
INNER JOIN dbo.Products p ON p.ProductID = t.ProductID
WHERE SumQuan < 0


DECLARE @dt2 DATETIME = GETDATE()
SELECT CONVERT(VARCHAR, @dt2- @dt, 113)


В таблице на данный момент приблизительно 150 000 записей и данный запрос на неплохом сервере выполняется в среднем 1.2 сек, что, на мой взгляд, непозволительно долго.
Пробовал добавлять индексы по WarehouseID, SHopID, ProductID, IsComplex, как составные, так и по отдельности - не помогло.
Делал кластеризованный индекс по WarehouseID, SHopID, ProductID, IsComplex, EventDate, Quan - скорость выполнения увкличилась на 20% примерно, но не более.
Порекомендуйте, пожалуйста, что в данном случае можно сделать?
План запроса в приложении.

Спасибо за уделенное время и внимание.

К сообщению приложен файл (vpl.sqlplan - 71Kb) cкачать
21 мар 17, 23:10    [20319407]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса / таблицы  [new]
Alexey30
Member

Откуда:
Сообщений: 163
И только что обнаружил, что если из процедуры убрать конкатенацию и связку с таблицей Products, то сразу производительность вырастает примерно в 5 раз - теперь 230 мс, что значительно лучше.
DECLARE @dt DATETIME = GETDATE()

SELECT *
FROM
(
	SELECT 
		ProductID,
		EventDate,
		SUM(Quan) OVER(PARTITION BY WarehouseID, ShopID, ProductID, IsComplex 
			ORDER BY WarehouseID, ShopID, ProductID, IsComplex, EventDate, Quan DESC  
			ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumQuan
	FROM _a
) t
WHERE SumQuan < 0

DECLARE @dt2 DATETIME = GETDATE()
SELECT CONVERT(VARCHAR, @dt2- @dt, 113)

Может быть еще что-то можно сделать?
21 мар 17, 23:17    [20319418]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса / таблицы  [new]
Владислав Колосов
Member

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

Вы бы фактический план приложили, а не предполагаемый. Затраты на сортировку будут иметь наибольший вес в любом случае.
22 мар 17, 01:48    [20319604]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса / таблицы  [new]
aleksrov
Member

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

Пробовал добавлять индексы по WarehouseID, SHopID, ProductID, IsComplex, как составные, так и по отдельности - не помогло.
При оптимизации оконных функций есть такое понятие как POC индекс, как его называет Бен-Ган, Ключами индекса должны быть столбцы оконных секций за которыми следуют столбы упорядочевания, в конце включаем остальные столбы используемые в запросе.
Также если у вас SQL 2016 (версию вы не сказали) появилась такая штука как Batch Mode Window Aggregate Operator, штука очень быстрая но работает только с Columnstore индексами, здесь сказано как это обойти.
22 мар 17, 04:49    [20319644]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса / таблицы  [new]
IDVT
Member

Откуда:
Сообщений: 320
aleksrov
Alexey30,

.... но работает только с Columnstore индексами, здесь сказано как это обойти.

Большое спасибо за ссылку!
22 мар 17, 09:14    [20319874]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить