As fi vrut sa-mi impartaseasca cineva experienta practica cu tabele temporare din punct de vedere al performantei.
Eu evit sa folosesc tabele temporare ori de cate ori este posibil. Este corect asa?

De asemenea, nu sunt sigur cine ofera performante mai bune: tabelele temporare sau variabile de tip TABLE.
Ar fi utila si comparatia cu CTE.

Stiu ca nu exista adevaruri universal valabile, dar as fi vrut sa identificam (din perspectiva practica) un fel de best practice pentru tabele temporare.

Parere : avind in vedere ca tabelele temporare sunt in tempdb, iar variabilele sunt in memorie, inclin sa cred ca performante mai bune sunt la variabile.
dar ar trebui verificat ...
Totusi, dupa cite stiu, nu se pot folosi una in locul celeilalte (de pilda, dpdv tranzactional)

---

Ignat Andrei
http://serviciipeweb.ro/iafblog

E bine sa verifici fiecare caz in parte.
Daca, de exemplu, rulez scriptul de mai jos la mine (Vista Business 32 biti SP1, 4 CPU 2.8 Ghz, 2GB RAM, SQL 2008 dev SP1) obtin 1026 ms in cazul variabilei tabel si 596 ms in cazul tabelului temporar. Scriptul este propus de Gail Shaw (http://sqlinthewild.co.za/):
Use AdventureWorks2008
go

-- Table variable

SET NOCOUNT ON

DECLARE @StartTime DATETIME

DECLARE @LineItems TABLE (
SalesOrderID INT PRIMARY KEY,
ProductID INT,
Total NUMERIC(38,6)
)

INSERT INTO @LineItems (SalesOrderID, ProductID, Total)
SELECT SalesOrderDetailID, productid, LineTotal FROM Sales.SalesOrderDetail where ModifiedDate > '2003-10-01 00:00:00.000'

SET @StartTime = GETDATE()

SELECT ProductNumber, SUM(Total)
FROM Production.Product p
INNER JOIN @LineItems Sales on p.ProductID = Sales.ProductID
GROUP BY ProductNumber

PRINT 'Duration with table variable: ' + CAST(DATEDIFF(ms, @StartTime, getdate()) as varchar(10)) + ' ms'

CREATE TABLE #LineItems (
SalesOrderID INT PRIMARY KEY,
ProductID INT,
Total NUMERIC(38,6)
)
INSERT INTO #LineItems (SalesOrderID, ProductID, Total)
SELECT SalesOrderDetailID, productid, LineTotal FROM Sales.SalesOrderDetail where ModifiedDate > '2003-10-01 00:00:00.000'

SET @StartTime = GETDATE()

SELECT ProductNumber, SUM(Total)
FROM Production.Product p
INNER JOIN #LineItems Sales on p.ProductID = Sales.ProductID
GROUP BY ProductNumber

PRINT 'Duration with temp table: ' + CAST(DATEDIFF(ms, @StartTime, getdate()) as varchar(10)) + ' ms'

DROP TABLE #LineItems

Este vorba cam de 65000 inregistrari.

Vezi si http://www.sqlservercentral.com/articles/Temporary+Tables/66720/.

Andrei,

Nu este adevărat faptul că tabelele temporare sunt stocate doar pe disc, iar variabilele de tip tabelă doar în memorie. Vezi întrebarea 4 din KB 305977.

În plus, recomand şi eu link-ul menţionat de Diana:
http://www.sqlservercentral.com/articles/Temporary+Tables/66720/ (necesită înregistrare gratuită)
precum şi următoarele:
http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
http://blogs.msdn.com/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Răzvan

Recomandarea mea este să se evite folosirea tabelelor temporare în proceduri care sunt apelate des (pentru că ar putea cauza recompilări) şi să se evite folosirea variabilelor de tip tabelă în cazul în care ar putea avea mai mult de câteva sute de rânduri (pentru că nu au distribution statistics).

Răzvan

poi si ce folosim ? pentru proceduri apelate des  in care avem nevoie de tabele in care sa fie stocate cateva mii de randuri (10.000 50.000) ?


eu am facut urmatorul test:

declare @t1 table
(a int,
b varchar(1000),
c datetime)

declare @i int
set @i=0

while @i < 1000000
begin
    insert into @t1 (a,b,c) values (@i,'1234456757869879090-=qwerwerteruort[piolajksdgflkajsdhf,mzxbnc,v',getdate())
    set @i = @i + 1
end

select * from @t1 where a<10000


unde
CPU = 23775
Reads = 1097834



cel de al 2 :


create table #t1
(a int,
b varchar(1000),
c datetime)

declare @i int
set @i=0

while @i < 1000000
begin
    insert into #t1 (a,b,c) values (@i,'1234456757869879090-=qwerwerteruort[piolajksdgflkajsdhf,mzxbnc,v',getdate())
    set @i = @i + 1
end

create index ix on #t1 (a)
select * from #t1 where a<10000
drop table #t1

unde
CPU = 26816
Reads = 1113503

si fara index

create table #t1
(a int,
b varchar(1000),
c datetime)

declare @i int
set @i=0

while @i < 1000000
begin
    insert into #t1 (a,b,c) values (@i,'1234456757869879090-=qwerwerteruort[piolajksdgflkajsdhf,mzxbnc,v',getdate())
    set @i = @i + 1
end

select * from #t1 where a<10000
drop table #t1

CPU = 25381
Reads = 1099355


 Din ce arata testele se pare ca cel mai bun este @t1.

Indexul poate ma ajuta la select , dar per total nu se observa diferenta , ba chiar mai mult ma ingreuneaza.

Gresesc ceva ?


Astept si parerile voastre legate de folosirea variabilei tabel si a tabelei temporare, care este cea mai buna pentru performanta in cazul in care procedura este apelata des si se folosesc inregistrari intre 1.000 si 10.000 ?

La fiecare apel de procedura se creeaza tabela temporara se foloseste si se da drop ....

Multumesc anticipat

Diana:

*****
PRINT 'Duration with table variable: ' + CAST(DATEDIFF(ms, @StartTime, getdate()) as varchar(10)) + ' ms'
*****
PRINT 'Duration with temp table: ' + CAST(DATEDIFF(ms, @StartTime, getdate()) as varchar(10)) + ' ms'

Din cate stiu performanta nu are nici o legatura cu timpul de executie.
Timpul de executie depinde de pc pe care ruleaza.

Performanta depinde de sec CPU si Reads pe care le vezi intr-un SQL profiler :D

Nu ?

Multumesc,