Discussion :

[Lo²]

Bonjour à tous,

J'ai lu en diagonale et je n'ai pas lu comment était exécutée cette requête.

Est-ce qu'il s'agit d'une requête lancée avec HExécuteRequete(..) ou HExécuteRequêteSQL(..) ?
Est-ce qu'il y a une différence sur la durée si on test l'un et l'autre ?

[JeAn-PhI]

bonjour,

est-ce que "explain" fonctionne en HFSQL ?
si la réponse est oui pouvez-vous faire le test et communiquer le résultat :

Code SQL :

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

1
2
3
4
5
6
7
8
 
EXPLAIN
SELECT Contact.CONTid, Contact.CONTcode, Contact.CONTcodeInt, Contact.CONTnom, Contact.CONTprenom, Contact.SOCid, Societe.SOCnom, TelFixe.CTCOMcodePays AS TelFixeCodePays, TelFixe.CTCOMvaleur AS TelFixeValeur, TelMobile.CTCOMcodePays AS TelMobileCodePays, TelMobile.CTCOMvaleur AS TelMobileValeur, Email.CTCOMvaleur AS EmailValeur
FROM CONTACT 
LEFT OUTER JOIN Societe ON Contact.SOCid = Societe.SOCid
LEFT OUTER JOIN Contact_Communication AS TelFixe ON Contact.CONTid = TelFixe.CONTid AND TelFixe.MDCcode = 1 AND TelFixe.MDCdefaut = 1
LEFT OUTER JOIN Contact_Communication AS TelMobile ON Contact.CONTid = TelMobile.CONTid AND TelMobile.MDCcode = 2 AND TelMobile.MDCdefaut = 1
LEFT OUTER JOIN Contact_Communication AS Email ON Contact.CONTid = Email.CONTid AND Email.MDCcode = 3 AND Email.MDCdefaut = 1

[philouZ]

le explain sur HFSQL est tout juste risible à moins que ça n'est changé depuis

[Djsven]

@tbc92
Je suis d'accord, le problème c'est que j'ai toujours bossé sur HFSQL CS avec Windev, donc tout mon code est basé sur HFSQL et je ne sais pas s'il serait adapté à un autre sgbd. Je pense notamment aux fonctions HVérifieStructure, HCreationSiInexistant(), HModifieStructure() qui je crois ne fonctionnent que sur HFSQL. Hors rien que ça c'est pratique, le client télécharge la mise à jour au démarrage de l'appli, et s'il y a eu une modification de la structure, elle est mise à jour automatiquement. Rien que ça, je ne sais pas comment le gérer autrement si je n'utilisais pas HFSQL. Et je suppose qu'il y a d'autres modifications à prévoir pour passer sur un autre SGBD. Mais je consens que ça serait plus rapide...

@Lo²
Elle est lancée via HExécuteRequêteSQL() car je construis la requête dynamiquement selon les critères sélectionnés par l'utilisateur. Pas possible avec une variable de type requête. Mais je ne pense pas que cela change grand chose en terme de rapidité.

@frenchsting
Pourtant je trouve que cette requête reste basique... Par contre elle est très rapide sur "peu" d'enregistrements, instantanée sur 1000/2000 contacts retournés, mais à partir de plusieurs dizaines de milliers, les perf chutent rapidement.
C'est pour ça que j'ai revu ma requête pour n'inclure qu'un seul LEFT OUTER JOIN sur ma table Contact_Communication afin de récupérer toutes les communications par défaut du contact, et ensuite en mémoire j'utilise une fonction qui remet en ligne le téléphone fixe, mobile et email par défaut pour chaque contact. Sur 50k contacts avec 3 comm par défauts (donc potentiellement 150k lignes à récupérer) c'est 4 secondes plus rapide que la requête qui récupère les 50k contacts avec directement les comm par défaut. C'est dommage quand même car je trouve que le code est moins propre, moins optimisé mais bon...

@JeAn-PhI
Alors je ne connaissais pas mais en effet on récupère quelque chose.

J'ai lancé la requête, sachant que je précise que sur la table Contact_Communication, je n'ai plus de clés composées et en clés avec doublon j'ai CONTid, MDCcode et MDCdefaut. Je précise ça par rapport à la suite de mon message. Voilà le résultat :



On remarque <index-scan key="SOCid"/> pour la jointure Société. On a donc une info sur la clé utilisée pour la recherche. Cependant, pour la table Contact_Communication, on a pas cette info. Je me suis demandé pourquoi, et j'ai remarqué que c'était probablement du au fait que le moteur utilise plusieurs clés pour la recherche et donc on ne les avait pas dans ce rapport. Etant donné que j'ai CONTid, MDCcode et MDCdefaut en clés dans ma table, le moteur doit utiliser ces 3 clés qui match avec le left outer join pour faire la recherche, d'où le fait qu'on n'a pas d'indication sur l'index key ici.


Lorsque j'ajoute la clé composée CONTid + MDCcode + MDCdefaut, on obtient :



=> Donc s'il y a une clé composée qui match avec le left outer join, la recherche en tient bien compte par rapport aux autres clés classiques. Sauf que l'exécution est 1 seconde plus lente que sans cette clé composée. SAUF à partir d'un certain nombre d'enregistrements. J'ai testé avec 250k communication (Défaut et non défaut), et l'exécution est légèrement plus rapide avec l'utilisation de la clé composé que sans. Mais pour 150k, c'était plus rapide sans cette clé composée...


J'ai fait un dernier test avec cette requête, mais cette fois-ci sans clé composée et uniquement CONTid en clé. Et là on voit que l'index key utilisée est CONTid. Et c'est sur cette clé que la recherche est la plus rapide. (1 seconde de gain par rapport aux précédentes)





J'ai effectué le même test pour la requête avec un seule LEFT OUTER JOIN sur la table Contact_Communication :

Code SQL :

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

1
2
3
4
5
SELECT Contact.CONTid, Contact.CONTcode, Contact.CONTcodeInt, Contact.CONTnom, Contact.CONTprenom, Contact.SOCid, Societe.SOCnom, Contact_Communication.MDCcode AS MDCcode, Contact_Communication.CTCOMvaleur AS CTCOMvaleur, Contact_Communication.CTCOMcodePays AS CTCOMcodePays
FROM CONTACT 
LEFT OUTER JOIN Societe ON Contact.SOCid = Societe.SOCid
LEFT OUTER JOIN Contact_Communication ON Contact.CONTid = Contact_Communication.CONTid AND Contact_Communication.MDCcode IN (1,2,3) AND Contact_Communication.MDCdefaut = 1
ORDER BY CONTid

Sans clé composée et avec CONTid, MDCcode et MDCdefaut en clés :




Même remarque que précédemment, pas d'index key d'affiché car je suppose que le moteur utilise à la fois la clé CONTid et MDCdefaut car lorsque j'ajoute une clé composé CONTid+MDCdefaut elle apparait bien :




Si j'utilise une clé composée CONTid+MDCcode+MDCdefaut ici, elle n'apparait pas, donc j'en déduis que le moteur utilise CONTid+MDCdefaut et ne tient pas compte du MDCcode à cause du IN dans ma requête => Pas de recherche sur l'index quand on utilise des IN.


Et dernier test, si je retire la clé MDCdefaut (même en conservant la clé sur MDCcode), j'ai bien l'index key en CONTid, ce qui confirme bien ce que j'ai dit précédemment. La recherche est plus rapide également lorsque le moteur utilise uniquement la clé CONTid, que lorsqu'il utilise CONTid et MDCdefaut (~250ms de plus) ou bien la clé composé CONTid+MDCdefaut (1seconde de plus)





Pour conclure, même sans clés composées, le moteur HFSQL peut utiliser plusieurs clés classiques pour optimiser la recherche, et ça reste souvent plus rapide qu'avec la clé composée, sauf à partir d'un certain nombre d'enregistrement. Même si le plus rapide reste quand le moteur utilise uniquement la clé CONTid pour ces deux requêtes.


Au final, d'après ces tests, j'ai retiré l'index sur MDCdefaut pour que la recherche ne s'effectue que sur CONTid pour ma requête avec 1 seul left outer join. Par contre, j'avais mis cette index sur MDCdefaut car j'utilise une autre requête avec un ORDER BY MDCdefaut, je ne sais pas s'il est préférable de mettre en clé les rubriques utilisées dans les ORDER BY ? Si quelqu'un sait ? Je sais que dans le rapport qu'on trouve dans le centre de contrôle HFSQL, ils conseillent de mettre en clés les rubriques utilisés dans le ORDER BY, mais vu qu'ils conseillent aussi de créer des clés composées pour améliorer la recherche alors que d'après mes tests elles ralentissent plus qu'autre chose, j'ai plus trop confiance.

[philouZ]

Pour tenter d'avancer, peut-être que tu peux créer une vue matérialisée où tu aurais déjà les informations en colonne. De cette manière ta jointure serait entre contact et ta vue materialisee ou les données seraient déjà préparées.

Il te suffit à chaque modification de moyen de communication de rafraichir cette vue.

Il faut comprendre que HFSQL est un embryon de SGBD. Tant que les requêtes sont simples, tout va bien. si tu complexifies trop les requêtes alors là il est dans les choux.

[tbc92]

En lisant uniquement le fichier contact, tu récupères les 50k lignes en 28ms.
Comptons le même temps pour le fichier Societe, et idem pour le fichier contact_communication. On ajoute un peu de temps pour ceci ou cela, on arrive à 100ms.

On ajoute un buffer, on arrive à 500ms.
Un SGBD normal va te renvoyer les résultats en moins de 500ms.

Tu peux expérimenter. Tu installes PostgreSQL sur un PC quelconque, tu copies ces 3 fichiers. Et tu testes.
En une journée, tu auras le résultat du test.

Certes, les parties hVérifieStructure vont être plus compliquées à mettre en place. Mais côté utilisateur, une requête qui répond en 1 seconde plutôt que 15 secondes, ça change tout.

Dans tes tests, 15 secondes au lieu de 16 secondes, c'est quasiment la marge d'erreur... Si demain un des fichiers devient plus gros, ce sera peut-être l'inverse.
En général, quand des utilisateurs se plaignent de performance, on regarde, et on trouve le truc pour passer de 16 secondes à 8 secondes, voire de 16 secondes à 2 secondes.
Entre les 2 traitements, celui qui utilise le bon plan d'exécution et l'autre, on a un rapport de 1 à 2, et souvent beaucoup plus.

c'était probablement du au fait que le moteur utilise plusieurs clés pour la recherche et donc on ne les avait pas dans ce rapport

Non.
J'ai des très gros doutes là-dessus. A ma connaissance, un traitement ne peut pas utiliser plusieurs clés pour un fichier. Ou alors, si quelqu'un a un lien qui expliquerait quand, et surtout comment, un SGBD fait pour utiliser différentes clés, alors je suis très intéressé.

ils conseillent de mettre en clés les rubriques utilisés dans le ORDER BY

Dans une requête avec 3 ou 4 fichiers, prenons le cas où il y a des OUTER Join.
Un des fichiers est 'maitre', le moteur de la BDD va lire ce fichier FIC01, et pour chaque ligne de FIC01, il va aller chercher dans FIC02 les lignes de même clé, puis dans FIC03 etc etc.
Il a besoin d'indexes pour aller facilement trouver les lignes utiles dans FIC02 puis FIC03. Dans ta requête, la clé composée (ContId,MDCdefault,MDCcode) semble parfaite.
Si on veut faire un ORDER BY sur une des colonnes de FIC01, alors cool, il suffit que le moteur ait les outils pour lire FIC01 dans l'ordre voulu, et c'est fini.
Mais si on veut faire un ORDER BY sur une des colonnes de FIC02 ou FIC03, c'est mort.
Si les jointures ne sont pas des OUTER JOIN mais des INNER JOIN, le moteur à le choix, quel est le fichier qui est lu en premier ... n'importe lequel des fichiers peut jouer ce rôle. C'est plus compliqué.

Les clés composées :
Pour ma part, je suis totalement fan.
Si on a une clé composée (COL01,COL02, COL03) et qu'on a besoin de COL01 uniquement, la clé composée convient. Si on a une clé (COL01,COL02,COL03) , alors pas besoin de clé (COL01) ni (COL01,COL02).

Au moins, en théorie ... En pratique, comment se comporte HFSQL, je ne sais pas.

[Djsven]

@philouZ
Le problème de la vue matérialisée c'est qu'elle va utiliser la même requête pour se rafraichir à chaque ajout / modification / suppression de moyen de communication non ? On va dire qu'une recherche sur 50k contacts n'a pas tellement de sens, la plus part du temps, les utilisateurs vont utiliser un filtre pour limiter le nombre de résultats retournés. Hors si je créé une vue qui se rafraichie constamment avec les 50k contacts et les moyens par défaut, j'ai peur que ça soit pire que mieux côté charge sur le serveur vu qu'il y aura bien plus d'ajout / modif / suppression de moyens de communication que de recherche sur l'intégralité des contacts de la base. (Ou alors je n'ai pas bien compris les vues, je n'en ai pas encore utilisé sur windev)

@tbc92
28ms c'était la requête count, donc un seul enregistrement retourné. Pour les 50k sur le même fichier sans jointure, ça prenait environ 500ms. Mais oui dans tout les cas, un sgbd normal sera bien plus performant, mais je ne comprends pas pourquoi ils n'améliorent pas HFSQL CS s'il est si mauvais... Car exécuter des requêtes avec 2-3 jointures, il n'y a rien d'exceptionnel la dedans.

Le résultat je le connais déjà de toute façon, mais je me renseignerai sur ce que ça implique de passer de hfsql cs à PostgreSQL ou SQL serveur ou autre (Déjà de voir lequel est meilleur) car de toute façon à terme si hfsql devient beaucoup trop lent, il n'y aura pas le choix.

Non.
J'ai des très gros doutes là-dessus. A ma connaissance, un traitement ne peut pas utiliser plusieurs clés pour un fichier. Ou alors, si quelqu'un a un lien qui expliquerait quand, et surtout comment, un SGBD fait pour utiliser différentes clés, alors je suis très intéressé.

Pourtant j'étais persuadé que c'était ça après mes tests. Le fait que l'index key ne soit affichée dans le rapport que quand j'utilise CONTid, ou une clé composé CONTid+MDCcode+MDCdefaut mais pas quand j'ai les clés CONTid, MDCcode et MDCdefaut, ça donnait l'impression que le traitement utilisait les 3. Car même s'il n'y a pas d'index key d'affichée dans le rapport, il en utilise bien une sinon ça serait bien plus long.

Les clés composées :
Pour ma part, je suis totalement fan.
Si on a une clé composée (COL01,COL02, COL03) et qu'on a besoin de COL01 uniquement, la clé composée convient. Si on a une clé (COL01,COL02,COL03) , alors pas besoin de clé (COL01) ni (COL01,COL02).

Au moins, en théorie ... En pratique, comment se comporte HFSQL, je ne sais pas.

D'après les tests, la clé composée (CONTid, MDCcode, MDCdefaut) sera utilisée uniquement si CONTid, MDCcode et et MDCdefaut sont présentes dans la jointure (hors IN). Si par exemple on utilise uniquement CONTid et MDCdefaut, la clé utilisée sera CONTid et non plus la clé composée. Je ne peux pas supprimer toutes les clés, dont les clés étrangères, et ne mettre que des clés composées car avec les liaisons dans l'analyse, ce n'est pas autorisé. "La rubrique intervenant dans la liaison doit être clé".


Après je vais voir à l'utilisation, comme j'ai dit, la recherche sur 50k contacts n'arrivera pratiquement jamais puisque les utilisateurs vont utiliser des filtres qui limitent le nombre de résultats retournés, donc la requête sera pratiquement instantanée la plupart du temps. Donc ce n'est pas spécialement gênant que ça prenne un peu de temps pour afficher les 50k contacts. Le seul truc gênant c'est si les autres requêtes sont ralenties pendant l'exécution d'une requête qui prend un peu de temps. Là c'est celle ci mais on pourrait très bien avoir une requête qui prend 4-5 secondes, même sans jointures, par rapport à un très grand nombre de résultats à retourner, et du coup on aurait le même problème.
J'ai remarqué que d'après l'analyseur de performance dans le centre de contrôle HFSQL, lorsque j'exécute la requête côté client, la courbe du CPU monte à 100%, étrange non? J'ai vu qu'il y avait une gestion possible du "load balancing", d'après l'aide, mais que par défaut elle est censée être active, c'est pourtant pas l'impression que ça donne...

[philouZ]

le but de la vue serait de mettre en colonne les infos de ta table communication pour aller plus vite au niveau des jointures. Un requête matérialisée qui stocke les données fonctionne comme une table classique. la requête n'est rééxécutée que si tu rafraichis la vue.

De cette manière tu peux faire une jointure entre contact et ta vue et afficher instantanément les données qui t'intéressent.

mais je ne comprends pas pourquoi ils n'améliorent pas HFSQL CS s'il est si mauvais

Pour PC Soft, HFSQL c'est déjà un SGBD d'une performance exceptionnelle et PC Soft ne supporte pas que l'on puisse critiquer un de ses produits. FSQL n'évoluera jamais car il faudrait tout remettre à plat pour en faire un véritable SGBD. C'est d'ailleurs la raison pour laquelle de nombreux développeurs ont abandonné cette base.

[tbc92]

Améliorer HFSQL, c'est compliqué. PCSoft doit faire des choix, et sur le créneau des SGBD, il y a 4 ou 5 autres produits qui sont nettement meilleurs que HFSQL.

Avant d'égaler ces produits, il faudrait investir plein d'argent, et trouver des compétences, ce qui est loin d'être évident.
Et égaler ces produits ne suffirait pas, il faudrait faire mieux.

L'intérêt de la gamme PCSoft, c'est qu'elle offre un package facile à installer, autonome, adapté pour que le débutant puisse faire un programme 'tout en un', sans avoir à maîtriser les produits PCSoft + autre chose.
Et pour ça, il faut un pseudo SGBD. HFSQL sert à ça.
La cible de PCSoft, c'est quand même essentiellement ce public de développeurs débutants.
Et pour les vrais développeurs, ils ont la possibilité de connecter Windev avec des vraies bases de données. Les accès natifs sont là pour ça quand c'est nécessaire. PCSoft sait que ces vrais SGBD sont incontournables, et que HFSQL ne pourra pas rivaliser.

[JeAn-PhI]

le explain sur HFSQL est tout juste risible à moins que ça n'est changé depuis

effectivement le explain ne sert à rien tellement c'est confus