Jump to content
  • 0
Ugo

Ordre Des Clefs De Liaisons Et Performances

Question

Bonsoir,

 

J'ai beau rechercher dans mes souvenirs, je ne pense pas m'être déjà posé la question ni même avoir croisé de discussions sur ce sujet. Si ma mémoire me fait défaut, alors considérez ce post comme une piqure de rappel...

 

Depuis le début de l’année un client se plaignait d’une baisse considérable de performances dans un traitement concernant plusieurs millions d’enregistrements, suite à une mise à jour de l’application. On était passé de 8 minutes à 15 minutes pour ce même traitement. Le nombre d’enregistrements n’avait pas véritablement évolué, ni le type de données stocké, mais je n’avais pas jusqu’à aujourd’hui identifié le problème, et le client ne m’avait plus sollicité jusqu’à ce vendredi.

Pour la petite histoire, j’ai passé la semaine dernière à optimiser l’une de nos bases SQL Server au terme d’un mois complet de monitoring des requêtes effectuées, en commençant par construire plusieurs nouveaux index couvrants, et en re-visitant les index multi-colonnes initialement définis, sur la base. Il y a des différences notables entre la gestion des index dans une base FileMaker et une base SQL en apparence, à commencer par l’index de base qui ne concernera que la seule clef primaire de toute table dans SQL. Pour ceux qui l’ignorent, on peut optimiser l’exécution des requêtes dans SQL en constituant des index, bien entendu, mais il en existe plusieurs types et ces index doivent être constitués en respectant plusieurs règles.

Je ne m’étends pas sur le sujet, aussi passionnant qu’il soit, ce n’est pas l’objet, mais disons qu’en premier lieu, la dispersion des données est toujours fondamentale dans ce type d'opération.

 

Une illustration courante de ce type d’index serait la constitution d’un index pour les colonnes « sexe », « nom » et « prénom » dans une table. La diversité des données étant plus forte pour un nom qu’un prénom, et bien entendu plus faible sur la colonne « sexe » qui ne contiendra que 2 valeurs potentielles, la meilleure combinaison sera toujours, de manière logique, du  plus large au plus faible, soit ici Nom + prénom + sexe.

 

Partant de ce postulat, je me suis donc intéressé à cette transposition possible dans FileMaker quand bien même le SQL interne de FileMaker ne comprend pas la constitution d’index multi-colonnes. La dispersion des données a nécessairement une influence quelque part me suis-je dis. On aura compris que lorsqu’une relation est établie entre deux occurrences dans FileMaker, et si les occurrences sont liées au travers d’une combinaison de clefs, cette relation pourrait être le fruit d’une clause WHERE, chacune suivant l’ordre dans lequel les clefs ont été agencées dans la fenêtre de définition du lien. Dans SQL Server, cet ordre n’a pas d’influence car l'Optimizer se charge d’analyser en amont le result set, mais on peut justement optimiser les processus en exploitant des index qui suivent l'ordre de la requête.

Mais dans FileMaker, c’est une toute autre histoire...

 

J’ai donc entrepris en début d’après-midi de reprendre l’analyse du problème que rencontrait mon client, en recherchant une voie autour des index existants sur les enregistrements de cette table, puis j’ai comparé la version 2014 et la version 2015, où j’ai constaté que j’avais modifié l’ordre des clefs dans l’un des liens définis dans cette base de données. En re-définissant l’ordre initial, le traitement est immédiatement repassé de 15 minutes à 8 minutes, et en modifiant encore l’ordre il est passé à 5 minutes…

 

Donc, conclusion, prenez bien soin de choisir avec minutie l’ordre d’agencement des clefs dans la définition d’un lien.

 

 

****

 

Pour ceux que ça intéresse, j'ai reproduis tout ceci sur un fichier test comprenant :

 

Une table avec 2 millions d'enregistrements, et  3 rubriques hors l'identifiant primaire :

  • key_text dont j’ai volontairement limité la dispersion à 500 valeurs différentes.
  • key_bool dont les valeurs varient de 1 à 0
  • key_genre_txt dont les valeurs varient de M à F

 

4 tables strictement identiques, ne comprenant que 3 clefs globales :

  • g_text_key
  • g_bool_key
  • g_genre_key

 

4 relations depuis chacune des tables "globales" vers la source de données en utilisant respectivement les ordres suivants, sans aucun tri :

  • Occurrence 1 :
  1. g_genre_key = key_genre_txt
  2. g_bool_key = key_bool_key
  3. g_test_key = key_text
  • Occurrence 2 :
  1. g_genre_key = key_genre_txt
  2. g_test_key = key_text
  3. g_bool_key = key_bool_key
  • Occurrence 3 :
  1. g_test_key = key_text
  2. g_bool_key = key_bool_key
  3. g_genre_key = key_genre_txt
  • Occurrence 4 :
  1. g_test_key = key_text
  2. g_genre_key = key_genre_txt
  3. g_bool_key = key_bool_key

J'ai construis un script se chargeant d'activer des enregistrements reliés dans une boucle qui traverse progressivement l'ensemble trouvé en redéfinissant les clefs globales d'origines pour une réactivation du même lien sur l'enregistrement successif, de sorte que tous les processus soient strictement les mêmes sur chacun des scripts, dans une boucle de 10.000 itérations.

 

Résultats obtenus après de multiples tests afin de le confirmer définitivement :

  1. Occurrence 1 : 88 secondes
  2. Occurrence 2 : 80 secondes
  3. Occurrence 4 : 65 secondes
  4. Occurrence 3 : 61 secondes

Plus avant, j'ai transformé le contenu du champ key_genre_txt pour que la dispersion s'étende à 10 valeurs, et c'est l'occurrence 4 qui présentait alors les meilleures performances, suivant logiquement ce qui est attendu en classifiant par le taux de dispersion.

 

Ces tests n'ont pas été conduits sur FileMaker 14, mais je ne m'attends pas à une quelconque transformation de ce comportement au final plus que logique.

Share this post


Link to post
Share on other sites

11 answers to this question

Recommended Posts

  • 0

Salut Ugo et merci pour ce cours sur l'ordre des clés à mettre du plus grand au plus petit, en fonction de la variété des contenus.
bonne nuit

Share this post


Link to post
Share on other sites
  • 0

Alors là, je te mettrais volontiers une étoile ;)

Merci !

Share this post


Link to post
Share on other sites
  • 0

Il faudrait comparer aussi avec une seule clef concaténée, "à la FM 6". Mon petit doigt me dit que ce serait encore plus rapide.

Share this post


Link to post
Share on other sites
  • 0

Salut,

Il faudrait comparer aussi avec une seule clef concaténée, "à la FM 6". Mon petit doigt me dit que ce serait encore plus rapide.

C'est certain. Mais cela aura un effet sur le poids du fichier sachant que l'influence de cet ordonnancement est généralement imperceptible dans un usage traditionnel. En reprenant à la source le problème identifié sur ce fichier, une alternative radicale aurait été de transformer la méthode pour ne plus exploiter de lien du tout mais travailler avec une simple succession de requêtes.

Maintenant, ce qui me surprend le plus c'est de jamais m'être posé la question avant.

Share this post


Link to post
Share on other sites
  • 0

Mais je crois qu'on se l'est posée ;)

Il me semble même qu'on avait fait des tests sans rien trouver, mais probablement sans penser à la dispersion mais plus à l'opérateur (égalités avant inégalités)

Ceci dit, je regardais justement de gros dev hier soir, et j'ai constaté qu'on avait appliqué ça très naturellement, soit par l'ordre des prédicats, soit en dédoublant les occurrences de table (a liée à a' par le critère le plus discriminant, puis a' liée à a'' par l'id et le critère suivant...

Share this post


Link to post
Share on other sites
  • 0

Coucou !

 

[cool :) merci :):) ]

 

Il me semble de mon coté que l'on avait discuté de l'importance de l'ordre des clefs de lien uniquement pour la création de fiche, il doit même y avoir un fichier dans le poste que je ne retrouve pas ( j'en ai pris le temps d'en lire d'autres, toujours aussi intéressant )

 

Merci Ugo, je pense que le nez dans le guidon empêche malheureusement de faire les batteries de test qui sont bénéfiques à tous et à l'appli !

Merci d'avoir pris le temps !

 

Bon Dimanche

 

Agnès

 

edit :

ce qui veut dire que la longueur du zkp que l'on choisi est importante également non ?

Share this post


Link to post
Share on other sites
  • 0

Bonne fete au passage ;)

 

Mais je crois qu'on se l'est posée ;)
Il me semble même qu'on avait fait des tests sans rien trouver, mais probablement sans penser à la dispersion mais plus à l'opérateur (égalités avant inégalités)
Ceci dit, je regardais justement de gros dev hier soir, et j'ai constaté qu'on avait appliqué ça très naturellement, soit par l'ordre des prédicats, soit en dédoublant les occurrences de table (a liée à a' par le critère le plus discriminant, puis a' liée à a'' par l'id et le critère suivant...

 

Oui, disons aussi que lorsqu'on doit disposer de plusieurs occurrences pour ce type de scénario, on procédera assurément comme ça, d'abord afin d'en faciliter la relecture. Mais combien de fois ai-je pris des clefs de l'une à l'autre au hasard pour reconstruire rapidement un lien, sans y prendre garde...

 

Pour compléter ce tests, à l'image de ce que j'entreprends dans une base SQL Server ( où parfois trop d'index tuent la performance des Insert/Delete et où des effets de bords sont ressentis au passage de certains caps en soumettant l'Optimizer a des analyses superflues ) j'ai ajouté quelques enregistrements, mais j'ai surtout entrepris de tester la réaction de FileMaker en conduisant les opérations suivantes :

 

Etape 1 : Récupération du fichier avec reconstruction des index :

Les performances ne sont plus les mêmes, on obtient alors :

  1. Occurence 2 : 97 secondes
  2. Occurrence 4 : 85 secondes
  3. Occurrence 3 : 80 secondes
  4. Occurence 1 : 76 secondes

 

J'ai reconduis plusieurs fois les opérations dans des enchainements variés, avec un résultat sensiblement identique à chaque fois. Soit donc inverse de ce que j'attendais de la première série de tests. L'index prend donc une fois reconstruit toute sa dominante apparemment.

 

Etape 2 : Basculement des clefs texte sur "Indexation minimal sans indexation automatique ( on est en 11 pour rappel )

  1. Occurence 2 : 97 secondes
  2. Occurrence 4 : 87 secondes
  3. Occurrence 3 : 83 secondes
  4. Occurence 1 : 68 secondes

Etape 3 : Export de la moitié des enregistrements et ré-import pour obtenir un fichier de 3 millions d'enregistrements dont 1 million fraîchement créés

  1. Occurence 2 : 153 secondes
  2. Occurrence 4 : 126 secondes
  3. Occurrence 3 : 122 secondes
  4. Occurence 1 : 103 secondes

Etape 4 : Suppression du 1/3 des enregistrements en prenant les 700.000 premiers enregistrements, et ré-indexation des 2 clefs texte ( option "Toute" )

  1. Occurence 2 : 128 secondes
  2. Occurrence 1 : 109 secondes
  3. Occurrence 4 : 101 secondes
  4. Occurence 3 : 98 secondes

Ce qui redevient conforme au premier test du premier post sur ce fil.

 

A chacun son interprêtation, mais l'écart entre chaque occurrence reste fondamentalement suffisamment explicite, quel que soit le schéma.

Share this post


Link to post
Share on other sites
  • 0

Coucou,

 

cela doit être ces révisions du Bac qui m'inspirent ;)

 

 

Il me semble de mon coté que l'on avait discuté de l'importance de l'ordre des clefs de lien uniquement pour la création de fiche, il doit même y avoir un fichier dans le poste que je ne retrouve pas ( j'en ai pris le temps d'en lire d'autres, toujours aussi intéressant )

...

ce qui veut dire que la longueur du zkp que l'on choisi est importante également non ?

 

Le choix des clefs d'une manière générale a une incidence, on a toujours souligné qu'une clef unique de type UUID prendra naturellement des octets supplémentaires comparativement à une clef numérique. Quand cette clef primaire devient étrangère, et qu'elle doit etre indexée aussi pour être exploitée dans un autre contexte, l'index constitué est naturellement plus complexe, même pour une disparité des données équivalentes.

 

Dans le cas de ce test, d'ailleurs, la chaine de texte était figée à 10 caractères.

 

Pour ce qui est du fil que tu évoques, je ne m'en souviens plus, mais assurément, dans mon test je n'effectue aucune création ( ni suppression d'ailleurs dont les effets seront tout à fait identique théoriquement si le lien autorise la suppression en cascade ), mais c'est le cas dans la base de données qui était incriminée, l'une des clefs permettant de basculer en mode création, d'où l'écart de performance encore plus important.

 

Maintenant, il faut encore une fois relativiser l'importance du choix que j'évoque ici, pour une TE présentant que quelques enregistrements, l'effet sera tout à fait négligeable. Maintenant, je suppose qu'on pourrait aussi jouer avec SQL pour vérifier si l'ordonnancement des WHERE a un impact sur le résultat, j'ai supposé jusqu'à présent que FileMaker ne dispose pas d'un Optimizer, mais je vous laisse jouer avec ça, vous me direz ;)

 

Bon Dimanche aussi  :fleur:

Share this post


Link to post
Share on other sites
  • 0

Excellent. Je vais aller vérifier mes liens....

 

Merci Ugo. :)

Share this post


Link to post
Share on other sites
  • 0

Bonjour,

 

J'ai un peu approfondi le sujet dans mon train du matin, en constituant 5 data-sets permettant de disposer de plusieurs séries de données représentatives pour mes tests.

Voici dans le doc html joint le résultat de mes tests, le pourcentage représentant la proportion d'enregistrements à forte dispersion dans chaque data-sets ( 0% = data-set constitué exclusivement d'enregistrements à faible dispersion, etc. )

 

Pour rappel, faible dispersion signifie que le nombre d'enregistrements correspondant est assez nombreux. Dans le fichier, le nombre d'occurrences d'une même chaine de texte varie de 5 à 2000, mais j'ai considéré pour les tests qu'une faible dispersion s'exprimait au-delà de 1700 et qu'une forte dispersion s'exprimait en dessous de 300 occurrences, ceci étant dû aux données du fichier lui-même.

 

Les occurrences sont renommées alphabétiquement mais elles correspondent dans l'ordre à celles décrites dans mon premier post sur ce fil.

 

Le fichier que j'ai utilisé est sommaire de sorte que seules les performances  sont mesurées, il est constitué d'un million d'enregistrements, le script se charge de récupérer une liste constituée au préalable, correspondant aux proportions désirées, de définir les globales utiles pour l'activation d'un lien dans une boucle de 3000 itérations pour chaque occurrence de tables.

 

On s'aperçoit nettement de l'influence du taux de dispersion sur les performances, le résultat exprimé correspond au délai en secondes pour chaque traitement.

 

PS: pas le temps de modifier, mais dans le dernier usecase, les deux premières colonnes sont inversées

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

usecase_ugo.html

Edited by Ugo

Share this post


Link to post
Share on other sites
  • 0

Magistral !

 

Bravo.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...