Udostępnij za pośrednictwem


Tabele tymczasowe w dedykowanej puli SQL w usłudze Azure Synapse Analytics

Ten artykuł zawiera podstawowe wskazówki dotyczące używania tabel tymczasowych i wyróżnia zasady tabel tymczasowych na poziomie sesji.

Korzystając z informacji podanych w tym artykule, można pomóc w modularyzacji kodu, poprawiając zarówno możliwość ponownego użycia, jak i łatwość konserwacji.

Co to są tabele tymczasowe?

Tabele tymczasowe są przydatne podczas przetwarzania danych, zwłaszcza podczas przekształcania, gdy wyniki pośrednie są przejściowe. W dedykowanej puli SQL tabele tymczasowe istnieją na poziomie sesji.

Tabele tymczasowe są widoczne tylko dla sesji, w której zostały utworzone i są automatycznie porzucane po zamknięciu tej sesji.

Tabele tymczasowe zapewniają korzyść wydajności, ponieważ ich wyniki są zapisywane w magazynie lokalnym, a nie w magazynie zdalnym.

Tabele tymczasowe w dedykowanej puli SQL

W dedykowanym zasobie puli SQL tabele tymczasowe oferują korzyść wydajności, ponieważ ich wyniki są zapisywane w magazynie lokalnym, a nie w magazynie zdalnym.

Tworzenie tabeli tymczasowej

Tabele tymczasowe są tworzone przez prefiks nazwy tabeli z .# Przykład:

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
)

Tabele tymczasowe można również utworzyć CTAS przy użyciu dokładnie tego samego podejścia:

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]
)
;

Uwaga

CTAS jest zaawansowanym poleceniem i ma dodatkową zaletę efektywnego korzystania z przestrzeni dziennika transakcji.

Usuwanie tabel tymczasowych

Po utworzeniu nowej sesji nie powinny istnieć tabele tymczasowe.

Jeśli wywołujesz tę samą procedurę składowaną, która tworzy tymczasową o tej samej nazwie, aby upewnić się, że CREATE TABLE instrukcje zakończyły się powodzeniem, można użyć prostego sprawdzania przedistnia z elementem , DROP jak w poniższym przykładzie:

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

Aby zapewnić spójność kodowania, dobrym rozwiązaniem jest użycie tego wzorca zarówno dla tabel, jak i tabel tymczasowych. Dobrym pomysłem DROP TABLE jest również usunięcie tabel tymczasowych po zakończeniu pracy z nimi w kodzie.

W przypadku opracowywania procedur składowanych często są wyświetlane polecenia upuszczania połączone na końcu procedury w celu zapewnienia, że te obiekty są czyszczone.

DROP TABLE #stats_ddl

Modularyzacja kodu

Ponieważ tabele tymczasowe można wyświetlać w dowolnym miejscu w sesji użytkownika, tę funkcję można wykorzystać, aby ułatwić modularyzowanie kodu aplikacji.

Na przykład poniższa procedura składowana generuje kod DDL w celu zaktualizowania wszystkich statystyk w bazie danych według nazwy statystyki:

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

Na tym etapie jedyną akcją, która wystąpiła, jest utworzenie procedury składowanej, która generuje tabelę tymczasową z #stats_ddlinstrukcjami DDL.

Ta procedura składowana pominie istniejący #stats_ddl element, aby upewnić się, że nie kończy się niepowodzeniem w przypadku uruchomienia więcej niż raz w ramach sesji.

Jednak ponieważ nie ma DROP TABLE na końcu procedury składowanej, po zakończeniu procedury składowanej pozostawia utworzoną tabelę, aby można ją było odczytać poza procedurą składowaną.

W dedykowanej puli SQL, w przeciwieństwie do innych SQL Server baz danych, można użyć tabeli tymczasowej poza procedurą, która ją utworzyła. Dedykowane tabele tymczasowe puli SQL mogą być używane w dowolnym miejscu w sesji. Ta funkcja może prowadzić do bardziej modułowego kodu i zarządzania nim, jak w poniższym przykładzie:

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;

Ograniczenia tabeli tymczasowej

Dedykowana pula SQL nakłada kilka ograniczeń podczas implementowania tabel tymczasowych. Obecnie obsługiwane są tylko tabele tymczasowe z zakresem sesji. Globalne tabele tymczasowe nie są obsługiwane.

Ponadto nie można tworzyć widoków w tabelach tymczasowych. Tabele tymczasowe można tworzyć tylko przy użyciu dystrybucji skrótu lub działania okrężnego. Replikowana tymczasowa dystrybucja tabel nie jest obsługiwana.

Następne kroki

Aby dowiedzieć się więcej na temat tworzenia tabel, zobacz artykuł Projektowanie tabel przy użyciu dedykowanej puli SQL .