Comment tenir des registres précis en résumant plusieurs tableaux ?

Question

J'ai une base de données normalisée et j'ai besoin de produire fréquemment des rapports basés sur le Web qui impliquent des jointures entre plusieurs tables. Ces requêtes prennent trop de temps, j'aimerais donc que les résultats soient calculés afin de pouvoir charger les pages rapidement. Les tables que je résume sont fréquemment mises à jour, et j'ai besoin que le résumé reflète toutes les mises à jour effectuées jusqu'à présent.

Toutes les tables ont des clés primaires entières à incrémentation automatique, et j'ajoute presque toujours de nouvelles lignes et je peux m'arranger pour effacer les résultats calculés lorsqu'ils changent.

J'ai abordé un problème similaire où j'avais besoin d'un résumé d'une seule table en faisant en sorte d'itérer sur chaque ligne de la table, et de garder une trace de l'état de l'itérateur et de l'indice primaire le plus élevé (i.e. "highwater") vu. C'est bien pour une seule table, mais pour plusieurs tables, je finirais par garder une valeur highwater par table, et cela semble compliqué. Je pourrais aussi dénormaliser jusqu'à une seule table (avec des changements d'application assez importants), ce qui constitue un pas en arrière et ferait probablement passer la taille de ma base de données d'environ 5 Go à environ 20 Go.

(J'utilise sqlite3 pour le moment, mais MySQL est également une option).

Answer 1

Je vois deux approches :

1. Vous déplacez les données dans une base de données séparée, dénormalisées, en effectuant un certain précalcul, afin de les optimiser pour un accès rapide et la production de rapports (cela ressemble à un petit entrepôt de données). Cela implique que vous devez penser à certains travaux (scripts, application séparée, etc.) qui copient et transforment les données de la source à la destination. En fonction de la façon dont vous voulez que la copie soit effectuée (complète/incrémentielle), de la fréquence de la copie et de la complexité du modèle de données (à la fois source et destination), cela peut prendre un certain temps pour mettre en œuvre et ensuite pour optimiser le processus. Elle présente l'avantage de ne pas toucher à votre base de données source.

2. Vous conservez la base de données actuelle, mais vous la dénormalisez. Comme vous l'avez dit, cela pourrait impliquer des changements dans la logique de l'application (mais vous pourriez trouver un moyen de minimiser l'impact sur la logique utilisant la base de données, vous connaissez la situation mieux que moi :) ).

Answer 2

Vous pouvez créer des déclencheurs.

Dès que l'une des valeurs calculées change, vous pouvez effectuer l'une des actions suivantes :

• Mettre à jour le champ calculé (Préféré)
• Recalculez votre tableau récapitulatif
• Enregistre un drapeau indiquant qu'un nouveau calcul est nécessaire. La prochaine fois que vous aurez besoin des valeurs calculées, vérifiez d'abord cet indicateur et effectuez le nouveau calcul si nécessaire.

Exemple :

CREATE TRIGGER update_summary_table UPDATE OF order_value ON orders 
BEGIN
  UPDATE summary 
    SET total_order_value = total_order_value 
                          - old.order_value 
                          + new.order_value 
    // OR: Do a complete recalculation
    // OR: Store a flag
END;

Plus d'informations sur les déclencheurs SQLite : http://www.sqlite.org/lang_createtrigger.html

Answer 3

Les rapports peuvent-ils être rafraîchis de manière incrémentielle, ou faut-il procéder à un nouveau calcul complet pour retravailler le rapport ? S'il s'agit d'un recalcul complet, vous souhaitez simplement mettre en cache l'ensemble des résultats jusqu'à la prochaine actualisation. Vous pouvez créer quelques tables pour contenir la sortie du rapport (et une table de métadonnées pour définir les versions de sortie du rapport qui sont disponibles), mais la plupart du temps, c'est trop et il est préférable de sauvegarder les résultats de la requête dans un fichier ou un autre magasin cache.

S'il s'agit d'un rafraîchissement incrémentiel, vous avez de toute façon besoin des plages de PK pour travailler, et vous voudriez donc quelque chose comme vos données sur les hautes eaux (sauf que vous pourriez vouloir stocker les paires min/max).

Answer 4

Finalement, je me suis arrangé pour qu'une seule instance de programme effectue toutes les mises à jour de la base de données et maintienne les résumés dans son tas, c'est-à-dire pas du tout dans la base de données. Cela fonctionne très bien dans ce cas mais serait inapproprié si j'avais plusieurs programmes faisant des mises à jour de la base de données.

Answer 5

Vous n'avez rien dit de votre stratégie d'indexation. Je m'y pencherais d'abord, pour m'assurer que vos index sont bien couverts.

Ensuite, je pense que l'option de déclenchement discutée est également une très bonne stratégie.

Une autre possibilité consiste à alimenter régulièrement un entrepôt de données avec un modèle adapté à la production de rapports à haute performance (par exemple, le modèle Kimball).