Optimitzar les sentències SQL ja no és una ciència sinó que s’està convertint en un art. No n’hi ha prou que la consulta de la teva aplicació retorni el resultat desitjat, a més cal que pensis una mica en la forma més eficient de fer-ho.
Fins fa poc passava olímpicament d’aquests aspectes, feia les meves sentències, dissenyava l’aplicació i solucionava la majoria de problemes de lentitud creant índexs.
En petites aplicacions trobar la forma més òptima de fer una consulta pot no ser vital però en grans aplicacions marca la diferència entre l’èxit i el desastre. Actualment em trobo treballant amb una aplicació relativament senzilla però on centenars d’usuaris ataquen simultàniament una base de dades de milers i milers de registres.
Us ensenyo algunes millores per si algú li serveix d’alguna cosa.
Sentència:
SELECT […] FROM […] WHERE UPPER(DDP_DNI) = UPPER(:B1)
Problema:
Realment ens cal donar la possibilitat a guardar lletres del DNI en majúsules i minúscules?
Solució:
Una optimització senzilla passa per no posar funcions sobre les columnes que poden servir de filtre. Per tant, si forcem que en aquest cas el DNI sigui sempre en majúscules ens podem estalviar el upper.
Això ho podem forçar a l’aplicació o directament amb un tigger. En el nostre cas la solució utilitzada ha estat la del trigger per ser la més senzilla d’implementar i no haver de tocar més codi de l’aplicació. 😀
Sentència:
SELECT * FROM XXX_MOVIMENTS, XXX_ESTATS_OBJECTE, XXX_MOTIUS
WHERE MOV_ESTAT = EOB_ID
AND MOV_MOTIU = MOT_ID (+)
AND (MOV_IDOBJ = :B3 OR :B3 IS NULL)
AND (MOV_ID = :B2 OR :B2 IS NULL)
AND (MOV_ULTIM = :B1 OR :B1 IS NULL)
ORDER BY MOV_DATA,MOV_ID
Problema:
A l’utilitzar en la sentència (OR <VARIABLE> IS NULL) la possibilitat d’utilitzar índex es perd, generant accessos per FULL SCAN!
Solució:
L’aplicació hauria de valorar quins paràmetres són nuls i reformular la consulta tenint-ho en conte. Si l’apliació gestionés els nuls es podrien utilitzar índex obtenint millors resultats!
Mireu un exemple:
Gestionant els Nuls en la sentència:
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=646 Card=1700 Bytes=134300)
1 0 SORT (ORDER BY) (Cost=646 Card=1700 Bytes=134300)
2 1 HASH JOIN (OUTER) (Cost=619 Card=1700 Bytes=134300)
3 2 HASH JOIN (Cost=616 Card=1700 Bytes=86700)
4 3 TABLE ACCESS (FULL) OF 'XXX_ESTATS_OBJECTE' (Cost=2 Card=67 Bytes=1675)
5 3 TABLE ACCESS (FULL) OF 'XXX_MOVIMENTS' (Cost=613 Card=1700 Bytes=44200)
6 2 TABLE ACCESS (FULL) OF 'XXX_MOTIUS' (Cost=2 Card=37 Bytes=1036
Gestionant els Nuls a l’aplicació faria una cosa semblant a això:
SELECT * FROM XGI_MOVIMENTS, XGI_ESTATS_OBJECTE, XGI_MOTIUS
WHERE MOV_ESTAT = EOB_ID
AND MOV_MOTIU = MOT_ID (+)
AND (MOV_IDOBJ = :B3 )
AND (MOV_ID = :B2 )
AND (MOV_ULTIM = :B1)
ORDER BY MOV_DATA,MOV_ID;
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=79)
1 0 NESTED LOOPS (Cost=4 Card=1 Bytes=79)
2 1 NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=54)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'XXX_MOVIMENTS' (Cost=2 Card=1 Bytes=26)
4 3 INDEX (UNIQUE SCAN) OF 'XGI_MOVIMENTS_PK' (UNIQUE) (Cost=2 Card=1)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'XXX_MOTIUS' (Cost=2 Card=1 Bytes=28)
6 5 INDEX (UNIQUE SCAN) OF 'XGI_MOT_PK' (UNIQUE)
7 1 TABLE ACCESS (BY INDEX ROWID) OF 'XXX_ESTATS_OBJECTE'
Com podeu veure el canvi és impressionant 😀