SQL-Ex blog

Для объяснения я собираюсь использовать большую базу данных Stack Overflow и написать хранимую процедуру:

CREATE OR ALTER PROC dbo.usp_TempTable_Heap 

	@DisplayName NVARCHAR(40) AS

	BEGIN

	CREATE TABLE #MyUsers (Id INT, DisplayName NVARCHAR(40));

	INSERT INTO #MyUsers (Id, DisplayName)

		SELECT Id, DisplayName

		FROM dbo.Users;

	SELECT Id, DisplayName

	FROM #MyUsers

	WHERE DisplayName = @DisplayName;

	END

GO

Первый оператор загружает около 10 миллионов строк во временную таблицу, а второй - только вытаскивает строки, которые соответствуют тому, кого мы ищем. Это, вероятно, кажется глупым, и вы восклицаете: "Брент, зачем вам потребовалось помещать миллион строк во временную таблицу, которая не нужна?" В ответ я скажу, что мне нужно быстро написать статью в блоге для объяснения концепции, которой мне приходилось неоднократно обучать клиентов. Я не могу копировать/вставлять здесь их код. Это было бы неправильно. Поэтому давайте поиграемся с этим.

Я включу статистику времени с тем, чтобы я мог получить некие соображения о том, на что SQL Server тратит свое время - на первый оператор или на второй:

SET STATISTICS TIME ON;

EXEC dbo.usp_TempTable_Heap N'Brent Ozar';

Я не часто в блоге использую статистику по времени, поэтому быстро объясню: на вкладке Messages (сообщения) в SSMS вы получаете по строке на каждый оператор в пакете, плюс итоговые результаты:

SQL Server Execution Times:
CPU time = 4453 ms, elapsed time = 4458 ms.
SQL Server Execution Times:
CPU time = 1031 ms, elapsed time = 1017 ms.
SQL Server Execution Times:
CPU time = 5484 ms, elapsed time = 5477 ms.
Первый оператор - загрузка во временную таблицу - занимает 4,5 секунды.

Второй оператор - нахождение Brent - занимает примерно секунду.

Может ли индекс на DisplayName ускорить второй запрос?

Чтобы это выяснить, давайте добавим новую версию нашей хранимой процедуры - теперь она создает индекс на DisplayName до загрузки данных:

CREATE OR ALTER PROC dbo.usp_TempTable_IndexAddedBeforeLoad

	@DisplayName NVARCHAR(40) AS

	BEGIN

	CREATE TABLE #MyUsers (Id INT, DisplayName NVARCHAR(40));

	CREATE INDEX DisplayName ON #MyUsers(DisplayName); /* Это новое */

	INSERT INTO #MyUsers (Id, DisplayName)

		SELECT Id, DisplayName

		FROM dbo.Users;

	SELECT Id, DisplayName

	FROM #MyUsers

	WHERE DisplayName = @DisplayName;

	END

GO

Теперь, когда мы выполним запрос:

EXEC dbo.usp_TempTable_IndexAddedBeforeLoad N'Brent Ozar';

статистика по времени нарисует ужасающую картину:

SQL Server Execution Times:
CPU time = 56594 ms, elapsed time = 56772 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 75 ms.
SQL Server Execution Times:
CPU time = 56594 ms, elapsed time = 56850 ms.
Конечно, второй оператор сокращает время с 1017 миллисекунд до 75, но… кого это волнует?!?! Дополнительная нагрузка построения индекса значительно выше, что делает в целом выполнение запроса в 10 раз медленнее.

Часть проблемы состоит в том, что сначала наша куча (таблица) должна загружаться, а затем данные должны быть отсортированы по DisplayName, после чего должен создаваться индекс на DisplayName. Мы не можем оба эти действия делать параллельно, поскольку некластеризованный индекс должен иметь возможность обратной ссылки на конкретную строку в куче - и чтобы это сделать, нам необходимо знать её физическое местоположение, как я говорил об этом в "Как думать подобно SQL Server"

Прекрасно. Как насчет кластеризованного индекса?

Мы можем уменьшить нагрузку процесса только при наличии одной структуры для хранения данных. Вместо кучи плюс некластеризованный индекс на DisplayName мы можем просто определить единственную структуру для временной таблицы: кластеризованный индекс на DisplayName. Этот индекс не может быть уникальным, поскольку у нескольких пользователей может быть одинаковые DisplayName, но это нормально. Поехали:

CREATE OR ALTER PROC dbo.usp_TempTable_ClusteredIndex

	@DisplayName NVARCHAR(40) AS

	BEGIN

	CREATE TABLE #MyUsers (Id INT, DisplayName NVARCHAR(40));

	CREATE CLUSTERED INDEX DisplayName ON #MyUsers(DisplayName); /* ЭТО НОВОЕ */

	INSERT INTO #MyUsers (Id, DisplayName)

		SELECT Id, DisplayName

		FROM dbo.Users;

	SELECT Id, DisplayName

	FROM #MyUsers

	WHERE DisplayName = @DisplayName;

	END

GO

И когда мы выполним запрос:

EXEC dbo.usp_TempTable_ClusteredIndex N'Brent Ozar';

то на первый взгляд этот метод выглядит более быстрым, чем последний:

SQL Server Execution Times:
CPU time = 42107 ms, elapsed time = 18071 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 42107 ms, elapsed time = 18145 ms.
Время выполнения снизилось с 57 секунд до 18 секунд, но здесь есть загвоздка. По большей части, более быстрое выполнение обусловлено тем, что запрос выполняется параллельно. Обратите внимание, что время ЦП выше, чем время выполнения - это признак того, что запрос выполнялся параллельно на нескольких ядрах ЦП. Теперь у нас могут возникнуть проблемы с процессором, если несколько таких запросов будут выполняться одновременно.

И все же это не так быстро, как наше оригинальное решение, куча.

Итак, как мне сделать временные таблицы быстрее?

Загружайте в них только те данные, которые необходимы. При загрузке во временные таблицы - на самом деле, в любые объекты - выполняйте фильтрацию как можно раньше.

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

Попробуйте CTE как альтернативу. Если у вас многошаговый процесс, который включает множество фильтраций и соединений, пусть SQL Server пересчитает, где должна выполняться логика фильтрации. Я не имею в виду, что CTE всегда лучше временных таблиц - часто имеет место обратное - но если вы уперлись в стену, пытаясь настроить производительность запросов, которые используют временные таблицы, попробуйте преобразовать их в CTE. SQL Server переупорядочит операции - ему не обязательно выполнять сначала первый CTE, затем второй CTE и так далее.