MediCoder Aide

Blogues » MediCoder » Optimisation SQL par contrainte métier
Par mytto20851 points 

Optimisation SQL par contrainte métier

Voici un fragment de code montrant une sélection des interventions susceptibles d'être en USCPO à une date donnée, par défaut une date courante.

// $date est entre $date est entre $operation.date + $operation.uscpo (days) 
$where[1] = "operations.date <= '$date'";
$where[2] = "DATE_ADD(operations.date, INTERVAL duree_uscpo DAY) > '$date'";

La seconde clause ne permet pas d'utiliser d'index puisque la valeur à tester est calculatoire. La première clause donnera donc un usage de l'index sur la date de l'interventon inférieur à une date donnée, a priori tout va bien. A priori seulement, car la date en question est presque toujours une date récente, donc l'index ne filtrera que les opérations dans le futur, lesquelles sont rapidement très minoritaires à mesure de l'usage du système.

L'index est ainsi rendu — presque — inutile, la requête réalise un — presque — full scan sur la table des interventions.

Pour le rendre à nouveau efficace il faudrait limiter la recherche sur une petite portion des opérations. A bien regarder l'usage du champ duree_uscpo, on se rend compte que les valeurs ne varient que de 0 à 5. En prenant 10 comme majorant, si operation.date + operations.duree_uscpo > $date, on est certain que operation.date + 10 > $date.

On peut ainsi ajouter une contrainte en apparence inutile en termes de filtre final, mais extrêmement utile pour le filtre initial réalisé par l'index:

// Minimal date will narrow scope and boost index execution greatly
$date_min = CMbDT::date("-10 DAY", $date);
$where[1] = "operations.date BETWEEN '$date_min' AND '$date'";
$where[2] = "DATE_ADD(operations.date, INTERVAL duree_uscpo DAY) > '$date'";

Dès lors l'index peut immédiatement se borner à scanner 10 jours d'interventions sur potentiellement des années d'historiques, rendant la requête quasi immédiate. Sur une base de 3 ans (1000 jours), la requête est environ 100 fois plus rapide.

Certes on prend un risque en mettant cette valeur en dur dans le code, au cas ou la durée USCPO serait anormalement saisie à plus de 10 jours, ce qui serait une aberration métier. On peut alors contraindre le champ avec une valeur maximale, et se servir de cette contrainte dans notre clause :

$operation = new COperation();
$max_uscpo = $operation->_specs["duree_uscpo"]->max;
 
// Minimal date will narrow scope and boost index execution greatly
$date_min = CMbDT::date("-$max_uscpo DAY", $date);
$where[1] = "operations.date BETWEEN '$date_min' AND '$date'";
$where[2] = "DATE_ADD(operations.date, INTERVAL duree_uscpo DAY) > '$date'";

Pour une fois une contrainte métier est au service de la réalisation technique.

Sponsors privilégiés

Mediboard project