UGISS User Group Italiano SQL Server
Le tabelle temporanee vengono tipicamente (e largamente) utilizzate per salvare dati e risultati intermedi durante un’elaborazione. In aggiunta, una volta terminata l’elaborazione, i risultati possono essere presentati all’utente attraverso una tabella temporanea. SQL Server fornisce la possibilità di utilizzare due tipi di tabelle temporanee, tutte memorizzate nel database di sistema tempdb:
- Local temporary tables
- Global temporary tables
Le local temporary tables sono visibili solo dall’utente che le ha create e solo all’interno della stessa connessione, vengono eliminate automaticamente da SQL Server quando l’utente si disconnette.
Le global temporary tables sono visibili da tutti gli utenti e da tutte le connessioni, vengono eliminate automaticamente da SQL Server quando l’utente che l’ha creata si disconnette.
Ipotizziamo di dover realizzare una funzione applicativa in grado di determinare il valore delle scorte di magazzino ad una certa data, l’output della funzione è rappresentato da una tabella temporanea locale che riassume per ogni prodotto la giacenza alla data di elaborazione ed il relativo valore unitario.
La funzione che permette di valorizzare le scorte, all’inizio della sua dell’elaborazione, crea la tabella temporanea #tmpProductValue con il seguente frammento di codice T-SQL:
use [AdventureWorks];
go
if OBJECT_ID(‘tempdb..#tmpProductValue’) is not null
drop table #tmpProductValue;
go
create table #tmpProductValue
(
ProductID int not null,
ProductNumber varchar(50) not null,
Quantity int not null,
UnitValue int not null,
ProductLine varchar(2) null,
SellStartDate datetime not null,
SellEndDate datetime null
primary key(ProductID)
);
go
create nonclustered index
[idx_#tmpProductValue_ProductNumber]
on #tmpProductValue(ProductNumber);
go
Nel comando di creazione della tabella temporanea, viene definita la primary key e l’indice cluster (automatico) sulla colonna ProductID. Viene inoltre creato anche un indice non cluster sulla colonna ProductName.
Dopo aver creato la tabella #tmpProductValue, la procedura elabora i dati ed esegue l’inserimento di un record per ogni prodotto giacente, il seguente codice T-SQL simula (in modo semplificato) l’inserimento di 500.000 righe nella tabella temporanea #tmpProductValue:
declare
@i as int,
@datebegin as datetime,
@dateend as datetime
set @i = 1
set @datebegin = GETDATE()
while (@i <= 500000)
begin
set nocount on
insert into #tmpProductValue
(
ProductID,
ProductNumber,
Quantity,
UnitValue,
SellStartDate
)
values
(
@i,
replace(cast(str(@i) as varchar(50)), ‘ ‘, ‘0’),
(@i * 2),
(@i + 1),
GETDATE()
)
set @i = (@i + 1)
set nocount off
end
set @dateend = GETDATE()
select
DATEDIFF(SECOND, @datebegin, @dateend) as elab_time
go
Le performance dell’elaborazione (inserimento dei dati nella tabella temporanea) non ci sembrano ottimali, per inserire 500.000 righe occorrono in media 27 – 28 secondi (la funzione reale a cui si ispira questo articolo impiega 4 – 5 ore per terminare la sua elaborazione su una local temporary table con indici), diamo un’occhiata al piano di esecuzione prodotto per un solo comando di INSERT:
Osserviamo le proprietà del task “Clustered Index Insert”, in particolare osserviamo il costo stimato per questa operazione.
Considerando i comandi T-SQL che interessano la tabella temporanea: nella prima fase dell’elaborazione troviamo soltanto INSERT, nessuna SELECT e nessun UPDATE. L’indice cluster sulla colonna ProductID e l’indice non-cluster sulla colonna ProductNumber rappresentano, in questa particolare situazione, solo un costo di manutenzione. Valutiamo di creare gli indici solo dopo i comandi di INSERT e vediamo come cambiano le performance ed il piano di esecuzione.
Per creare la tabella temporanea utilizziamo ora il seguente frammento di codice T-SQL, in cui non vengono creati indici:
use [AdventureWorks];
go
if OBJECT_ID(‘tempdb..#tmpProductValue’) is not null
drop table #tmpProductValue;
go
create table #tmpProductValue
(
ProductID int not null,
ProductNumber varchar(50) not null,
Quantity int not null,
UnitValue int not null,
ProductLine varchar(2) null,
SellStartDate datetime not null,
SellEndDate datetime null
);
go
Se eseguiamo di nuovo l’inserimento di 500.000 righe nella tabella temporanea notiamo che le performance sono migliorate. Senza indici occorrono in media 19 – 20 secondi (la funzione reale a cui si ispira questo articolo impiega ora 15 minuti per terminare la sua elaborazione su una local temporary table senza indici), diamo un’occhiata al nuovo piano di esecuzione prodotto per il comando di INSERT:
Osserviamo le proprietà del task “Table Insert”, in particolare osserviamo il costo stimato per questa operazione.
Conclusioni
In situazioni analoghe a quella descritta, quando un’elaborazione fa uso di tabelle temporanee, può essere conveniente mantenere la tabella senza indici fino a quando non sono terminati i comandi di INSERT.
