Неймовірно великий код SQL, групування поєднання таблиць по виборці

Потрібно не забувати, що програмні частини компонент виконають будь-який наказ людини, навіть якщо цей наказ безглуздий.

Результат безглуздого запиту: підвисання систем для користувачів, занадто велике навантаження дискової системи та серверів, неймовірно великі витрати електроенергії на обробку запиту.

Ось приклад SQL-запиту до бази даних MsSQL системи 1С:

DECLARE  @DateStart date = N’2023-02-01’;
DECLARE  @DateFinish date = DATEADD(DAY, 1, GETDATE());

SELECT
	СкладскиеСотрудники._Description AS Исполнитель,
	COUNT(DISTINCT СхожиеЗадачиОтбора.ЗадачаОтборСсылка) AS ОтобраноСтрРЛ,
	COUNT(DISTINCT ЗадачаОтбор._IDRRef) AS ФактЗадач,
	SUM(усЗадачаУпаковка._Fld2906 / СхожиеЗадачиОтбора.КолвоСхожих) AS ФактШт,
	SUM(усПартииНоменклатуры._Fld5323 * усЗадачаУпаковка._Fld2906 / СхожиеЗадачиОтбора.КолвоСхожих) AS ФактГрн, --	СУММА(усПартииНоменклатуры.ЦенаЗакупки * усЗадачаУпаковка.Количество / СхожиеЗадачиОтбора.КолвоСхожих) КАК СуммаОтобрано,
	CAST(DATEADD([YEAR], -2000, ЗадачаОтбор._Fld2922) AS date) AS ДатаВыполненияЗадачи,
	’Отбор’ AS ТипЗадачи,
	’Сборщик’ AS Роль
FROM
	_Document174 AS ЗадачаОтбор 

-- Коэффициент для учета схожих задач 
LEFT JOIN(	SELECT 
				усЗадачаОтбор._IDRRef AS ЗадачаОтборСсылка, 
				COUNT(DISTINCT усЗадачаОтбор._IDRRef) AS КолвоСхожих 
			FROM
				_Document174 AS усЗадачаОтбор 
				INNER JOIN _Document174 AS усЗадачаУпаковка 
				ON усЗадачаОтбор._Fld2923_RRRef = усЗадачаупаковка._Fld2923_RRRef
					AND усЗадачаОтбор._Fld2902RRef = усЗадачаупаковка._Fld2902RRef
					AND усЗадачаОтбор._Fld2905RRef = усЗадачаупаковка._Fld2905RRef
					AND усЗадачаОтбор._Fld2901RRef = усЗадачаупаковка._Fld2920RRef
			WHERE
					CAST(усЗадачаОтбор._Fld2896RRef AS uniqueidentifier) = ’{620E6DB7-8091-141D-4F97-C97ABB668B03}’	--	Отбор
					AND CAST(усЗадачаОтбор._Fld2897RRef AS uniqueidentifier) = ’{EDE221A7-01E7-6478-4DFB-8B3A58A5C280}’	--	Выполнена
					AND CAST(усЗадачаУпаковка._Fld2896RRef AS uniqueidentifier) = ’{6F42AB8A-C208-E52A-4D0E-15B138F85A29}’--	Упаковка
					AND CAST(усЗадачаУпаковка._Fld2897RRef AS uniqueidentifier) = ’{EDE221A7-01E7-6478-4DFB-8B3A58A5C280}’   --  Выполнена
					AND (DATEADD([YEAR], -2000, усЗадачаОтбор._Fld2922)) BETWEEN @DateStart AND @DateFinish	
			GROUP BY
			усЗадачаОтбор._IDRRef) AS СхожиеЗадачиОтбора 
	ON ЗадачаОтбор._IDRRef = СхожиеЗадачиОтбора.ЗадачаОтборСсылка

-- Количество отобранной номенклатуры (считается по упакованной номенклатуре)
LEFT JOIN _Document174 AS усЗадачаУпаковка 
	ON ЗадачаОтбор._Fld2923_RRRef = усЗадачаупаковка._Fld2923_RRRef
		AND ЗадачаОтбор._Fld2902RRef = усЗадачаупаковка._Fld2902RRef
		AND ЗадачаОтбор._Fld2905RRef = усЗадачаупаковка._Fld2905RRef
		AND ЗадачаОтбор._Fld2901RRef = усЗадачаупаковка._Fld2920RRef

-- Цена закупки из партии номенклатуры
LEFT JOIN _Reference86 AS усПартииНоменклатуры
	ON ЗадачаОтбор._Fld2905RRef = усПартииНоменклатуры._IDRRef


-- Читабельный сборщик из сотрудников
LEFT JOIN dbo._Reference112 AS СкладскиеСотрудники
	ON ЗадачаОтбор._Fld2915RRef = СкладскиеСотрудники._IDRRef

WHERE 		  
	CAST(ЗадачаОтбор._Fld2896RRef AS uniqueidentifier) = ’{620E6DB7-8091-141D-4F97-C97ABB668B03}’	--	Отбор
	AND CAST(ЗадачаОтбор._Fld2897RRef AS uniqueidentifier) = ’{EDE221A7-01E7-6478-4DFB-8B3A58A5C280}’	--	Выполнена
	AND DATEADD([YEAR], -2000, ЗадачаОтбор._Fld2922) BETWEEN @DateStart AND @DateFinish
		
GROUP BY 
	CAST(DATEADD([YEAR], -2000, ЗадачаОтбор._Fld2922) AS date),
	СкладскиеСотрудники._Description
UNION
SELECT
	СкладскиеСотрудники._Description AS Исполнитель,
	SUM(УпакованоСтрРЛ.СтрРЛ) СтрокиРЛ,
	COUNT(усЗадачаУпаковка._IDRRef) AS ФактЗадач,
	SUM(усЗадачаУпаковка._Fld2906) AS ФактШт, 
	SUM(усПартииНоменклатуры._Fld5323 * усЗадачаУпаковка._Fld2906) AS ФактГрн,
	CAST(DATEADD([YEAR], -2000, усЗадачаУпаковка._Fld2922) AS date) AS ДатаВыполненияЗадачи,
	’Контроль’ AS ТипЗадачи,
	’Контролер’ AS Роль
FROM
	_Document174 AS усЗадачаУпаковка

---- Коэффициент для количества строк РабЛиста
LEFT JOIN(	SELECT
				ЗадачаУпаковка._Fld2923_RRRef AS ДокументОснование,
				COUNT(DISTINCT ЗадачаОтбор._IDRRef) / CAST(COUNT(DISTINCT ЗадачаУпаковка._IDRRef) as float) AS СтрРЛ
			FROM
				_Document174 AS ЗадачаУпаковка
				INNER JOIN _Document174 AS ЗадачаОтбор
					ON ЗадачаУпаковка._Fld2923_RRRef = ЗадачаОтбор._Fld2923_RRRef
					AND ЗадачаОтбор._Fld2902RRef = Задачаупаковка._Fld2902RRef
					AND ЗадачаОтбор._Fld2905RRef = Задачаупаковка._Fld2905RRef
					AND ЗадачаОтбор._Fld2901RRef = Задачаупаковка._Fld2920RRef
			WHERE
					CAST(ЗадачаОтбор._Fld2896RRef AS uniqueidentifier) = ’{620E6DB7-8091-141D-4F97-C97ABB668B03}’	--	Отбор
					AND CAST(ЗадачаОтбор._Fld2897RRef AS uniqueidentifier) = ’{EDE221A7-01E7-6478-4DFB-8B3A58A5C280}’	--	Выполнена
					AND CAST(ЗадачаУпаковка._Fld2896RRef AS uniqueidentifier) = ’{6F42AB8A-C208-E52A-4D0E-15B138F85A29}’--	Упаковка
					AND CAST(ЗадачаУпаковка._Fld2897RRef AS uniqueidentifier) = ’{EDE221A7-01E7-6478-4DFB-8B3A58A5C280}’   --  Выполнена
					AND (DATEADD([YEAR], -2000, ЗадачаУпаковка._Fld2922)) BETWEEN @DateStart AND @DateFinish
			GROUP BY
				ЗадачаУпаковка._Fld2923_RRRef) AS УпакованоСтрРЛ  
	ON усЗадачаУпаковка._Fld2923_RRRef = УпакованоСтрРЛ.ДокументОснование 
				
---- Цена закупки из партии номенклатуры
LEFT JOIN _Reference86 AS усПартииНоменклатуры
	ON усЗадачаУпаковка._Fld2905RRef = усПартииНоменклатуры._IDRRef

-- Читабельный Контролер из сотрудников
LEFT JOIN dbo._Reference112 AS СкладскиеСотрудники
	ON усЗадачаУпаковка._Fld2915RRef = СкладскиеСотрудники._IDRRef

WHERE
	CAST(усЗадачаУпаковка._Fld2896RRef AS uniqueidentifier) = ’{6F42AB8A-C208-E52A-4D0E-15B138F85A29}’	--Упаковка
	AND CAST(усЗадачаУпаковка._Fld2897RRef AS uniqueidentifier) = ’{EDE221A7-01E7-6478-4DFB-8B3A58A5C280}’	--	Выполнена
	AND DATEADD([YEAR], -2000, усЗадачаУпаковка._Fld2922) BETWEEN @DateStart AND @DateFinish
		
GROUP BY 
	CAST(DATEADD([YEAR], -2000, усЗадачаУпаковка._Fld2922) AS date),
	СкладскиеСотрудники._Description

План запиту:

Недоліки цього звіту:

1. Зашифрованість полів та таблиць, їх назви незрозумілі для людини: _Reference86, _Reference112, _Fld2902RRef, _Fld2905RRef, _Fld2901RRef. Це результат вибору платформи. На рівні 1С в MsSQL використовуються ось такі незрозумілі назви. Ці назви можна зрозуміти тільки використовуючи довідник конфігурації; при цьому доведеться порівнювати фізичні назви на рівні бази даних з логічними назвами на рівні платформи.

2. Часте використання приведення типів CAST(***, AS ***) та інших функцій. Використання функцій не дозволяє серверу коректно сформувати індексні читання. Як результат, виконання запитів без використання індексів.

3. Поєднання запитів не таблиця-таблиця, а таблиця-селект: LEFT JOIN( SELECT. Це призводить до побудови таблиць в пам'яті, що є дуже затратною операцією та зовсім неідексованими читаннями. Бо в цьому випадку індексів не може бути – таблиця в пам'яті.

4. Велика кількість групувань та поєднань запитів за допомогою Union. Групування – це неіндексовані читання. Серверу доводиться складати таблиці в пам'яті. Коли таблиці мають багато мільйонів запитів, ці запити дуже сильно навантажують багатопроцесорні системи, призводячи до некомфортної роботи користувачів та відмови в обслуговуванні сервером.

5. Ми бачимо по плану запитів, що хоч в самому низу гілки запиту є пара кластеризованих індексованих читань, проте багато рівнів йдуть складні розрахунки, що повністю неіндексовані. Цей запит працює з багатомільйонною кількістю рядків і такі дії підвішують потужні багатопроцесорні системи з великою кількістю інформації. Вінчестери завантажуються на 100% своєї потужності, процесори завантажуються на 100% потужності і температура в серверній від одного такого перевантаженого сервера може піднятись на 1-2 градуси, що свідчить про неймовірні витрати енергії!

Висновки:

- Не використовуйте такі жахливі запити! Не забувайте, що призначення SQL – поєднання таблиць одна з одною, а не бізнес-аналітика та розрахунки. Поєднання таблиць за допомогою оптимізації по індексах та можливості оптимізувати запит. Не забирайте таку можливість у сервера, створюючи величезні запити, які сервер не спроможний оптимізувати!

Автор: Рудюк С.А. 2023. K2 Cloud ERP.