Sqlite3 - Optimisation Last record with trigger

Posted on Sat 02 January 2016 in Bdd

Mon soucis

Je vous expose mon soucis, qui m'a conduit jusqu'à ce poste.

Durant les vacances de noel, j'ai décidé d'avancer un maximum sur mon système Domotique (avec dashboard, stockage des valeurs pour statistiques, etc..). Toutes les minutes, mon système va interroger mon server Zway pour obtenir les dernières valeurs de mes capteurs et les stocke dans ma base de données SQLITE.

Le problème que j'ai vite rencontré, est que sur mon dashboard, j'affiche les dernières valeurs collectées de mes capteurs.
Ma requête SQL ressemblait à celle-ci :

  SELECT sv.Id, s.Name, sv.Value, s.Type, sv.Date
    FROM sensors s, sensors_value sv
    WHERE s.Id = sv.Id_sensor
    GROUP BY s.Id
    ORDER BY sv.Date DESC;

La table sensors stocke les données des capteurs : Nom, Type (température, humidité, detecteur de mouvement).
La table sensors_value quant à elle, stocke les données recoltées pour chacun des capteurs(identifiant du capteur, valeur, date de collecte).

Le soucis vite rencontré est le suivant :
La requête met 0.001s à s'exécuter lorsqu'elle vient d'être crééée et ne contient que peu de données.
Par contre, au bout de 2 jours, le temps d'exécution commence à atteindre les 0.5s........pas type top, surtout pour une application web qui doit charger également le php, image, css et javascript.

Voici la solution que j'ai mise en place pour pallier cette problèmatique.

Solution

J'ai décidé de créer une table intermédiaire, celle-ci ne contiendra que le nom, le type, les dernières valeurset la dernière date de collecte de chacun des capteurs.

Par contre au niveau de mon code, je ne voulais pas tout changer au niveau des insertions dans la bdd lors de la collecte.
J'ai donc décidé de faire appel aux Trigger.

Qu'est-ce qu'un Trigger ?

Il s'agit d'une procédure qui est déclenchée à la suite de certains événements (INSERT, UPDATE, DELETE).
Dans mon cas, je souhaite que le trigger se déclenche lors de l'insertion de données dans la table sensors_value.

Mise en place

Dans un premier temps, j'ai créée la table intermédiaire : recent_results

CREATE TABLE IF NOT EXISTS recent_results (
    Id INTEGER PRIMARY KEY, 
    Name TEXT, 
    Value TEXT, 
    Type TEXT, 
    Date DATE);

Puis j'ai mis en place le trigger.

CREATE TRIGGER IF NOT EXISTS optimizer_insert_sensor 
    AFTER INSERT ON sensors 
    BEGIN INSERT INTO recent_results (Id, Name, Type) 
    VALUES(NEW.Id, NEW.Name, NEW.Type ); 
END;

Je prend en compte qu'un capteur puisse être ajouté à mon système. Si tel est le cas, il sera rajouté à ma table recent_results, pour y stocker ses dernières valeurs.

CREATE TRIGGER IF NOT EXISTS optimizer 
    AFTER INSERT ON sensors_value 
    BEGIN UPDATE recent_results SET Date=NEW.Date, Value=NEW.Value WHERE Id = NEW.Id_sensor; 
END;

Et enfin mon trigger de mise à jour.....


Au niveau de mon code, je n'ai qu'à modifier ma table à utiliser pour mon dashboard, pour afficher les dernières valeurs de mes capteurs via la table recent_results et non plus sensors_value.

Et le temps d'exécution de mes requêtes reste inchangé : 0.001s
Pour connaitre le temps d'exécution rien de plus simple :

$ sqlite3 mabdd
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite> 

sqlite> .timer on

sqlite> select * from recent_results;
1|Salon_temperature|23.5|°C|2016-01-02 18:52:08
2|Salon_humidite|41|%|2016-01-02 18:52:08
3|Salon_webcam|http://mywebcam/webcam.jpg|url|
4|Salon_luminosite|0|lux|2016-01-02 18:52:08
5|Salon_detecteur_mouvement|false|alarm|2016-01-02 18:52:08
Run Time: real 0.001 user 0.001000 sys 0.000000