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

Откуда:
Сообщений: 2260
Здравствуйте!

Изучаю оптимизацию для увеличения производительности SQL-запросов.

Есть исходные данные:
+SQL. Исходные данные
DROP TABLE IF EXISTS dbo.Продажи;

CREATE TABLE dbo.Продажи
(	
	Период INT NULL,	
	ГруппаКлиентов NVARCHAR(20) NULL,
	Клиент NVARCHAR(20) NULL,
	ГруппаSKU NVARCHAR(20) NULL,
	SKU NVARCHAR(20) NULL,
	Количество INT NOT NULL	
)ON [PRIMARY]; 

SET NOCOUNT ON

DECLARE @Count INT = 1;

WHILE @Count <= 10000 --Большое количество итерации в цикле!
BEGIN
	INSERT INTO dbo.Продажи
		(Период, ГруппаКлиентов, Клиент, ГруппаSKU, SKU, Количество)		
	SELECT
		CAST(CONCAT(Год, Месяц) AS INT), ГруппаКлиентов, Клиент, ГруппаSKU, SKU, Количество	
	FROM
		(SELECT
			Месяц, ГруппаКлиентов, Клиент, ГруппаSKU, SKU, Количество
		FROM
			(VALUES
				('ГруппаКлиентов 1', 'Клиент 1', 'ГруппаSKU 1', 'SKU 1', 10),
				('ГруппаКлиентов 1', 'Клиент 1', 'ГруппаSKU 1', 'SKU 2', 11),
				('ГруппаКлиентов 1', 'Клиент 1', 'ГруппаSKU 1', 'SKU 3', 12),
				('ГруппаКлиентов 1', 'Клиент 1', 'ГруппаSKU 1', 'SKU 4', 13),
				('ГруппаКлиентов 1', 'Клиент 1', 'ГруппаSKU 2', 'SKU 5', 10),
				('ГруппаКлиентов 1', 'Клиент 1', 'ГруппаSKU 2', 'SKU 6', 11),
				('ГруппаКлиентов 1', 'Клиент 1', 'ГруппаSKU 2', 'SKU 7', 12),
				('ГруппаКлиентов 1', 'Клиент 1', 'ГруппаSKU 2', 'SKU 8', 13),
				('ГруппаКлиентов 1', 'Клиент 2', 'ГруппаSKU 1', 'SKU 1', 10),
				('ГруппаКлиентов 1', 'Клиент 2', 'ГруппаSKU 1', 'SKU 2', 11),
				('ГруппаКлиентов 1', 'Клиент 2', 'ГруппаSKU 1', 'SKU 3', 12),
				('ГруппаКлиентов 1', 'Клиент 2', 'ГруппаSKU 1', 'SKU 4', 13),
				('ГруппаКлиентов 1', 'Клиент 2', 'ГруппаSKU 2', 'SKU 5', 10),
				('ГруппаКлиентов 1', 'Клиент 2', 'ГруппаSKU 2', 'SKU 6', 11),
				('ГруппаКлиентов 1', 'Клиент 2', 'ГруппаSKU 2', 'SKU 7', 12),
				('ГруппаКлиентов 1', 'Клиент 2', 'ГруппаSKU 2', 'SKU 8', 13),
				('ГруппаКлиентов 2', 'Клиент 3', 'ГруппаSKU 1', 'SKU 1', 10),
				('ГруппаКлиентов 2', 'Клиент 3', 'ГруппаSKU 1', 'SKU 2', 11),
				('ГруппаКлиентов 2', 'Клиент 3', 'ГруппаSKU 1', 'SKU 3', 12),
				('ГруппаКлиентов 2', 'Клиент 3', 'ГруппаSKU 1', 'SKU 4', 13),
				('ГруппаКлиентов 2', 'Клиент 3', 'ГруппаSKU 2', 'SKU 5', 10),
				('ГруппаКлиентов 2', 'Клиент 3', 'ГруппаSKU 2', 'SKU 6', 11),
				('ГруппаКлиентов 2', 'Клиент 3', 'ГруппаSKU 2', 'SKU 7', 12),
				('ГруппаКлиентов 2', 'Клиент 3', 'ГруппаSKU 2', 'SKU 8', 13),
				('ГруппаКлиентов 2', 'Клиент 4', 'ГруппаSKU 1', 'SKU 1', 10),
				('ГруппаКлиентов 2', 'Клиент 4', 'ГруппаSKU 1', 'SKU 2', 11),
				('ГруппаКлиентов 2', 'Клиент 4', 'ГруппаSKU 1', 'SKU 3', 12),
				('ГруппаКлиентов 2', 'Клиент 4', 'ГруппаSKU 1', 'SKU 4', 13),
				('ГруппаКлиентов 2', 'Клиент 4', 'ГруппаSKU 2', 'SKU 5', 10),
				('ГруппаКлиентов 2', 'Клиент 4', 'ГруппаSKU 2', 'SKU 6', 11),
				('ГруппаКлиентов 2', 'Клиент 4', 'ГруппаSKU 2', 'SKU 7', 12),
				('ГруппаКлиентов 2', 'Клиент 4', 'ГруппаSKU 2', 'SKU 8', 13)
			)AS t(ГруппаКлиентов, Клиент, ГруппаSKU, SKU, Количество)
		CROSS APPLY 
			(SELECT Месяц FROM (VALUES('01'), ('02'), ('03'), ('04'), ('05'), ('06'), 
                                                  ('07'), ('08'), ('09'), ('10'), ('11'), ('12')) t1(Месяц)) t2) t3
	CROSS APPLY 
			(SELECT Год FROM (VALUES('2018'), ('2019'), ('2020')) t(Год)) t4;

	SET @Count = @Count + 1
END

SET NOCOUNT OFF

SELECT COUNT(*) AS [Количество строк] FROM dbo.Продажи;

Создаем разные индексы
+SQL
CREATE CLUSTERED INDEX ClusteredIndex
	ON dbo.Продажи (Период ASC, ГруппаКлиентов ASC, Клиент ASC, ГруппаSKU ASC, SKU ASC, Количество ASC)
	ON [PRIMARY];
GO

--CREATE NONCLUSTERED INDEX NonClusteredIndex1
--	ON dbo.Продажи (Период, ГруппаКлиентов, Клиент, ГруппаSKU, SKU, Количество)
--	ON [PRIMARY];
--GO

CREATE NONCLUSTERED INDEX NonClusteredIndex2
	ON dbo.Продажи (SKU)
	ON [PRIMARY];
GO


CREATE NONCLUSTERED INDEX NonClusteredIndex3
	ON dbo.Продажи (Количество)
	ON [PRIMARY];

Запускаем разные выгрузки
+SQL
SET STATISTICS IO ON
SELECT
	ГруппаКлиентов,
	Клиент,
	ГруппаSKU,
	SKU,
	Количество
FROM
	dbo.Продажи
WHERE
	--NOT SKU = 'SKU 4'
	--SKU = 'SKU 1' OR SKU = 'SKU 2' OR SKU = 'SKU 3'
	Количество = 13
	--ГруппаКлиентов = 'ГруппаКлиентов 1' AND Клиент = 'Клиент 1' AND ГруппаSKU = 'ГруппаSKU 1' AND SKU = 'SKU 1' AND Количество < 13
	--ГруппаКлиентов = 'ГруппаКлиентов 1' AND Клиент = 'Клиент 1' AND ГруппаSKU = 'ГруппаSKU 1' AND SKU = 'SKU 1'
	--ГруппаКлиентов = 'ГруппаКлиентов 1' AND Клиент = 'Клиент 1' AND ГруппаSKU = 'ГруппаSKU 1' 
	--ГруппаКлиентов = 'ГруппаКлиентов 1' AND Клиент = 'Клиент 1'
SET STATISTICS IO OFF
+SQL
SET STATISTICS IO ON

SELECT
	ГруппаКлиентов,
	Клиент,
	ГруппаSKU,
	SKU,
	SUM(Количество) AS Количество
FROM
	dbo.Продажи
WHERE
	--NOT SKU = 'SKU 4'
	--SKU = 'SKU 1' OR SKU = 'SKU 2' OR SKU = 'SKU 3'
	Количество = 13
	--ГруппаКлиентов = 'ГруппаКлиентов 1' AND Клиент = 'Клиент 1' AND ГруппаSKU = 'ГруппаSKU 1' AND SKU = 'SKU 1' AND Количество < 13
	--ГруппаКлиентов = 'ГруппаКлиентов 1' AND Клиент = 'Клиент 1' AND ГруппаSKU = 'ГруппаSKU 1' AND SKU = 'SKU 1'
	--ГруппаКлиентов = 'ГруппаКлиентов 1' AND Клиент = 'Клиент 1' AND ГруппаSKU = 'ГруппаSKU 1' 
	--ГруппаКлиентов = 'ГруппаКлиентов 1' AND Клиент = 'Клиент 1'
GROUP BY
	ГруппаКлиентов,
	Клиент,
	ГруппаSKU,
	SKU

SET STATISTICS IO OFF

Создаем еще один индекс вместо обычного кластерного
+SQL
CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex
	ON
		dbo.Продажи
	WITH
		(DROP_EXISTING = ON, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE)
	ON [PRIMARY]

Необходимо ускорить производительность для запросов.

1. Я правильно понимаю, что для ускорения производительности в данном случае не нужно использовать только PRIMARY, а нужно создавать отдельные файловые группы? Если да, то насколько значительно это ускорит?

2. Какие еще варианты существуют для улучшения производительности в данном случае?
14 ноя 21, 18:02    [22395924]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про оптимизацию  [new]
aleks222
Member

Откуда:
Сообщений: 1846
ferzmikk


1. Я правильно понимаю, что для ускорения производительности в данном случае не нужно использовать только PRIMARY, а нужно создавать отдельные файловые группы? Если да, то насколько значительно это ускорит?

2. Какие еще варианты существуют для улучшения производительности в данном случае?


1. Ты не заметишь.
2. Подумать. На тему: "нафига козе баян?".
14 ноя 21, 18:35    [22395932]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про оптимизацию  [new]
SERG1257
Member

Откуда:
Сообщений: 2931
ferzmikk
Какие еще варианты существуют для улучшения производительности в данном случае?
индексированая вьюха
https://docs.microsoft.com/ru-ru/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15
14 ноя 21, 19:54    [22395951]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про оптимизацию  [new]
Владислав Колосов
Member

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

надо создать покрывающий индекс по количеству, но это может иметь последствия - увеличатся затраты на хранения и вставку.
14 ноя 21, 21:44    [22395976]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про оптимизацию  [new]
flexgen
Member

Откуда: Город на песке
Сообщений: 910
Все эти индексы что были созданы, они используются? Что вернет вот такой запрос:
SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
14 ноя 21, 22:31    [22395995]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про оптимизацию  [new]
komrad
Member

Откуда:
Сообщений: 5910
ferzmikk

2. Какие еще варианты существуют для улучшения производительности в данном случае?


анализировать statistics io и реальные планы выполнения
чем меньше чтений - тем быстрее запрос

зачем создавать кластерный индекс по всем столбцам?

запустить sp_BlitzIndex для этой таблицы и проанализировать результат
https://www.brentozar.com/blitzindex/

+ про индексы
-- Clustered Index


Nonclustered Index


Сообщение было отредактировано: 15 ноя 21, 12:00
15 ноя 21, 11:58    [22396111]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про оптимизацию  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4974
ferzmikk
Здравствуйте!

Изучаю оптимизацию для увеличения производительности SQL-запросов.



Я имел дело с такой таблицей продажи где-то на 6 проектах. Где она сотни лямов или больше лярда строк была. Со временем устанавливается некий набор индексов от 6 до 10, которые устраивает всех, кто в ней ковыряется.

Но как правило требуется месячное партиционирование по дате.

Если требуется очень разнообразная аналитика -- тут однозначно надо делать OLAP кубы.

Если это ERP система, то там обычно фильтрация по номеру чека, пользователю и т.п. Со временем 4-6 индексов также устаканиваются.

Так что никакой универсальной системы индексов никто не предложит. Анализируй запросы. Создавай индексы.
17 ноя 21, 12:39    [22396916]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про оптимизацию  [new]
ferzmikk
Member

Откуда:
Сообщений: 2260
komrad
зачем создавать кластерный индекс по всем столбцам?
Я пока экспериментирую с разными индексами. Просто в разных запросах используются различные комбинации полей в WHERE и GROUP BY.
28 ноя 21, 14:05    [22402138]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про оптимизацию  [new]
ferzmikk
Member

Откуда:
Сообщений: 2260
flexgen
Все эти индексы что были созданы, они используются?
По идее должны не все использоваться. Экспериментирую, чтобы подобрать наиболее оптимальный.

flexgen
Что вернет вот такой запрос:
OBJECT NAME INDEX NAME USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
Продажи NULL 0 3 0 0
Продажи index4 1 0 0 0
Продажи NonClusteredIndex2 0 0 0 0

Учитывая, что какие то индексы удаляю и добавляю другие.
28 ноя 21, 14:14    [22402141]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить