Home > Articoli > Indexes and temporary table

Indexes and temporary table

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.

 

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Unit testing: Come scrivere la tua prima unit test!

Nell’articolo precedente, il secondo di questa serie, abbiamo descritto come installare il framework tSQLt, il …