Skip to content

Есть ли польза от удаления временной таблицы в хранимой процедуре?

Пересказ статьи Chad Callihan. Is Dropping a Temp Table in a Stored Procedure Beneficial?


Вообще говоря, лучше удалять вещи, которые не используются. Не храните индексы, которые не используются, поскольку они занимают место на диске и по-прежнему поддерживаются в актуальном состоянии при изменении данных. Нужно ли хранить таблицу, которая содержит неиспользуемые устаревшие данными? Возможно, пришло время для таких вещей, как архивация базы данных или секционирование.
Хотя это и не настолько важно, но есть один связанный с этим момент - как обрабатывать временные таблицы в хранимых процедурах. Удаляете вы их в конце хранимой процедуры или оставляете, чтобы их почистил 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 выполнить обработку за вас при отключении. Если ваши хранимые процедуры уже удаляют временные таблицы, замечательно. Если нет, я бы не стал тратить зря время и усилия на их удаление.

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

No comments

The author does not allow comments to this entry

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

Form options

Submitted comments will be subject to moderation before being displayed.