SQL-Ex blog

Хотя это и не настолько важно, но есть один связанный с этим момент - как обрабатывать временные таблицы в хранимых процедурах. Удаляете вы их в конце хранимой процедуры или оставляете, чтобы их почистил SQL Server? Лучше ли один вариант по сравнению с другим с точки зрения производительности? Давайте проведем некоторое тестирование и посмотрим, что мы выяснится.

То же самое, но разное

Вот наша тестовая хранимая процедура без удаления временной таблицы. Мы создаем временную таблицу и наполняем ей тестовыми данными:

CREATE PROC [dbo].[TestNoDrop]

AS

CREATE TABLE #Temp (

    ID INT IDENTITY(1, 1)

    ,TEMP_VARCHAR VARCHAR(100)

    ,TEMP_DATE DATETIME2

    )

DECLARE @RecordCount AS INT = 100;

WHILE @RecordCount > 0

BEGIN

    INSERT INTO #Temp

    VALUES (

        'Here is a test string of data!'

        ,GETDATE()

        )

    SET @RecordCount -= 1;

END

Вторую хранимую процедуру мы сделаем из копии первой, но добавим в конце DROP TABLE #Temp:

CREATE PROC [dbo].[TestWithDrop]

AS

CREATE TABLE #Temp (

    ID INT IDENTITY(1, 1)

    ,TEMP_VARCHAR VARCHAR(100)

    ,TEMP_DATE DATETIME2

    )

DECLARE @RecordCount AS INT = 100;

WHILE @RecordCount > 0

BEGIN

    INSERT INTO #Temp

    VALUES (

        'Here is a test string of data!'

        ,GETDATE()

        )

    SET @RecordCount -= 1;

END

DROP TABLE #Temp

Перед тестированием каждой хранимой процедуры я выполняю DBCC FREEPROCCACHE, чтобы получить чистые результаты при последующем выполнении sp_BlitzCache. После очистки процедурного кэша TestWithDrop запускается на выполнение 1000 раз:

EXEC TestWithDrop;

GO 1000

Выполнение TestWithDrop 1000 раз занимает на моей машине 7 секунд в SSMS. Есть ли разница без удаления?

EXEC TestNoDrop;

GO 1000

На моей машине это заняло те же 7 секунд.

Мы можем использовать sp_BlitzCache, чтобы ближе взглянуть на то, как выполнялась каждая процедура.

CPU подобны:



Продолжительность и количество чтений также близки:



Ничего удивительного не произошло в SSMS. Ни на основании опыта при запуске каждой процедуры 1000, ни в выводе sp_BlitzCache нет сколь-нибудь заметной разницы между удалением и неудалением временной таблицы.

Что следует предпочесть?

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