Discussion :

[lefju cabro]

Bonjour,

J'ai une application qui effectue des acquisitions de signaux sur 16 voies. Toutes les 10 secondes, je dois stocker 40 valeurs réelles par voie --> au total 640 valeurs réelles sont à stocker toutes les 10 secondes.

Voici quelques explications de ma base dont de schéma est en pièce jointe :
- table DEF contient la définition des voies (16 voies)
- table INDIC contient la définition des indicateurs à stocker (40 indics)
- table SEG contient les infos d'un segment (1 segment = plage de 10s)
- table VAL_INDIC est la table contenant les valeurs réelles (40 valeurs/voie/segment). Cette table contient 3 clefs étrangères pour les tables DEF, INDIC et SEG.

La tâche d'insertion est la suivante:
- au bout de 10 secondes, j'insère un segment dans SEG
- je parcourt les 16 voies et pour chaque voie j'insère les 40 valeurs

Cette tâche fonctionne mais les temps d'insertions sont de plus en plus longs, la croissance est quasiment linéraire:
- 800ms pour le segment 1 (= base vide)
- 4300ms pour segment 5300

Mon problème est qu'au bout d'un moment l'insertion prendra plus de temps que l'acquisition est mon application va planter

En cherchant sur le forum, j'ai trouvé quelques pistes que j'ai déjà mis en place:
- en début d'acquisition, je retire les 3 clefs étrangères de la table VAL_INDIC. Celles-ci sont remises en fin d'acquisition.
- je n'utilise pas le générateur pour incrémenter le ID de la table VAL_INDIC: je recherche le max au début de l'insert puis je l'incrémente. Pas de problème à priori car ma base est mono-poste, mono-user.
- les 640 insertions sont dans une transaction, le commit est uniquement fait à la fin.

Je gagne en rapidité mais malgré tout les temps d'insertions sont de plus en plus longs:
- 800ms pour le segment 1 (= base vide)
- 2000ms pour segment 5300

D'où mes
- ce comportement est-il normal ?
- mon schéma de base base est-il correct ? Est-ce que je peux l'améliorer ?
- est-il possible de stabiliser ces temps d'insertions ? Si les 640 insertions prennent 4000ms, pas grave mais il ne faut pas que ce temps augmente.

Merci d'avance pour vos conseils.

[SergioMaster]

Bonjour,

en premier survol car je suis très pris

Cette tâche fonctionne mais les temps d'insertions sont de plus en plus longs, la croissance est quasiment linéraire:

vérifier que la transaction est bien fermée entre chaque blocs d'insertions.
ce qui à priori est le cas,

les 640 insertions sont dans une transaction, le commit est uniquement fait à la fin.

mais comment sont fait ces 640 insertions? via PSQL , via un programme ?

[lefju cabro]

Bonjour SergioMaster,

Merci pour ta réponse.

vérifier que la transaction est bien fermée entre chaque blocs d'insertions.

Oui les transactions sont fermées à chaque commit

mais comment sont fait ces 640 insertions? via PSQL , via un programme ?

Les insertions sont exécutées dans le programme d'acquisition (Delphi Berlin / FB 3 / FireDAC). Je pense qu'en passant par un thread d'insertion je "masquerai" mes problèmes de lenteur et au mon appli ne plantera pas. Mais ça ne me dira pas pourquoi les temps d'insertions augmentent

[SergioMaster]

Bonsoir,
Bon, ces réponses, je m'y attendais un peu sauf peut être l'utilisation de FB 3

reste les pistes des options de Firedac (très nombreuses)
comment sont faites les insertions : par excute direct, par un SQL 'INSERT INTO ......' + params+execSQL ou par Edit+...+ Post ?
et quid de l'utilisation du ArrayDML (chapitre 15 de Firedac in Depth), j'ai pu voir cette technique en action c'est (y a pas de mots) environ 4 fois plus rapide !
la même demo se retrouve sur le net mais je n'ai plus l'adresse en tête, il y a la video du regretté Pawel Glowacki

(voir vers la 15 mn de la vidéo) en tout cas, l'arrayDML correspond parfaitement à la problématique.

Autre chose, Firedac est "vendu" et certifié FB 2.5, c'est peut être un des petits problèmes non encore indiqué avec la version FB 3 ?

[Artemus24]

Salut à tous.

Quand il y a un problème de lenteur alors que la table grossit, je me demande si vous avez des index sur les critères de vos insertions ?

je parcours les 16 voies et pour chaque voie j'insère les 40 valeurs

Comment faites-vous ces parcours ?

Quel est la volumétrie de vos tables ?
Il serait intéressant de nous fournir un exemple de ce que vous faites.

@+

[SergioMaster]

Bonjour,

En cherchant sur le forum, j'ai trouvé quelques pistes que j'ai déjà mis en place:
- en début d'acquisition, je retire les 3 clefs étrangères de la table VAL_INDIC. Celles-ci sont remises en fin d'acquisition.
- je n'utilise pas le générateur pour incrémenter le ID de la table VAL_INDIC: je recherche le max au début de l'insert puis je l'incrémente. Pas de problème à priori car ma base est mono-poste, mono-user.
- les 640 insertions sont dans une transaction, le commit est uniquement fait à la fin.

Phase 1
Après une lecture plus approfondie, il me semble que la première étape " je retire les 3 clefs étrangères" ne devrait se faire qu'en début de programme d'acquisition et subséquemment n'être réactivés qu'en fin de ce dernier. Il me semble d'ailleurs que c'est cette phase qui doit être consommatrice de temps (à chaque fois le contrôle de réactivation se fait sur la table entière) d'où ma question : comment se fait cette étape ?

Phase 2
Le générateur, alors là quel est le plus rapide ? le générateur avec trigger, le SELECT Max(Id) ou une colonne IDENTITY (GENERATED BY DEFAULT AS IDENTITY) j'ai des doutes sur le SELECT Max(Id) sauf si, encore une fois il n'est fait qu'une seule fois en début de programme et non en début de séquence d'acquisition.

Phase 3 : voir mon précédent post

je me demande si vous avez des index sur les critères de vos insertions ?

pour moi il est évident que ID, champ incrémenté, est un index primaire.


JE RÉFLÉCHI TOUT HAUT
Comme il s'agit d'une appli mono-poste, que les ID sont gérés par le programme, et qu'il s'agit d'insertions je me demande si cet index non plus ne devrait pas être suspendu (pas supprimé) et ce après le SELECT MAX(VAL_INDIC.id) fait une seule fois .

s'il y avait un GENERATEUR aka SEQUENCE (avec aucun Trigger pour l'incrémenter, ou alors un trigger suspendu comme pour les index) celui-ci pourrait être mise à jour à chaque fin d'acquisition et ce sans nuire (à mon avis) à la vitesse puisqu'il ne s'agirait que d'une seule instruction SQL, mais éviterai ce SELECT MAX(VAL_INDIC.id) consommateur (quoique VAL_INDIC.id étant un index ...)

Bon, je vous laisse sur ces pistes, questions et réflexions

[lefju cabro]

Bonjour,

Merci pour vos messages.

comment sont faites les insertions : par excute direct, par un SQL 'INSERT INTO ......' + params+execSQL ou par Edit+...+ Post ?

Mes insertions sont effectuées par SQL 'INSERT INTO ......' + params+execSQL

quid de l'utilisation du ArrayDML (chapitre 15 de Firedac in Depth), j'ai pu voir cette technique en action c'est (y a pas de mots) environ 4 fois plus rapide !

Je ne connais pas cette technique je vais regarder ce que je peux en faire

Comment faites-vous ces parcours ?
Quel est la volumétrie de vos tables ?
Il serait intéressant de nous fournir un exemple de ce que vous faites.

Mon algo actuel d'acquisition et d'insertion de données et le suivant:

• En début d'acquisition:

- lecture unique de toutes les voies de mesures de la table DEF (= 16 enregistrements)
- lecture unique de toutes les définitions d'indicateurs de la table INDIC (= 40 enregistrements)
- suppression des clefs étrangères de la table VAL_INDIC

• A la fin de chaque segment (= après 10 secondes d'acqui), je stocke mes valeurs d'indicateurs en BDD:

- création d'un enregistrement dans la table SEG
- ouverture d'une transaction pour l'insertion des 640 enregistrements de VAL_INDIC
- récupère le MAX(id) de la table VAL_INDIC
- parcourt les voies de mesure

- parcourt les indicateurs

- ajout des valeurs des indicateurs pour la voie de mesure, le segment et l'indicateur donné

- commit la transaction

• En fin d'acqui:

- ajout des clefs étrangères de la table VAL_INDIC


En terme de volumétrie, j'ai fait tourné mon acqui tout le we:
- 16 voies de mesures dans DEF
- 40 indicateurs dans INDIC
- 22 925 segments dans SEG
- 14 672 000 valeurs d'indicateurs dans VAL_INDIC

Phase 1
Après une lecture plus approfondie, il me semble que la première étape " je retire les 3 clefs étrangères" ne devrait se faire qu'en début de programme d'acquisition et subséquemment n'être réactivés qu'en fin de ce dernier.

Oui, c'est fait de cette manière: les clefs sont supprimées en début d'acqui et recrées en fin d'acqui.

Phase 2
Le générateur, alors là quel est le plus rapide ? le générateur avec trigger, le SELECT Max(Id) ou une colonne IDENTITY (GENERATED BY DEFAULT AS IDENTITY) j'ai des doutes sur le SELECT Max(Id) sauf si, encore une fois il n'est fait qu'une seule fois en début de programme et non en début de séquence d'acquisition.

Le select MAX(id) est fait à chaque appel de ma procédure de stockage en BDD: sur cette base la requête (qui a été appelée 22 925 fois) prend 4.0s. Je vais déplacer cette procédure pour n'être appelé qu'une fois en début d'acqui

Phase 3 : pour moi il est évident que ID, champ incrémenté, est un index primaire.

C'est une clef primaire mais je ne vois pas d'index...

[SergioMaster]

C'est une clef primaire mais je ne vois pas d'index...

qui dit clé primaire dit index qu'il est facile de désactiver
par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
UPDATE  RDB$INDICES r SET  r.RDB$INDEX_INACTIVE=1
where r.RDB$RELATION_NAME='VAL_INDIC'

à noter qu'il faut ques les foreign key soient déjà supprimées (sinon erreur d'intégrité)

[Artemus24]

Salut à tous.

Oui, c'est fait de cette manière: les clefs sont supprimées en début d'acqui et recrées en fin d'acqui.

Ce sont les clefs étrangères ou les index qui sont supprimés en début d'acquisition ?
Pourquoi supprimez-vous vos index ? Le but d'un index est de vous faire gagner du temps !
Et si vous supprimez vos clef étrangères, vous risquez d'avoir un problème d'intégrité.

Le select MAX(id) est fait à chaque appel de ma procédure de stockage en BDD

Pourquoi utilisez-vous le "max(id)" alors que FireBird sait gérer tout seul les incrémentations de la colonne "id" ?
Cela dépend de la version que vous utilisez, mais je pense soit au "gen_id" dans un trigger en FB2.5 ou soit à "generated by default as identity" en FB3.0.

C'est une clef primaire mais je ne vois pas d'index...

En général, une clef primaire est un index.

JE RÉFLÉCHI TOUT HAUT

C'est votre droit de détailler votre réponse. Et puis, ce n'est pas genre de critiquer l'opinion des autres.

Comme il s'agit d'une appli mono-poste, que les ID sont gérés par le programme, et qu'il s'agit d'insertions je me demande si cet index non plus ne devrait pas être suspendu (pas supprimé) et ce après le SELECT MAX(VAL_INDIC.id) fait une seule fois

C'est bien d'émettre des hypothèses, mais je n'aime pas raisonner dans le vide.

s'il y avait un GENERATEUR aka SEQUENCE (avec aucun Trigger pour l'incrémenter, ou alors un trigger suspendu comme pour les index) celui-ci pourrait être mise à jour à chaque fin d'acquisition et ce sans nuire (à mon avis) à la vitesse puisqu'il ne s'agirait que d'une seule instruction SQL, mais éviterai ce SELECT MAX(VAL_INDIC.id) consommateur (quoique VAL_INDIC.id étant un index ...)

A quoi va servir ce max(id) ? Je suppose pour les clef étrangères.

@+

[lefju cabro]

Salut,

Ce sont les clefs étrangères ou les index qui sont supprimés en début d'acquisition ?

Je supprime uniquement les clefs étrangères, je ne touche pas aux index.

Et si vous supprimez vos clef étrangères, vous risquez d'avoir un problème d'intégrité.

Oui j'y ai pensé... Pour éviter les problèmes d'intégrité je fais des tests (FindKey sur les petites tables) pour m'assurer que l'insertion sera OK.

Pourquoi utilisez-vous le "max(id)" alors que FireBird sait gérer tout seul les incrémentations de la colonne "id" ?
Cela dépend de la version que vous utilisez, mais je pense soit au "gen_id" dans un trigger en FB2.5 ou soit à "generated by default as identity" en FB3.0.

J'ai lu puis testé que l'utilisation du générateur pour l'import massif de données pouvait ralentir les insertions. Grâce à vos conseils, je cherche max(id) une seule fois en début d'acqui, puis incrémente manuellement à chaque fois que j'en ai besoin.

En écrivant cela je me dis que je pourrai utiliser le GEN (à la place de max(id)) une fois au début d'acqui, j'insère tout ce que je veux en incrémentant manuellement mon Id puis à la fin de mon acqui, je mets à jour la valeur de mon générateur...

[lefju cabro]

Je teste la méthode suivante (en rouge les modifications par rapport à la première méthode présentée):

• En début d'acquisition:

- lecture unique de toutes les voies de mesures de la table DEF (= 16 enregistrements)
- lecture unique de toutes les définitions d'indicateurs de la table INDIC (= 40 enregistrements)
- suppression des clefs étrangères de la table VAL_INDIC
- récupère le MAX(id) de la table VAL_INDIC

• A la fin de chaque segment (= après 10 secondes d'acqui), je stocke mes valeurs d'indicateurs en BDD:

- création d'un enregistrement dans la table SEG
- ouverture d'une transaction pour l'insertion des 640 enregistrements de VAL_INDIC
- parcourt les voies de mesure

- parcourt les indicateurs

- ajout des valeurs des indicateurs pour la voie de mesure, le segment et l'indicateur donné avec les arrayDML

- commit la transaction

• En fin d'acqui:

ajout des clefs étrangères de la table VAL_INDIC

J'ai fait tourné l'ensemble pendant 1h30, ça me paraît bien. Pour avoir de des données + représentatives, je le ferai tourner cette nuit.

[SergioMaster]

En écrivant cela je me dis que je pourrai utiliser le GEN (à la place de max(id)) une fois au début d'acqui, j'insère tout ce que je veux en incrémentant manuellement mon Id puis à la fin de mon acqui, je mets à jour la valeur de mon générateur...

cela faisait partie de mes réflexions "Tout haut" non argumentées

J'ai lu puis testé que l'utilisation du générateur pour l'import massif de données pouvait ralentir les insertions.

oui, s'il y a trigger, non s'il est géré manuellement.
il suffirai de remplacer cette récupère le MAX(id) de la table VAL_INDIC par récupére la valeur du générateur
et en fin de traitement Mettre à jour la valeur du générateur.

C'est bien d'émettre des hypothèses, mais je n'aime pas raisonner dans le vide.

J'ai déjà fait ce genre de choses : désactivation de l'index primaire, insertions importantes, réactivation de l'index, ceci en mode mono-utilisateur (shutdown de la base sauf pour le SYSDBA) pendant le traitement, comme dans ce cas il s'agit d'une appli mono poste c'est encore plus facile.

Oui j'y ai pensé... Pour éviter les problèmes d'intégrité je fais des tests (FindKey sur les petites tables) pour m'assurer que l'insertion sera OK.

Peut être que l'utilisation liste d'objets serait préférable pour la vitesse des tests ,
Toutefois, les petites tables sont en mémoire dans le dataset donc si un index (pas Firebird mais Firedac, ne pas confondre) est créé sur ce dataset cela sera encore plus rapide.

- ajout des valeurs des indicateurs pour la voie de mesure, le segment et l'indicateur donné avec les arrayDML

n'est-ce pas que c'est plus rapide et dans ce cas totalement adapté ?

[Artemus24]

Salut à tous.

Je supprime uniquement les clefs étrangères, je ne touche pas aux index.

Pourquoi ?

Pour éviter les problèmes d'intégrité je fais des tests (FindKey sur les petites tables) pour m'assurer que l'insertion sera OK.

Travail inutile ! Laissez FireBird se charger des problèmes d'intégrité de votre base de données.

J'ai lu puis testé que l'utilisation du générateur pour l'import massif de données pouvait ralentir les insertions.

On lit beaucoup de conneries sur le net. La seule façon de savoir si c'est vrai, est de faire le test.

Nous ne savons toujours pas quelle version de FireBird utilisez-vous ?

En écrivant cela je me dis que je pourrai utiliser le GEN (à la place de max(id)) une fois au début d'acqui,

Sur ce point, je pense que vous allez gagner du temps.

suppression des clefs étrangères de la table VAL_INDIC

Je ne comprends pas l'intérêt de faire ceci ?

Ce qui serait intéressant, c'est de nous montrer vos requêtes et non de nous dire ce que vous faites.

oui, s'il y a trigger, non s'il est géré manuellement.

Cela dépend comment on gère ce "generateur". Il n'y a pas une seule solution à un problème posé.

J'ai déjà fait ce genre de choses : désactivation de l'index primaire, insertions importantes, réactivation de l'index, ceci en mode mono-utilisateur (shutdown de la base sauf pour le SYSDBA) pendant le traitement, comme dans ce cas il s'agit d'une appli mono poste c'est encore plus facile.

Je l'ai déjà fait aussi, en faisant en sorte de trier au préalable les lignes avant de les insérer.

Mais le problème ici est différent. Il s'agit d'une grappe de 640 lignes à insérer.
La quantité est faible, mais je n'ai pas bien compris ce que "lefju cabro" entend par "parcourt les voies de mesure, parcourt les indicateurs" ?
Est-ce qu'il le fait pour chaque ligne ? Ou le fait-il dans une requête global ?

n'est-ce pas que c'est plus rapide et dans ce cas totalement adapté ?

Je suis totalement incompétent pour la partie développement.
Mais le problème ne se situe-t-il pas plutôt dans la partie FireBird ?

@+

[SergioMaster]

Bonsoir

Travail inutile ! Laissez FireBird se charger des problèmes d'intégrité de votre base de données.

non car on fait de l'insertion en paquet, il serait dommage d'avoir à rechercher après des heures d'insertions où se situe l'erreur

On lit beaucoup de conneries sur le net. La seule façon de savoir si c'est vrai, est de faire le test.

quand les indications viennent des conférences Firebird, j'ai tendance à les croire

Nous ne savons toujours pas quelle version de FireBird utilisez-vous ?

il a été indiqué qu'il s'agissait de FB3 (post n°3)

suppression des clefs étrangères de la table VAL_INDIC

Je ne comprends pas l'intérêt de faire ceci ?

il n'y a pas possibilité de suspendre un index d'intégrité, le seul moyen est donc de supprimer la contrainte référentielle, l'intérêt de la suppression ? des vérifications de contraintes en moins puisque vérifiée déjà par programmes

Cela dépend comment on gère ce "generateur". Il n'y a pas une seule solution à un problème posé.

bon, on va dire pour simplifier qu'il y en a 3 sortes
le générateur sans trigger pour le géré (donc géré par programme or Firebird)
le générateur avec trigger , trigger qui se charge de l'auto incrémentation et la gestion des "sauts forcés" (le plus classique)
et le petit dernier l'IDENTITY, celui que j'aime le moins car on n'a presque pas la main dessus
il est évident que je parlai du premier ou du second s'il l'on sait (par SQL c'est possible) désactiver le trigger associé

Je suis totalement incompétent pour la partie développement.
Mais le problème ne se situe-t-il pas plutôt dans la partie FireBird ?

non, le problème était double,
d'une part le coté Firebird avec une gestion de l'index primaire non suspendue le temps des insertions
du coté programmation avec un mauvais choix pour l'obtention de l'ID et surtout une méconnaissance d'une partie de Firedac (ArrayDML) et peut être aussi des Index des composants Firedac <= ce n'est pas parce qu'il y a un index sur la table que celui-ci est utilisé par le FindKey, cette partie est un piège courant et mal connu.

[Artemus24]

Salut SergioMaster.

non car on fait de l'insertion en paquet, il serait dommage d'avoir à rechercher après des heures d'insertions où se situe l'erreur

C'est au niveau de FireBird qu'il faut vérifier, avant d'insérer quoi que ce soit surtout pas grappe, que la nouvelle clef étrangère existe belle et bien dans la table parente.
Si elle n'existe pas, alors faire en sorte de l'y insérer, afin que l'insertion par grappe se passe correctement.

quand les indications viennent des conférences Firebird, j'ai tendance à les croire

Oui, moi aussi, mais je parlais des didacticiels et autres sites qui donnent des conseils qui sont pour la plupart du temps périmés.

il a été indiqué qu'il s'agissait de FB3 (post n°3)

Au tant pour moi !

l'intérêt de la suppression ? des vérifications de contraintes en moins puisque vérifiée déjà par programmes

Je ne suis pas du tout partisan de supprimer les contraintes d'intégrités, même si elles sont déjà faites par programme.
Encore que, je ne comprends pas trop comment elles sont faites par programme, si elles dépendent de la base de données.

En principe, je passe par une table de travail, avant de faire de la répartition de ces lignes dans les différentes tables de la base de données.
Tout le travail consiste à préparer la grappe d'insertion afin de ne pas obtenir un rejet.

et le petit dernier l'IDENTITY, celui que j'aime le moins car on n'a presque pas la main dessus

Question de culture, je suppose, car sous MySql, il existe la déclarative "auto_increment" qui fait exactement la même chose que l'IDENTITY sous FireBird.
Et c'est ce que je préconise de faire dans la version FireBird 3.0.

non, le problème était double,

Le langage est destiné à faire de la mise en forme des requêtes, voire aussi des contrôles avant les insertions.
Selon moi, le gros du travail se fait dans FireBird.

Tout ce qu'énonce "lefju cabro" se résume à un chargement :

J'ai une application qui effectue des acquisitions de signaux sur 16 voies. Toutes les 10 secondes, je dois stocker 40 valeurs réelles par voie --> au total 640 valeurs réelles sont à stocker toutes les 10 secondes.

Et qu'est-ce qu'il entend par "acquisition" ?

Pour le reste, je pense que vous êtes bien plus compétent que moi.

@+

[SergioMaster]

Bonjour,

Et qu'est-ce qu'il entend par "acquisition" ?

Je pense qu'il s'agit tout simplement d'une batterie de capteurs genre températures, hydrographie, etc. ou de tout autre genre de l'informatique industrielle voire aéronautique mais cela n'a pas beaucoup d'importance.

C'est au niveau de FireBird qu'il faut vérifier, avant d'insérer quoi que ce soit surtout pas grappe, que la nouvelle clef étrangère existe belle et bien dans la table parente. Si elle n'existe pas, alors faire en sorte de l'y insérer, afin que l'insertion par grappe se passe correctement.

on est d'accord mais dans cette configuration, il n'y a pas de nouvelles clés (si ce n'est le SEGMENT créé juste avant le bloc de valeurs, si j'ai bien compris)
pour ce qui est des optimisation je faisais référence à cette série de diapositives points 21 et 23 entre autres, je suis sûr que c'est plus détaillé dans la conférence mais je n'ai pas mis le doigt dessus

[lefju cabro]

Bonjour,

Les tests de cette nuit sont concluants: les 640 insertions prennent 200ms ce qui est parfait !! Il y a une légère augmentation du temps d'insertion mais c'est quasi-négligeable.

Le fait de ne faire la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select (Max(Id)

qu'une seule fois en début d'acquisition ET d'utiliser les ArrayDML ont grandement diminué les temps d'insertions.

Merci à vous 2 pour vos conseils.

Par curiosité, je vais continuer mes tests en remettant mon générateur avec trigger (pour obtenir l'ID pour chaque insertion) et/ou les clefs étrangères:
Je laisse les 3 clefs étrangères (provenant des tables SEG, DEF et INDIC) sur ma table VAL_INDIC, les temps d'insertions sont identiques.
Je laisse les 3 clefs étrangères (provenant des tables SEG, DEF et INDIC) sur ma table VAL_INDIC + ajout du générateur par trigger sur la table VAL_INDIC sont plus élevés mais restent convenables.