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.
Ciao,
dovrei sviluppare una query “ricorsiva” per far sì di visualizzare una distinta base di un prodotto.
E’ possibile applicare tale concetto a SQL db2?
Se si, come?
Grazie mille in anticipo