Временные таблицы в Synapse SQL

Статья
06/01/2023

В этой статье содержатся важные рекомендации по использованию временных таблиц и приводятся основные концепции временных таблиц уровня сеанса в Synapse SQL.

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

Временные таблицы

Временные таблицы удобны при обработке данных — особенно во время преобразования, где промежуточные результаты являются временными. В Synapse SQL временные таблицы существуют на уровне сеанса. Их можно просмотреть только в сеансе, в котором они были созданы. Поэтому после завершения сеанса они автоматически удаляются.

Временные таблицы в выделенном пуле SQL

Временные таблицы в ресурсе выделенного пула SQL позволяют оптимизировать производительность, так как их результаты записываются в локальное, а не удаленное хранилище.

Создание временной таблицы

Для создания временной таблицы к имени таблицы добавляется префикс #. Пример:

CREATE TABLE #stats_ddl
(
    [schema_name]        NVARCHAR(128) NOT NULL
,    [table_name]            NVARCHAR(128) NOT NULL
,    [stats_name]            NVARCHAR(128) NOT NULL
,    [stats_is_filtered]     BIT           NOT NULL
,    [seq_nmbr]              BIGINT        NOT NULL
,    [two_part_name]         NVARCHAR(260) NOT NULL
,    [three_part_name]       NVARCHAR(400) NOT NULL
)
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
,    HEAP
)

Временные таблицы можно также создать с помощью CTAS точно таким же образом:

CREATE TABLE #stats_ddl
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
,    HEAP
)
AS
(
SELECT
        sm.[name]                                                                AS [schema_name]
,        tb.[name]                                                                AS [table_name]
,        st.[name]                                                                AS [stats_name]
,        st.[has_filter]                                                            AS [stats_is_filtered]
,       ROW_NUMBER()
        OVER(ORDER BY (SELECT NULL))                                            AS [seq_nmbr]
,                                 QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [two_part_name]
,        QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [three_part_name]
FROM    sys.objects            AS ob
JOIN    sys.stats            AS st    ON    ob.[object_id]        = st.[object_id]
JOIN    sys.stats_columns    AS sc    ON    st.[stats_id]        = sc.[stats_id]
                                    AND st.[object_id]        = sc.[object_id]
JOIN    sys.columns            AS co    ON    sc.[column_id]        = co.[column_id]
                                    AND    sc.[object_id]        = co.[object_id]
JOIN    sys.tables            AS tb    ON    co.[object_id]        = tb.[object_id]
JOIN    sys.schemas            AS sm    ON    tb.[schema_id]        = sm.[schema_id]
WHERE    1=1
AND        st.[user_created]   = 1
GROUP BY
        sm.[name]
,        tb.[name]
,        st.[name]
,        st.[filter_definition]
,        st.[has_filter]
)
;

Примечание

CTAS — мощная команда, которая особенно эффективна при использовании пространства журнала транзакций.

Удаление временных таблиц

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

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

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

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

DROP TABLE #stats_ddl

Разбиение кода на модули

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

CREATE PROCEDURE    [dbo].[prc_sqldw_update_stats]
(   @update_type    tinyint -- 1 default 2 fullscan 3 sample 4 resample
    ,@sample_pct     tinyint
)
AS

IF @update_type NOT IN (1,2,3,4)
BEGIN;
    THROW 151000,'Invalid value for @update_type parameter. Valid range 1 (default), 2 (fullscan), 3 (sample) or 4 (resample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

CREATE TABLE #stats_ddl
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
)
AS
(
SELECT
        sm.[name]                                                                AS [schema_name]
,        tb.[name]                                                                AS [table_name]
,        st.[name]                                                                AS [stats_name]
,        st.[has_filter]                                                            AS [stats_is_filtered]
,       ROW_NUMBER()
        OVER(ORDER BY (SELECT NULL))                                            AS [seq_nmbr]
,                                 QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [two_part_name]
,        QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [three_part_name]
FROM    sys.objects            AS ob
JOIN    sys.stats            AS st    ON    ob.[object_id]        = st.[object_id]
JOIN    sys.stats_columns    AS sc    ON    st.[stats_id]        = sc.[stats_id]
                                    AND st.[object_id]        = sc.[object_id]
JOIN    sys.columns            AS co    ON    sc.[column_id]        = co.[column_id]
                                    AND    sc.[object_id]        = co.[object_id]
JOIN    sys.tables            AS tb    ON    co.[object_id]        = tb.[object_id]
JOIN    sys.schemas            AS sm    ON    tb.[schema_id]        = sm.[schema_id]
WHERE    1=1
AND        st.[user_created]   = 1
GROUP BY
        sm.[name]
,        tb.[name]
,        st.[name]
,        st.[filter_definition]
,        st.[has_filter]
)
SELECT
    CASE @update_type
    WHEN 1
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+');'
    WHEN 2
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH FULLSCAN;'
    WHEN 3
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH SAMPLE '+CAST(@sample_pct AS VARCHAR(20))+' PERCENT;'
    WHEN 4
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH RESAMPLE;'
    END AS [update_stats_ddl]
,   [seq_nmbr]
FROM    #stats_ddl
;
GO

На этом этапе единственное действие заключается в создании хранимой процедуры, которая создает временную таблицу #stats_ddl. Хранимая процедура удаляет #stats_ddl, если эта таблица уже существует. Это обеспечивает бесперебойную работу в случае повторного запуска таблицы на протяжении сеанса.

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

В отличие от других баз данных SQL Server, в Synapse SQL временную таблицу можно использовать вне процедуры, которая ее создала. Временные таблицы, созданные с использованием выделенного пула SQL, можно использовать в любом месте внутри сеанса. В результате получается более управляемый модульный код, как показано в примере ниже:

EXEC [dbo].[prc_sqldw_update_stats] @update_type = 1, @sample_pct = NULL;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''

WHILE @i <= @t
BEGIN
    SET @s=(SELECT update_stats_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

Ограничения временной таблицы

В выделенном пуле SQL есть несколько ограничений реализации для временных таблиц:

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

Временные таблицы в бессерверном пуле SQL

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

Например, невозможно присоединить временную таблицу к данным из файлов в хранилище. Максимальное число временных таблиц — 100, а их максимальный общий размер — 100 МБ.

Дальнейшие действия

Дополнительные сведения о разработке таблиц см. в статье Проектирование таблиц с помощью ресурсов Synapse SQL.

Поделиться через