Performance di DB2 SQL complesse: CTE

La potenza elaborativa del DB2 SQL for i su IBMi (AS/400) ha ormai raggiunto livelli notevoli e offre spesso più strade per raggiungere lo stesso risultato. Spesso, però, hanno performance diverse a seconda della tecnica utilizzata. In quest'articolo voglio mostrare la tecnica delle CTE (Common Table Expression) che, grazie alla WITH clause, permettono di gestire richieste complesse raggruppando vari step in un unica richiesta multi-step.

I vantaggi offerti sono molteplici, la leggibilità dell'istruzione ma anche e soprattutto un vantaggio prestazionale, perchè:

• si evita di scrivere su disco tabelle intermedie negli step precedenti in quanto eventuali tabelle temporanee vengono gestite solo in RAM
• il motore DB2 SQL non vede un'istruzione alla volta ma tutte insieme e questo permette al suo ottimizzatore di calcolare la soluzione migliore per raggiungere il risultato finale

Per capire meglio conviene sempre utilizzare degli esempi.

Meglio chiarire subito che l'esempio mostrato sotto è abbastanza semplice e non avrà prestazioni molto discostanti tra la soluzione tradizionale e la tecnica CTE, ma serve per capire tecnicamente come utilizzarla. Supponiamo di avere la classica tabella delle fatture di vendita (FATVEN) e vogliamo fare una statistica del fatturato che ci dia, per ogni agente, le seguenti informazioni:

• fatturato annuale progressivo alla data
• fatturato progressivo alla stessa data dell'anno precedente
• percentuale aumento/perdita
• fatturato progressivo alla stessa data di due anni prima
• percentuale di aumento/perdita

Senza il CTE si può ottenere il risultato con la seguente sequenza di istruzioni:

Istruzione 1:
DECLARE GLOBAL TEMPORARY TABLE fatturato AS (
select
jcdagf as cd_agente,
dec(sum(case when jdtftf between 20130101 and 20130531
        then jvlvef
        else 0 end), 11, 2) as fatt_aa,
dec(sum(case when jdtftf between 20120101 and 20120531
        then jvlvef
        else 0 end), 11, 2) as fatt_ap,
dec(sum(case when jdtftf between 20110101 and 20110531
        then jvlvef
        else 0 end), 11, 2) as fatt_app
from FATVEN
where (jdtftf between 20110101 and 20110531
   or  jdtftf between 20120101 and 20120531
   or  jdtftf between 20130101 and 20130531
      )
group by jcdagf
) with data;

Istruzione 2:
select 
a.cd_agente,
ifnull(b.dscag, 'DIREZIONALE') as "Agente",
a.fatt_aa as "Fatturato anno attuale",
a.fatt_ap as "Fatturato anno precedente",
case when ifnull(a.fatt_ap, 0) = 0
     then 100
     else float(dec( (a.fatt_aa - a.fatt_ap)*100 / a.fatt_ap, 7, 2)) end as "%",
a.fatt_app as "Fatturato 2 anni prima",
case when ifnull(a.fatt_app, 0) = 0
     then 100
     else float(dec( (a.fatt_ap - a.fatt_app)*100 / a.fatt_app, 7, 2)) end as "%"
from qtemp.fatturato a
left join aage200f b on a.cd_agente=b.codag
order by cd_agente

Utilizzando le Common Table Expression si può scrivere un unica istruzione come la seguente:

with 
INPUT as ( 
select 
20130531 as DT_LIM 
from sysibm.sysdummy1 
), 

Parametri as ( 
select 
dt_lim, 
int(substr(char(dt_lim), 1, 4)) as Anno, 
int(substr(char(dt_lim), 5, 2)) as Mese, 
int(substr(char(dt_lim), 7, 2)) as Giorno 
from input 
), 

Fatturato as ( 
select 
jcdagf as cd_agente, 
dec(sum(case when jdtftf between (select (anno)*10000 from parametri) 
                     and     (select (anno)*10000+mese*100+giorno from parametri) 
         then jvlvef 
         else 0 end), 11, 2) as fatt_aa, 
dec(sum(case when jdtftf between (select (anno-1)*10000 from parametri) 
                     and     (select (anno-1)*10000+mese*100+giorno from parametri) 
         then jvlvef 
         else 0 end), 11, 2) as fatt_ap, 
dec(sum(case when jdtftf between (select (anno-2)*10000 from parametri) 
                     and     (select (anno-2)*10000+mese*100+giorno from parametri) 
         then jvlvef 
         else 0 end), 11, 2) as fatt_app 
from FATVEN
where (jdtftf between (select (anno-2)*10000 from parametri) 
              and     (select (anno-2)*10000+mese*100+giorno from parametri) 
   or  jdtftf between (select (anno-1)*10000 from parametri) 
              and     (select (anno-1)*10000+mese*100+giorno from parametri) 
   or  jdtftf between (select (anno)*10000 from parametri) 
              and     (select (anno)*10000+mese*100+giorno from parametri) 
      ) 
group by jcdagf 
) 

select 
a.cd_agente, 
ifnull(b.dscag, 'DIREZIONALE') as "Agente", 
a.fatt_aa as "Fatturato anno attuale", 
a.fatt_ap as "Fatturato anno precedente", 
case when ifnull(a.fatt_ap, 0) = 0 
     then 100 
     else float(dec( (a.fatt_aa - a.fatt_ap)*100 / a.fatt_ap, 7, 2)) end as "%", 
a.fatt_app as "Fatturato 2 anni prima", 
case when ifnull(a.fatt_app, 0) = 0 
     then 100 
     else float(dec( (a.fatt_ap - a.fatt_app)*100 / a.fatt_app, 7, 2)) end as "%" 
from fatturato a 
left join aage200f b on a.cd_agente=b.codag 
order by cd_agente

Risulta subito evidente che questa istruzione, rispetto alla prima, non richiede scritture su disco in quanto non utilizza TEMPORARY TABLE ovvero tabelle in QTEMP. Provate ad utilizzare questa tecnica, all'inizio può sembrare un po' ostica, ma un volta capita la utilizzerete moltissimo con enorme soddisfazione.

by