Patrzyłem na artykuł tutaj Tabele tymczasowe vs. zmienne tabel i ich wpływ na wydajność programu SQL Server, a na serwerze SQL Server 2008 udało się odtworzyć wyniki podobne do pokazanych tam w 2005 roku.
Podczas wykonywania procedur przechowywanych (definicje poniżej) z tylko 10 wierszami wersja zmiennej tabeli wykonuje wersję tabeli tymczasowej ponad dwa razy.
Wyczyściłem pamięć podręczną procedur i uruchomiłem obie procedury przechowywane 10 000 razy, a następnie powtórzyłem proces dla kolejnych 4 uruchomień. Wyniki poniżej (czas w ms na partię)
T2_Time V2_Time
----------- -----------
8578 2718
6641 2781
6469 2813
6766 2797
6156 2719Moje pytanie brzmi: jaka jest przyczyna lepszej wydajności wersji zmiennej tabeli?
Przeprowadziłem dochodzenie. np. Patrząc na liczniki wydajności za pomocą
SELECT cntr_value
from sys.dm_os_performance_counters
where counter_name = 'Temp Tables Creation Rate';potwierdza, że w obu przypadkach obiekty tymczasowe są buforowane po pierwszym uruchomieniu zgodnie z oczekiwaniami, a nie tworzone od nowa dla każdego wywołania.
Podobnie śledzenie Auto Stats, SP:Recompile, SQL:StmtRecompilewydarzenia w Profiler (zrzut ekranu poniżej) pokazuje, że wydarzenia te występują tylko raz (na pierwszym wezwaniem #temptabeli procedury przechowywanej), a pozostałe 9,999 egzekucje nie budzą żadnej z tych wydarzeń. (Wersja zmiennej tabeli nie otrzymuje żadnego z tych zdarzeń)
Nieco większy narzut związany z pierwszym uruchomieniem procedury przechowywanej nie może w żaden sposób uwzględniać dużej ogólnej różnicy, ponieważ nadal zajmuje tylko kilka ms, aby wyczyścić pamięć podręczną procedur i uruchomić obie procedury raz, więc nie wierzę w statystyki ani rekompilacje mogą być przyczyną.
Utwórz wymagane obiekty bazy danych
CREATE DATABASE TESTDB_18Feb2012;
GO
USE TESTDB_18Feb2012;
CREATE TABLE NUM
(
n INT PRIMARY KEY,
s VARCHAR(128)
);
WITH NUMS(N)
AS (SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY $/0)
FROM master..spt_values v1,
master..spt_values v2)
INSERT INTO NUM
SELECT N,
'Value: ' + CONVERT(VARCHAR, N)
FROM NUMS
GO
CREATE PROCEDURE [dbo].[T2] @total INT
AS
CREATE TABLE #T
(
n INT PRIMARY KEY,
s VARCHAR(128)
)
INSERT INTO #T
SELECT n,
s
FROM NUM
WHERE n%100 > 0
AND n <= @total
DECLARE @res VARCHAR(128)
SELECT @res = MAX(s)
FROM NUM
WHERE n <= @total
AND NOT EXISTS(SELECT *
FROM #T
WHERE #T.n = NUM.n)
GO
CREATE PROCEDURE [dbo].[V2] @total INT
AS
DECLARE @V TABLE (
n INT PRIMARY KEY,
s VARCHAR(128))
INSERT INTO @V
SELECT n,
s
FROM NUM
WHERE n%100 > 0
AND n <= @total
DECLARE @res VARCHAR(128)
SELECT @res = MAX(s)
FROM NUM
WHERE n <= @total
AND NOT EXISTS(SELECT *
FROM @V V
WHERE V.n = NUM.n)
GOSkrypt testowy
SET NOCOUNT ON;
DECLARE @T1 DATETIME2,
@T2 DATETIME2,
@T3 DATETIME2,
@Counter INT = 0
SET @T1 = SYSDATETIME()
WHILE ( @Counter < 10000)
BEGIN
EXEC dbo.T2 10
SET @Counter += 1
END
SET @T2 = SYSDATETIME()
SET @Counter = 0
WHILE ( @Counter < 10000)
BEGIN
EXEC dbo.V2 10
SET @Counter += 1
END
SET @T3 = SYSDATETIME()
SELECT DATEDIFF(MILLISECOND,@T1,@T2) AS T2_Time,
DATEDIFF(MILLISECOND,@T2,@T3) AS V2_Time
#temptabeli tylko raz, mimo że zostały wyczyszczone i ponownie wypełnione kolejne 9 999 razy po tym.