PC SOFT

FORUMS PROFESSIONNELS
WINDEVWEBDEV et WINDEV Mobile

Accueil → WINDEV 2024 → [SQL-Serveur] [Accès natif] Query Timeout Expired
[SQL-Serveur] [Accès natif] Query Timeout Expired
Débuté par Corinne BONHOMME, 12 juil. 2019 15:27 - 11 réponses
Membre enregistré
188 messages
Popularité : +7 (7 votes)
Posté le 12 juillet 2019 - 15:27
Bonjour,

Je ne sais plus vraiment où chercher, alors je viens voir si quelqu'un aurait une idée, une piste.
Je développe en WinDev depuis WD4.1, donc je connais quand même assez bien le langage et surtout les commandes HLitxxx

Il y a 14 ans, j'ai écrit une application qui se connecte à l'AS/400 en accès natif, donc avec des commandes HLitxxxx partout.
À l'époque, c'était pas mal plus ce que je connaissais, maintenant, je commence à préférer les commandes SQL direct.
L'application est très grosse : 420 fenêtres, 160 000 lignes de code et des tables à 8 millions d'enregistrements. Le transfert des données de l'AS/400 vers SQL-Serveur prend 5 heures.

Cette application transfert vers SQL-Serveur (2012) et évidement, on manque de temps, donc au lieu de tout reprendre pour le ré-écrire avec une meilleure structure (ce qui ne lui ferait pas de mal, on évolue en 14 ans ;) ) et en utilisant les évolutions du langage, on doit tout transférer avant la fin de l'année (tests utilisateurs et finaux compris)

J'ai évidement trouvé des petits problèmes, des différences entre les 2 plates-formes que j'ai adapté et corrigé sans problème.

Mais là, je tombe sur un problème et je crois devenir complètement folle. Je ne comprends pas.

J'ai des commandes HLitRecherchePremier() sur une clef simple (même pas composée) : unique ou doublon qui me plante en Query Timeout Expired.
Ce que je remarque, c'est qu'à chaque fois que cela arrive, c'est sur mes gros fichiers et toujours sur des index autres que l'index cluster. Toujours des index non-cluster.

Si je remplace la commande HLitRecherchePremier() par une requête SQL, tout marche bien et c'est super performant.
Mais malheureusement, je ne peux pas remplacer toutes les lectures par des ordres SQL dans cette phase du projet, c'est trop gros. C'est un projet 2.0 avec plein d'amélioration (Classes, Databinding, fenêtre interne…), mais en attendant, il faut que je comprenne pourquoi la commande qui est correcte, qui fonctionne sur l'AS/400 depuis 14 ans me plante avec des Query Timeout Expired dans certaines conditions. Le problème, ne vient pas de la commande, car j'utilise la même commande dans d'autres programmes avec SQL-Serveur.

Si quelqu'un à des idées de pistes à regarder. Des experts SQL-Serveur qui pourraient m'aider à vérifier peut-être des paramètres qui pourraient être important dans le cas de grosse table.

Les scripts des tables et index ont été créé avec l'outils de l'analyse de PCSOFT.
On a juste ajouté un FillFacteur à 90, mais avant il était à 0 (soit 100) et j'avais aussi le problème.

Je vais continuer à chercher, car je ne peux pas continuer à transformer le code en SQL, quand j'ai une erreur, pendant mes tests.
Il faut que je trouve une méthode un peu moins aléatoire et qui va m'assurer une stabilité avant la ré-écriture complète qui ne se fera pas avant 2-3 ans (si j'ai de la chance)

Les tables ont été généré sur SQL-Serveur et ré-importée dans le projet pour remplacer les définitions des fichiers AS/400 et être sûr que tout est bien en phase avec les tables SQL-Serveur.

Et en passant, on a augmenté le TimeOut, car on avait des plantages trop fréquent au début, donc on a augmenté le timeout de la connexion. On a configuré le timeout à 2min au lieu de 30s (par défaut)

// ---------------------------------------------------------------
// [Corinne] - 23 mai 2019
// À cause de problème de performance des serveur SQL-Serveur VM, les applications comme CLIC plante en "Query time expired" quand le paramètre est laissé à la valeur par défaut (30s)
// Nous sommes obligés de forcer une valeur à 2 min, car un test sur le serveur d'une commande sur un gros fichier de CLIC (Relevé de compteur) a pris 1min 10s, donc je fixe à 2min (120s).
// "WD Command Timeout" : Fixe la durée maximale (en secondes) de l'exécution d'une commande (Timeout de commande).
// Les valeurs possibles sont les suivantes :
// • -1 : Valeur par défaut de la couche client (généralement 30 sec)
// • 0 : Pas de timeout. Dans ce cas, l'attente est infinie (attention : il existe un risque de blocage de l'application).
// • valeur supérieure à 0 : Valeur du timeout en secondes
// ---------------------------------------------------------------

:m_sInfosEtendues = "WD Command Timeout=120" // [Corinne] 23 mai 2019 : Forcer la valeur à 2min (120s), car sur les gros fichiers on a des "Query time expired" continuellement.


Merci d'avance


Voici un exemple d'un message d'erreur :
Erreur à la ligne 19 du traitement Méthode PrmLoadClasse.
Vous avez appelé la fonction HLitRecherchePremier.
Erreur de l'accès natif SQLSERVER.
Numéro d'erreur = 117 
 
SQL Server a renvoyé l'erreur 80040e31
Description: Query timeout expired
SQL State: HYT00
SQL Error Number: 0
 
L'exécution de la requête suivante a échoué :
SELECT [IDAuto],[SKKEYX],[SKY6S1],[SKRMC2],[SKPFNA],[SKY7S1],[SKRNC2],[SKPGNA],[SKLXN2],[SKLYN2],[SKY8S1],[SKFCTS],[SKIUV1],[SKQET1] FROM [BonTravail].[EVBTNOP] WITH (NOLOCK) WHERE [SKKEYX]>? 
ORDER BY 2

----- Informations techniques -----

Projet : CLIC

Appel WL :
Traitement de 'Méthode PrmLoadClasse' (ClaEVBTNOP_NormeObjectif.PrmLoadClasse), ligne 19
Fonction 'HLitRecherchePremier', syntaxe 1

Que s'est-il passé ?
Erreur de l'accès natif SQLSERVER.
Numéro d'erreur = 117 
 
SQL Server a renvoyé l'erreur 80040e31
Description: Query timeout expired
SQL State: HYT00
SQL Error Number: 0
 
L'exécution de la requête suivante a échoué :
SELECT [IDAuto],[SKKEYX],[SKY6S1],[SKRMC2],[SKPFNA],[SKY7S1],[SKRNC2],[SKPGNA],[SKLXN2],[SKLYN2],[SKY8S1],[SKFCTS],[SKIUV1],[SKQET1] FROM [BonTravail].[EVBTNOP] WITH (NOLOCK) WHERE [SKKEYX]>? 
ORDER BY 2

Code erreur : 73001
Niveau : erreur fatale
Code erreur WD55 : 3001

Dump de l'erreur du module 'wd240hf.dll' (24.0.164.1).
Identifiant des informations détaillées (.err) : 72801
Informations de débogage :
IEWDSQLSERVER=204.10
Module=
Version=<24.0.12.0>
Fonction (7,12)
Informations supplémentaires :
EIT_BASECODE : <-2147217871>
EIT_NATIVECODE : <117>
EIT_LOGICALTABLENAME : 
EIT_PILEWL :
Méthode PrmLoadClasse (ClaEVBTNOP_NormeObjectif.PrmLoadClasse), ligne 19
Méthode PrmFichierVersClasse (ClaEVBONTP_BonTravail.PrmFichierVersClasse), ligne 139
Méthode PrmLoadClasse_IDAuto (ClaFichierCLIC.PrmLoadClasse_IDAuto), ligne 21
Procédure locale PrlFenetreLoad (FrmEVBONTP_Fiche.PROCEDURE.PrlFenetreLoad), ligne 64
Fin d'initialisation de FrmEVBONTP_Fiche (FrmEVBONTP_Fiche), ligne 54
Clic sur BTN_OuvrirBT (FrmGestionGarantie_PCC.BTN_OuvrirBT), ligne 12
Clic sur BTN_Actions (FrmGestionGarantie_PCC.BTN_Actions), ligne 12
EIT_DATEHEURE : 12/07/2019 09:13:38
EIT_TYPE_WDFILE : <4>
EIT_IDCODE : <458752>

Assistance


Et le code qui a planté sur la commande HLitRecherchePremier()
FONCTION PrmLoadClasse(ppe_sNumBT)
// ppe_sNumBT (E) : Numéro de BT

// Cette méthode charge tous les membres de la classe à partir du numéro de BT
SI SansEspace(ppe_sNumBT)="" ALORS
:Ecrit_m_sMessageErreur("Aucun numéro de BT défini")
RENVOYER Faux
FIN

// Recherche du BT dans la BT
SI HLitRecherchePremier(EVBTNOP,SKKEYX,ppe_sNumBT,hIdentique) ALORS
// Enreg trouvé
:PrmFichierVersClasse()

RENVOYER Vrai

SINON
:PrmHRAZ()
:Ecrit_m_sMessageErreur("BT non trouvé")
RENVOYER Faux

FIN





--
Corinne Bonhomme
Montréal, Canada
Membre enregistré
188 messages
Popularité : +7 (7 votes)
Posté le 17 juin 2020 - 14:57
Bonjour,

Je relance ce problème.
J'ai toujours le problème et pour le moment, la seule solution que j'ai trouvé, c'est de ré-écrire en SQL les processus qui plantent.
Mais un projet de 400 fenêtres et 100 000 lignes de code, je ne peux pas le faire partout.

Une des tables qui pose problème a 10 094 545 enregistrements.
Est-ce si gros pour SQL-Serveur ? Ou est-ce trop gros pour l'accès natif de Windev ?
Car en commande SQL, tout est OK.
Mais une commande HlitRecherchePremier(), sur une clef plante en Query Timeout expired avec un seul utilisateur sur la BD !!!!
Alors quand je vais avoir plusieurs utilisateurs en même temps, je n'ose pas imaginer ce qui va se passer.

Merci d'avance, s'il y a des experts SQL-Serveur sur ce forum.

--
Corinne Bonhomme
Montréal, Canada
Membre enregistré
2 566 messages
Popularité : +222 (260 votes)
Posté le 17 juin 2020 - 16:07
As-tu essayé de faire un HFiltre sur ta table avant le HLitPremier ?

--
Cordialement,

Philippe SAINT-BERTIN
Membre enregistré
84 messages
Popularité : +4 (4 votes)
Posté le 17 juin 2020 - 16:46
Bonjour Corinne,

Pas mal de petites chose me viennent à l'esprit. La toute première est que tu fais un HLitRecherchePremier.
Tu ne fais pas de parcours après ce qui est logique puisque tu cherches seulement l'existence d'un enregistrement.
As-tu essayé d'ajouter HlimiteParcours ? La requête qui plante ne sera plus exécutée car elle s'opère uniquement pour amorcer le parcours lorsque l'enregistrement n'existe pas.

Concernant tes tables avec des millions d'enregistrement, je te rassure cela ne doit avoir aucune incidence selon moi.

Nous utilisons également SQL Server depuis de nombreuses années sans soucis. Tu dois par contre vérifier ta connexion avec la position du curseur (Client ou serveur).

Lis très très attentivement la documentation sur les accès natif.
Nous ouvrons systématiquement deux connexions. La première avec HOuvre et la seconde avec SQLConnect.
Nous utilisons au maximum SQLExec (sur la connexion SQLConnect) et gardons les commandes HLitRecherchePremier, Hmodifie, etc... pour les écrans de type fiche, Les tables sont toutes avec du SQLExec suivi de SQLTable ou parcours de résultat.

Si tu veux en parler de vive voix... +32475453853 en oubliant pas mon décalage horaire ;-)

Bonne prog.

--
Benoit Neve
Membre enregistré
188 messages
Popularité : +12 (12 votes)
Posté le 17 juin 2020 - 20:41
Bonjour

Moi je ne comprends pas la requête affichée dans le message d'erreur :
SELECT [IDAuto],[SKKEYX],[SKY6S1],[SKRMC2],[SKPFNA],[SKY7S1],[SKRNC2],[SKPGNA],[SKLXN2],[SKLYN2],[SKY8S1],[SKFCTS],[SKIUV1],[SKQET1] FROM [BonTravail].[EVBTNOP] WITH (NOLOCK) WHERE [SKKEYX]>?
ORDER BY 2

C'est une recherche à l'identique non ? On devrait avoir WHERE [SKKEYX] = 'xxx' non ? D'où sort ce ">?"

Là cela fait peur et cela me conforte dans mon choix ne NE JAMAIS UTILISER les ordres H sur une base SQL, en dehors de HFSQL
Elle ne les comprend pas donc d'une part il y aura un temps de conversion non négligeable depuis les métadonnées de l'analyse, et d'autre part on ne maîtrise pas la requête générée, à part utiliser HExécuteRequêteSQL (avec hRequêteSansCorrection) ...

10 millions de lignes c'est rien pour SQL Serveur (vous n'êtes pas sur du SQL Serveur Express quand même ?) le problème n'est pas là, le problème est lié ici à la clause WHERE générée qui n'est visiblement pas correcte.

Autre idée, tester sans l'accès natif mais via OLE DB / ODBC pour voir si c'est mieux ? Mais je n'y crois guère si ce sont les informations de l'analyse qui construisent la requête on aura la même requête en sortie.

Cela vaut peut-être le coup de remonter l'exemple au support technique pour qu'ils regardent.

--
Côme, Clairinfo
Membre enregistré
84 messages
Popularité : +4 (4 votes)
Posté le 18 juin 2020 - 09:02
Côme a écrit :
Bonjour

Moi je ne comprends pas la requête affichée dans le message d'erreur :
SELECT [IDAuto],[SKKEYX],[SKY6S1],[SKRMC2],[SKPFNA],[SKY7S1],[SKRNC2],[SKPGNA],[SKLXN2],[SKLYN2],[SKY8S1],[SKFCTS],[SKIUV1],[SKQET1] FROM [BonTravail].[EVBTNOP] WITH (NOLOCK) WHERE [SKKEYX]>?
ORDER BY 2

C'est une recherche à l'identique non ? On devrait avoir WHERE [SKKEYX] = 'xxx' non ? D'où sort ce ">?"

Bonjour Côme,

Cette requête est due au HLitRecherchePremier. Ce n'est pas la première requête mais la seconde.
L'accès avec les ordres Hxxxx essaye de recréer le même comportement que sous HF. Il ne trouve pas l'enregistrement demandé (requête 1) alors il amorce un parcours (Cette requête). C'est pourquoi il faut utiliser HlimiteParcours alors Il n'exécute pas la seconde.

--
Benoit Neve
Membre enregistré
188 messages
Popularité : +12 (12 votes)
Posté le 18 juin 2020 - 12:22
Bonjour Benoit

J'espère que cette solution va solutionner le problème de Corinne.
En tous cas merci pour la piste je ne connaissais pas cette option.
Reste la logique de tout cela, vraiment étrange pour moi !
"Il ne trouve pas l'enregistrement demandé (requête 1) alors il amorce un parcours..."
Boudiou...

--
Côme, Clairinfo
Membre enregistré
386 messages
Popularité : +13 (13 votes)
Posté le 18 juin 2020 - 12:51
Bonjour,

Je confirme ce que dit B. Neve, l'utilisation de hLimiteParcours est clairement nécessaire pour éviter ce genre de requêtes superflues exécutées lorsqu'aucun enregistrement correspondant à la recherche n'existe dans la base de données.

Je pense que ce comportement est surtout mis en place pour la rétro-compatibilité et l'homogénéisation des fonctions HLit*() et HRecherche*(), d'ailleurs on notera que, dans le code initial, l'utilisation de l'option hIdentique, bien qu'inutile (vu que HLitRecherchePremier fait une recherche à l'identique par défaut) et non documentée, ne génère pas d'erreur de compilation.

Pour s'éviter une réécriture massive de tout un projet pour utiliser cette variable, il est possible de passer temporairement par une surcharge de la procédure HLitRecherchePremier():

Procedure HLitRecherchePremier(LOCAL Table, LOCAL Colonne, LOCAL Valeur, LOCAL Options = 0) : booléen

RENVOYER WL.HLitRecherchePremier(Table, Colonne, Valeur, OUBinaire(hLimiteParcours, Options))


Il conviendra d'adapter le code en fonction du projet, en effet certains codes font réellement l'usage de ce "parcours si l'enregistrement est absent" et l'utilisation du hLimiteParcours dans ce cas entraînera des bugs à l'utilisation.
Posté le 18 juin 2020 - 15:03
Côme a écrit :
Bonjour Benoit

J'espère que cette solution va solutionner le problème de Corinne.
En tous cas merci pour la piste je ne connaissais pas cette option.
Reste la logique de tout cela, vraiment étrange pour moi !
"Il ne trouve pas l'enregistrement demandé (requête 1) alors il amorce un parcours..."
Boudiou...

--
Côme, Clairinfo


Ca c'est à cause du hlitRecherchePREMIER... qui dit premier, dit parcours en boucle des SUIVANTS...

Si on veut juste récupérer UN enreg, il vaut mieux utiliser l'instruction d'origine, à savoir hlitRecherche()
Membre enregistré
342 messages
Popularité : +30 (32 votes)
Posté le 19 juin 2020 - 16:32
Bonjour

Je confirme ce qui a été dit plus haut : il faut utiliser hLimiteParcours
La différence est énorme et ça apportera une solution acceptable avant le passage en SQL

--
Pascal H. http://phapps.e-monsite.com
Posté le 24 février 2021 - 13:45
Bonjour
juste un complément d'information :
nous avons été confronté à ce même problème en déplaçant une base de donnée Ms-SqlServer sur des disques plus lents, et suite à l'analyse du problème avec notre DBA , nous avons abouti à la même conclusion que la recherche qui était à l'identique sous Windev en mode test voyait son = changé en > avec la version compilée.

Jeff
Membre enregistré
188 messages
Popularité : +7 (7 votes)
Posté le 26 avril 2022 - 19:24
Et merci à tous.
Avec l'aide de PCSoft, la solution est bien le "hLimiteParcours" dans les instructions hLitXxx().

--
Corinne Bonhomme
Montréal, Canada