A magic keyword — VALUES…

Синтаксис конструкции INSERT может показаться весьма тривиальным, поскольку стандарт T-SQL рассматривал ключевое слово VALUES лишь в контексте вставки данных – INSERT INTO … VALUES ….

С выходом SQL Server 2008 существенно расширился синтаксис T-SQL, благодаря чему стало возможным использовать многострочную конструкцию VALUES, при этом не только в контексте вставки данных.

В данном топике будет рассмотрена сравнительная эффективность использования конструкции VALUES в различных типовых ситуациях. Чтобы дать объективную оценку полученных результатов, для каждого примера, будет рассмотрен его план выполнения.

Как и в предыдущем топике, планы выполнения мы будем получать через профайлер, встроенный в dbForge Studio for SQL Server.

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

IF OBJECT_ID('dbo.GradePointAverage', 'U') IS NOT NULL
    DROP TABLE dbo.GradePointAverage
GO

CREATE TABLE dbo.GradePointAverage (
      StudentID INT
    , I SMALLINT NOT NULL
    , II SMALLINT NOT NULL
    , III SMALLINT NOT NULL
    , IV SMALLINT NOT NULL
    , CONSTRAINT PK_GradePointAverage PRIMARY KEY (StudentID)
)

INSERT INTO dbo.GradePointAverage (StudentID, I, II, III, IV)
SELECT sv.number, sv.number % 94, sv.number % 83, sv.number % 72, sv.number % 61
FROM [master].dbo.spt_values sv
WHERE sv.type = 'P'
	AND sv.number BETWEEN 1 AND 2000

Предположим, что требуется узнать минимальный и максимальный бал по каждому из учащихся.

Чтобы сделать сравнение более интересным, каждый из предложенных подходов будет выполнен в разных ситуациях: 1) когда у таблицы есть первичный ключ и 2) когда таблица является неупорядоченной кучей.

Сначала приведем самый неудачный пример реализации:

SELECT
	  StudentID
	, MaxGradePoint = MAX(GradePoint)
	, MinGradePoint = MIN(GradePoint)
FROM (
	SELECT StudentID, GradePoint = I
	FROM dbo.GradePointAverage
		UNION ALL
	SELECT StudentID, II
	FROM dbo.GradePointAverage
		UNION ALL
	SELECT StudentID, III
	FROM dbo.GradePointAverage
		UNION ALL
	SELECT StudentID, IV
	FROM dbo.GradePointAverage
) t
GROUP BY StudentID

Вместо того, чтобы прочитать данные один раз, обращение к исходной таблице происходит 4 раза – это, мягко говоря, не рационально:

Попробуем избавится от повторные чтений, применяя конструкцию UNPIVOT:

SELECT
	  StudentID
	, MaxGradePoint = MAX(GradePoint)
	, MinGradePoint = MIN(GradePoint)
FROM (
	SELECT *
	FROM dbo.GradePointAverage
	UNPIVOT (
		GradePoint FOR Grade IN (I, II, III, IV)
	) unpvt
) t
GROUP BY StudentID

Повторные чтения ушли, но план усложнился:

При этом, в случае, когда у таблице нет кластерного индекса, серверу приходится применять сортировку для упорядочивания данных.

Посмотрим как ведет себя конструкция VALUES:

SELECT
	  gpa.StudentID
	, t.MaxGradePoint
	, t.MinGradePoint
FROM dbo.GradePointAverage gpa
CROSS APPLY (
	SELECT 
		  MaxGradePoint = MAX(GradePoint)
		, MinGradePoint = MIN(GradePoint)
	FROM (
		VALUES (I), (II), (III), (IV)
	) t (GradePoint)
) t

При изменении условий, план остается очень простым и неизменным:

Значение Query Cost, полученный из SSMS, также наглядно подтверждает преимущества конструкции VALUES:

Применение конструкции VALUES не ограничивается задачами по преобразованию строк в столбцы. Еще одним из эффективных применений данной конструкции является форматированный вывод запроса.

Сразу уточню, что не считаю это хорошей практикой, поскольку данная задача должна выполнятся на клиенте. Тем не менее, подобные задачи часто были полезны, например, при разработке отчетов.

Предположим, для каждой исходной строки необходимо вывести данные в следующем виде:

Можно решить эту задачу вычитывая данные несколько раз применяя UNION ALL c сортировкой:

SELECT
	  StudentID
	, GradePoint
	, AverageGradePoint
FROM (
	SELECT ID = StudentID, StudentID, GradePoint = I, AverageGradePoint = (I + II + III + IV) / 4., RN = 1
	FROM dbo.GradePointAverage
		UNION ALL
	SELECT StudentID, NULL, II, NULL, 2
	FROM dbo.GradePointAverage
		UNION ALL
	SELECT StudentID, NULL, III, NULL, 3
	FROM dbo.GradePointAverage
		UNION ALL
	SELECT StudentID, NULL, IV, NULL, 4
	FROM dbo.GradePointAverage
) t
ORDER BY ID, RN

Опять мы получаем повторные чтения. При этом обратите внимание на сортировку, в случае, когда таблица не имеет кластерного индекса:

Как вариант, можно вернутся к конструкции UNPIVOT, проверяя при этом номер строки:

SELECT  
	  StudentID = CASE WHEN RN = 1 THEN StudentID END
	, GradePoint
	, AverageGradePoint = CASE WHEN RN = 1 THEN AverageGradePoint END
FROM (
    SELECT
          StudentID
        , GradePoint
        , AverageGradePoint
        , RN = ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY 1/0)
    FROM (
		SELECT *, AverageGradePoint = (I + II + III + IV) / 4.
		FROM dbo.GradePointAverage 
	) gpa
    UNPIVOT (
        GradePoint FOR Grade IN (I, II, III, IV)
    ) unpvt
) t

Повторные чтения ушли, но сортировка при использовании кучи никуда не исчезла:

Применим конструкцию VALUES, написав более элегантный запрос:

SELECT t.*
FROM dbo.GradePointAverage 
OUTER APPLY (
	VALUES
		  (StudentID, I,   (I + II + III + IV) / 4.)
		, (NULL,      II,  NULL)
		, (NULL,      III, NULL)
		, (NULL,      IV,  NULL)
) t (StudentID, GradePoint, AverageGradePoint)

Мы получили простой и эффективный план выполнения:

Согласно Query Cost, конструкция VALUES в очередной раз демонстрирует свою эффективность, по сравнению с соперниками:

Краткие выводы:

Конструкция VALUES не является полной заменой UNPIVOT, тем не менее, в некоторых ситуациях, она бывает очень полезной – позволяя существенно упрощять запросы.

Надеюсь, что у меня получилось, наглядно, это продемонстрировать.

Автор: AlanDenton

Источник