Discussion :

[droliprane]

Bonjour à tous,

maintenant que j'ai migré 2 années de données dans ma gpao "maison" je teste mes interfaces et je me rends compte que ça rame pas mal. Ca vient peut être de la façon dont j'ai construit mes fenêtres, qui comportent systématiquement des tables remplies par requêtage et filtrage via des listes.

L'exemple ici est l'interface d'interrogation des factures. J'ai migré plus de 4500 factures, et mon interface fait une pagination en n'affichant que 100 lignes à l'écran, et éventuellement on change de page.

Avant même l'affichage de cet écran, j'attends 20 secondes que ça charge (appli figée ne répond pas), ensuite ça se débloque et je vois ça :



J'ai testé avec l'analyseur de performances :



Et ce sont bien les 2 appels à HExécuteRequêteSQL qui consomment les 99,9% du temps de chargeFactures (voir code plus bas).

Pourtant ma requête est sensée me ramener seulement 100 lignes, ce qui n'est quand même pas le bout du monde. Alors je m'interroge sur le pourquoi de cette lenteur. Je me permets de coller le code la procédure chargeFactures, qui s'exécute chaque fois que je tape dans un champs ou que je sélectionne dans une liste.... J'ai essayé de la threader mais c'est encore plus long (affichage progressif mais très lent) !

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
PROCEDURE chargeFactures(LOCAL page_selectionnee est un entier = 1)
 
 
nb_requetes ++
// ça c'est pour ne pas exécuter la procédure inutilement à l'initialisation de l'interface
// car elle est déclenchée sur sélection d'un élément de chacune des listes Client, Commande ou Article 
// ici, ignore_n_premieres_requetes vaut 3
SI nb_requetes <= ignore_n_premieres_requetes ALORS RETOUR  
 
TableSupprimeTout(TABLE_Factures)
 
criteres est une chaîne = ""
criteres2 est une chaîne = ""
 
img est une chaîne = ""
 
SI LISTE_Commandes > 0 ALORS 
	criteres = " WHERE cc_id = " + LISTE_Commandes
SINON
	SI SAI_filtre1 <> "" ALORS
		criteres = " WHERE cc_num LIKE '%" + Replace(SAI_filtre1, "*", "%") + "%'"
	SINON
		// critere pour obligatoirement avoit un WHERE
		criteres = " WHERE fcl_id > 0"
	FIN
FIN
 
SI LISTE_Articles > 0 ALORS 
	criteres = criteres + " AND art_id = " + LISTE_Articles
SINON
	SI SAI_filtre2 <> "" ALORS
		criteres = criteres + " AND (art_reference LIKE '%" + Replace(SAI_filtre2, "*", "%") + "%' OR (fcl_article_id = 0 AND fcl_commentaire LIKE '%" + Replace(SAI_filtre2, "*", "%") + "%'))"
	FIN
FIN
 
SI LISTE_Clients > 0 ALORS
	criteres2 = "AND clt_id = " + LISTE_Clients
SINON
	criteres2 = "AND clt_code LIKE '%" + Replace(SAI_filtre, "*", "%") + "%'"
FIN
 
 
// PREMIERE REQUETE POUR CONNAITRE LE NOMBRE DE PAGES
Sql est une chaîne = "SELECT count(fc_id) AS nblignes FROM fc,clt, dev " + 
"WHERE fc_id IN (SELECT DISTINCT fcl_fc_id FROM " +
"(((fcl FULL OUTER JOIN cc ON fcl_bl_cc_id = cc_id) " +
"FULL OUTER JOIN ccl ON  fcl_bl_ccl = ccl_ligne) " +
"FULL OUTER JOIN art ON fcl_article_id = art_id) " +
criteres + ") AND fc_client = clt_id AND fc_devise = dev_id " + criteres2
 
 
Rs est une Source de Données
nblignes est un entier = 0
nbpages est un entier = 1
SI PAS HExécuteRequêteSQL(Rs,Cnx,hRequêteSansCorrection,Sql) ALORS
	Erreur("Erreur d'initialisation de la requête"+RC+HErreurInfo()+ RC + RC + Sql)
	RETOUR
FIN
SI HLitPremier(Rs) ALORS
	nblignes = Rs.nblignes
FIN
HLibèreRequête(Rs)
 
//info(nblignes)
 
 
critere_pagination est une chaîne = ""
SI COMBO_nb_lignes..ValeurAffichée <> "TOUT" ALORS
	premiere_ligne est un entier = ((page_selectionnee - 1) * Val(COMBO_nb_lignes..ValeurAffichée) + 1)
	derniere_ligne est un entier = premiere_ligne + Val(COMBO_nb_lignes..ValeurAffichée) - 1
	critere_pagination = "WHERE a.ligne >= " + premiere_ligne + " AND a.ligne <= " + derniere_ligne
 
	nbpages = PartieEntière(nblignes / Val(COMBO_nb_lignes..ValeurAffichée))
	SI PartieDécimale(nblignes / Val(COMBO_nb_lignes..ValeurAffichée)) > 0 ALORS
		nbpages ++
	FIN
	SI nbpages = 0 ALORS nbpages = 1
FIN
 
LIB_nbpages = "/ " + nbpages
ListeSupprimeTout(COMBO_page)
POUR i = 1 _A_ nbpages
	ListeAjoute(COMBO_page,i)
FIN
ListeSelectPlus(COMBO_page,page_selectionnee)
 
 
Sql = "SELECT* FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY fc_id Desc) AS ligne FROM fc,clt, dev " + 
"WHERE fc_id IN (SELECT DISTINCT fcl_fc_id FROM " +
"(((fcl FULL OUTER JOIN cc ON fcl_bl_cc_id = cc_id) " +
"FULL OUTER JOIN ccl ON  fcl_bl_ccl = ccl_ligne) " +
"FULL OUTER JOIN art ON fcl_article_id = art_id) " +
criteres + ") "+
"AND fc_client = clt_id AND fc_devise = dev_id " + criteres2 + ") a  " + critere_pagination
 
//trace(Sql)
 
 
SI PAS HExécuteRequêteSQL(Rs,Cnx,hRequêteSansCorrection,Sql) ALORS
	Erreur("Erreur d'initialisation de la requête"+RC+HErreurInfo()+ RC + RC + Sql)
	RETOUR
FIN
SI HLitPremier(Rs) ALORS
 
	TANTQUE PAS HEnDehors(Rs)
 
		img = "delete.png"
		TableAjoute(TABLE_Factures, Rs.fc_id + TAB + Rs.fc_num + TAB + Rs.clt_code + TAB + Rs.fc_total_ht + TAB + Rs.fc_taux_tva + TAB + Rs.fc_total_tva + TAB + Rs.fc_total_ttc + TAB + Rs.fc_emballage + TAB + Rs.fc_port + TAB + Rs.fc_assurance + TAB + Rs.fc_total_facture + TAB + Rs.dev_nom + TAB + Rs.fc_date + TAB + img)
		HLitSuivant(Rs)
	FIN
 
FIN
 
HLibèreRequête(Rs)
 
 
SI TABLE_Factures..Vide ALORS
	BTN_IMPRIMER..Grisé = Vrai
SINON
	BTN_IMPRIMER..Grisé = Faux
FIN

Ensuite quand j'essaye d'interagir avec l'interface, par exemple commencer à saisir un nom de client, ça prend entre 5 et 10 secondes à chaque caractère tapé, avant de retrouver la main dans le champs de saisie...

Bref, c'est pas possible de faire bosser les gens comme ça.

Il faut que je trouve une solution rapidement, j'aimerais déployer mon appli fin Janvier.

Toute aide est la bienvenue, merci d'avance.


EDIT : ça semble venir de la construction de ma requête Sql (la deuxième, la plus complexe à mon avis) car à titre de comparaison j'ai un écran d'interrogation des livraisons, qui me ramène encore d'avantage de lignes (125 pages de 100 lignes) et l'écran se charge initialement en moins de 3 secondes et se réactualise assez bien à chaque saisie...

[droliprane]

Ca semble venir des deux requêtes, tant celle qui sert à compter le nombre de factures, que celle qui les affiche

J'ai testé mes requêtes directement dans SQL Server, et la première (comptage) met 7-8 secondes à retourner le résultat

La seconde, met environ 15 secondes à chaque fois

Soit il est possible de construire mieux mes requêtes, soit je stocke dans ma table fcl (lignes de factures) des champs redondant avec les données qui existent dans les tables liées, mais ce qui m'éviterait de faire toutes ces jointures...

A savoir la référence article et le numéro de commande, stockés dans la ligne de facture, simplement pour faciliter le requêtage dans cet écran....

Je vais tester

[michel.souris]

Je ne suis pas étonné d'apprendre que les requêtes sont très longues à s'exécuter....

Plusieurs raisons à ça :

-> des likes partout (sont-ils vraiment nécessaire ?)
-> un IN dans le WHERE (si vous pouvez passer par une jointure, ça serait mieux)
-> des FULL OUTER JOIN dans les sous-requêtes... (sont-ils vraiment nécessaire ? si oui c'est que votre modèle présente des étrangetés !)

[droliprane]

Bonjour

Les LIKE oui c'est une demande particulière de ma boite, je conçois que ça alourdit le temps de traitement

Le IN si je fais une jointure (entre tablle fc et fcl) je ne pourrai plus compter les factures seules car j'aurai démultiplié les résultats avec les lignes de chaque facture

Les OUTER JOIN sont là car je permets d'avoir sur une facture une ligne qui ne soit pas rattachée à un BL (et donc une commande/ligne/article), donc je veux chercher dans toutes les lignes de factures, même celles qui sont orpheline d'un BL...

[tatayo]

Bonjour,
Vu les requêtes, j'imagine que c'est du SQL Server.
Je note aussi que les deux requêtes se ressembles furieusement.
Est-ce que tu ne peux pas passer par une vue matérialisée ?

Tatayo.

[michel.souris]

D'après ce que vous dites vous devriez faire des LEFT JOIN mais pas de FULL OUTER JOIN...

[ErwanA]

Je ne commenterais pas les requêtes car je ne l'ai pas recherché dans le code.
Par contre, vous pouvez améliorer un peu les performances sur vos HLitPremier en rajoutant le paramètre HSansRafraichir.

Ce paramètre évite que la requête ne soit ré-exécuter une 2e fois.

ça s'utilise simplement comme cela

Code :

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

1
2
3
[...]
SI HLitPremier(Rs, HSansRafraichir) ALORS
[...]

[tbc92]

Bonsoir,

Si je comprends bien ,

Dans une première version, tu avais une requête, qui récupérait les données.
C'était trop long.
Pour solutionner cela, tu as décidé d'exécuter la requête 2 fois, ( une fois pour avoir le nombre de lignes, et une fois pour récupérer une partie des données).
Forcément, c'est mal parti. Si tu exécutes quasiment la même requête 2 fois, ça ralentit le traitement.

Pire, tu te retrouves à exécuter des requêtes imbriquées, avec des filtres sur les numéros de lignes à afficher.
Et pire encore, tu ré-exécutes la requête à chaque changement de page, au lieu de l'exécuter une fois pour toutes.

De manière générale, quand un traitement est trop lent, c'est très rarement en ajoutant des lignes de codes qu'on améliore la performance.

Reprend ta fenêtre d'origine, avant tous les ajouts que tu as fait pour optimiser.

Regarde à nouveau l'analyseur de performance, mais en regardant mieux.... tu dois pouvoir voir le détail entre l'instruction HexecuteREquete, et les instructions HlitSuivant()

Pour afficher uniquement les lignes utiles, tu peux :
- Passer par une 'source de données' .. et une table fichier. Ainsi, c'est Windev qui s'occupera de paginer au mieux, et il le fait très bien.
- Rester avec une table mémoire, mais modifier la partie Récupération des données :

Au lieu de faire :

Code :

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

1
2
3
4
5
Hlitpremier()
Tantque pas hendehors()
  Tableajoute()
  HlitSuivant()
fin

tu fais un truc :

Code :

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

1
2
3
4
5
6
Hlitpremier()
Tantque pas hendehors() _et_ nbre_lignes < nbre_lignes_max
  nbre_lignes++
  Tableajoute()
  HlitSuivant()
fin

Ca te permet d'afficher la 1ère page. Et pour le bouton 'Page-Suivante, relancer simplement une série de 100 HlitSuivant().

En complément de cela, il faut bien regarder du coté de la requête, ou de la base de données, il y a peut-être des optimisations aussi de ce côté là.


Pour cela , il y a un test que tu peux faire :
Tu as ta requête initiale, tu exécute simplement le hexécuteRequete, et 100 fois hLitSuivant pour récupérer les 100 premières lignes. Avec ce test, tu n'as pas d'outil pour afficher les pages suivantes... mais ce test va te permettre de connaître le temps minimum, incompressible pour afficher les 100 premières lignes.
Si ce test est trop lent, c'est qu'il faut chercher du côté base de données / Requete / Ré-Indexation.

[droliprane]

En fait c'est mon système de pagination qui ralentit l'accès du fait de la première requête qui compte le nombre total d'enregistrements ?

Mais ça vient aussi pas mal du contexte "factures" aussi, car je n'ai pas ces lenteurs pour l'interro BL, ou l'interro CC (commandes clients) et pourtant j'en ai bien plus que des factures.

Avancer de page en page en n'exécutant que 100 HlitSuivant, c'est valable que si on ne peut avancer que d'une page à la fois, moi je veux pouvoir atteindre directement la page 27 par exemple (donc les lignes 2701 à 2800)

Et puis quid si on veut revenir en arrière en page 14 par exemple

Je vais déjà m'affairer à simplifier ma requête en ajoutant des champs redondant dans la table fc (existant dans les tables liées)

Merci

[michel.souris]

Je suis le seul que ça choque de voir des FULL OUTER JOIN à outrance ?

Parce qu'autant on peut améliorer son algo, c'est certain, autant les requêtes sont déjà anti-optimales (et il le dit lui même car 7 secondes et 15 secondes depuis un worksheet c'est pas bon signe).

Bref pour moi vous pouvez améliorer votre algo (sans conteste...) mais vous devez vous penchez sur la structure de vos requêtes.... en ayant pris soin de vérifier que les indexes sont bien pensés !

[droliprane]

Mais j'ai changé mon code Michel !

Par contre, le comptage du nombre de lignes par cette requête :

Code :

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

1
2
3
4
 
SELECT count(DISTINCT fcl_fc_id) AS nblignes FROM 
	fc INNER JOIN fcl ON fc_id = fcl_fc_id 
	   INNER JOIN clt ON fc_client = clt_id

Plus simple en apparence, elle met pourtant 12 secondes au lieu de 7 pour me dire qu'il y a 4448 lignes, et ce depuis l'interpréteur de requête de SSMS.

[michel.souris]

Alors...

Code :

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

1
2
3
4
 
SELECT count(DISTINCT fcl_fc_id) AS nblignes FROM 
	fc INNER JOIN fcl ON fc_id = fcl_fc_id 
	   INNER JOIN clt ON fc_client = clt_id

A quoi sert la jointure sur Clt ?
A quoi sert la jointure sur Fcl ?
Si c'est pour finir sur distinct (anti-performant également) ?

Non parce que je pense que vous souhaitez compter les factures (a priori)...

donc

Code :

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

1
2
 
SELECT count(fc_id) AS nblignes FROM fc

devrait suffir...

En SQL si on ajoute des tables dans un FROM ça n'est pas sans raison....

[droliprane]

Oui alors la requête ressemble à ça parce qu'elle est construite dynamiquement en fonction des critères saisis dans l'ihm
Et c'est vrai qu'à l'initialisation il n'y a aucun critère de saisi et donc on se retrouve avec des jointures inutiles. Il faut que je corrige ça
Le DISTINCT plombe les performances, je viens de le lire ailleurs, mais donc j'ai pigé je vais éviter ça

N'empêche que si j'exécute ça :

Code SQL :

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

1
2
SELECT count(*) FROM 
	fc INNER JOIN (SELECT DISTINCT fcl_fc_id FROM fcl WHERE fcl_art_reference LIKE '%dor%') fcl_temp ON fc_id = fcl_temp.fcl_fc_id

ça prend 1 seconde !

Et si je viens à rajouter le critère de recherche dans le code client, la requête deviendrait :

Code SQL :

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

1
2
3
SELECT count(*) FROM 
	fc INNER JOIN (SELECT DISTINCT fcl_fc_id FROM fcl WHERE fcl_art_reference LIKE '%dor%') fcl_temp ON fc_id = fcl_temp.fcl_fc_id
	   INNER JOIN (SELECT clt_id FROM clt WHERE clt_code LIKE '%at%') clt_temp ON fc_client = clt_id

et prend 55 secondes !

J'en suis limite à penser que je vais ramener toutes les lignes dans une table, et faire des filtres sur la table via TableActiveFiltre etc....

4500 lignes c'est pas le bout du monde, et les filtres sur le widget table seront peut être plus performant que de requêter à chaque fois la BD

Par contre, je n'ai pas une vision "temps réel" de la BD tant que je ne recharge pas l'interface.

[michel.souris]

Autre chose qui me vient à l'esprit mais il ne me semble pas opportun de faire une requête de count puis une requête de sélection... il serait plus pertinent de faire une requête de sélection dont on récupère le nombre de ligne... (je sais pas si HNbEnr permet de le faire dans votre contexte).

Enfin je ne comprends votre façon de penser les requêtes... pourquoi créer des sous-ensemble que vous utiliser dans vos jointures ? pourquoi ne pas travailler sur la table et restreindre avec le WHERE (ou dans la jointure en cas de LEFT JOIN) ?

Ensuite STOP au distinct, utilisez des GROUP BY....

Votre idée de gérer tout en interface est (selon moi) une mauvaise piste.

[Nicolas_Jeanneau]

Bonjour,

pour ajouter à la réflexion : ne faire les filtres que quand l'ensemble est défini. On fait les jointures, on définit donc l'ensemble. Une fois le tout bien jointé, on compte le nombre de lignes ou on filtre le résultat. Au moins le temps de traitement est moins long ... De plus, si tu travailles avec un serveur de base de données, une même requête lancée coup sur coup sera moins longue dans le temps de traitement la 2e fois car il utilisera le cache !

bon courage,

Nicolas

[droliprane]

Autre chose qui me vient à l'esprit mais il ne me semble pas opportun de faire une requête de count puis une requête de sélection... il serait plus pertinent de faire une requête de sélection dont on récupère le nombre de ligne... (je sais pas si HNbEnr permet de le faire dans votre contexte).

Enfin je ne comprends votre façon de penser les requêtes... pourquoi créer des sous-ensemble que vous utiliser dans vos jointures ? pourquoi ne pas travailler sur la table et restreindre avec le WHERE (ou dans la jointure en cas de LEFT JOIN) ?

Le HNbEnr() n'est pas possible dans mon cas, il faut être en base HyperFile ou avoir l'accès natif SqlServer pour en bénéficier il me semble.

En ce qui concerne la dernière requête, j'avoues que je ne fonctionne pas comme ça d'habitude, mais j'ai fait plusieurs essais depuis le SSMS et c'est la requête qui mettait le moins de temps à aboutir. Mais il est clair qu'habituellement je fais mes jointures en premier, puis les filtres dans le where.

Je continues mes recherches mais on part dans tous les sens alors je ne sais plus trop quelle direction prendre

[michel.souris]

Et bien d'après ce que vous dites la requête est longue à s’exécuter, si cela ne vient pas de la requête (puisque vous avez testé et déterminé la syntaxe qui vous parait la plus rapide à l’exécution) le problème vient de vos indexes.

Il me semble que le mieux pour affirmer que HNbEnr n'est pas disponible est de chercher l'info.......

[Hemgé]

Le HNbEnr() n'est pas possible dans mon cas, il faut être en base HyperFile ou avoir l'accès natif SqlServer pour en bénéficier il me semble.

Bonjour,

Si vous utilisez un parcours de type SQLPremier/SQLCol, SQLInfoGene() vous permet de connaître SQL.NbLig, soit le nombre de lignes du résultat.
Ce qui se comprend puisque tous les enregistrements sont chargés en mémoire dans cette approche.

Hemgé

[droliprane]

Finalement j'opte pour une simplification de l'interface qui profitera à l'utilisateur au niveau du temps d'accès

Je décide de ne ramener que les 100 premiers résultats issus du filtrage opéré par l'utiisateur

Ainsi pour trouver sa facture, il deva agir sur les critères client, numéro de commande, éventuellement article

Le problème que je rencontre maintenant c'est qu'à chaque frappe dans un champs filtre donné (par exemple celui du nom client) la liste clients se filtre également et donc l'événement de sélection d'un élément de la liste se déclenche, et recharge ma table de factures

Le rechargement prend de moins en moins de temps au fur et à mesure que la recherche devient précise, mais lors du premier caractère entré, j'attends quand même 4-5 secondes, puis 3 secondes, puis 2, et enfin ça se raffraichit très rapidement

Je songe à mettre dans un thread la procédure qui alimente la table de résultats (factures) pour que la saisie de l'utilisateur ne soit pas hachée et perturbée par le rechargement de ma table.
Pensez-vous que ce soit une bonne ou mauvaise idée ?

Merci

[zouzoukha]

Salut,

Pour ce cas précis j'ai pour habitude de créer un thread pour lancer la requête.
Je laisse 1 seconde entre chaque "frappe" modification du champ.( donc tant que j'écris la requête n'est pas lancé)