Vous consultez une page technique concernant le logiciel de gestion Mercator. Celle-ci contient des informations spécifiques destinées aux professionnels de Mercator. Souhaitez-vous être redirigés vers des informations plus générales ?


   Ne plus poser cette question

xLookup ou l'erreur de design récurrente dans votre code !

0000002702     -      24/04/2017

La fonction xLookup est une fonction bien pratique mais souvent utilisée abusivement dans les paramétrages qui passent un jour sous la loupe de l'équipe de support et de programmation de Mercator. Pour rappel, l'objet de cette méthode est de renvoyer un champ d'un record dans une table selon une recherche indexée.  

Cette fonction date de la version DBF de Mercator, c'est-à-dire d'une époque où Mercator n'était pas une application client-server. Mercator disposait de l'ensemble de ses données, "sous le coude", avec tous les risques que cela comportait (endommagement des données en cas de plantage, peu de contrôles d'intégrité, ...)

Dans la version DBF, un appel de ce type, xlookup('CLI_X','C_ID','ABC','C_NOM') produisait in fine le code FoxPro suivant :

select cli_x
set order to c_id
seek('ABC')
return cli_x.c_nom

Les données étant détenues localement par Mercator, le résultat de la recherche était pratiquement instantané.

A présent, Mercator est une application qui s'inscrit dans un contexte "client-server" et les avantages de cette configuration ne doivent plus être démontrés. Mercator est le client tandis que SQL Server est chargé d'héberger les données et de veiller à leur intégrité. La différence essentielle est que les données ne sont plus considérées comme locales mais distantes. Ainsi donc, chaque xLookup produira la séquence suivante (pour l'exemple donné ci-dessus) :

  • une connexion au serveur SQL
  • l'exécution de cette requête :
    exec sp_executesql N'select C_NOM as ret from CLI (NOLOCK) where (C_ID=@valeur_lookup)',N'@valeur_lookup char(3)',@valeur_lookup='ABC'
  • la récupération du résultat dans le client Mercator
  • la déconnexion du serveur SQL

En conclusion, on peut dire que la méthode xLookup n'est pas une fonction qui respecte le modèle client-server.

Quand on parle de la fonction xLookup, on parle aussi de toutes les fonctions qui en sont dérivées ou qui reposent sur le même principe :

  • xCondit
  • xCours
  • xDepot
  • xDesign
  • xDispo
  • xExercice
  • xFamille
  • xGamEnum
  • xGamTyp
  • xLiees
  • xNom
  • xPaiem
  • xPeriode
  • xRayon
  • xRep
  • xTiers
  • xTypePaiem
  • xUser
  • xVatRateIsStandard
  • ...

Concrètement...

"La validation d'une livraison fournisseur de 800 lignes est anormalement lente"

Diagnostic du support Mercator : un customizer BeforeSave contient une boucle qui parcourt toutes les lignes du document. Dans cette boucle, le programmeur a placé 2 xLookups. Cela va donc produire 1600 connexions et déconnexions au serveur SQL.

"Une grille d'historique est excessivement lente, au premier affichage et quand on scrolle"

Diagnostic du support Mercator : un customizer place des valeurs dans la grille et ces valeurs proviennent de xLookups. Chaque rafraîchissement de cellule produit une connexion au serveur SQL. Il en résulte des centaines de connexions pour un simple scrolling dans la grille.

Faut-il bannir l'usage de xLookup dans la programmation et le paramétrage de Mercator ?

La réponse de l'équipe de programmation est sans équivoque : OUI
Il faut juste tolérer son usage là où on est certain que cela ne produira aucun préjudice en termes de performances.

Comment détecter une situation où un usage abusif de xLookups est rencontré ?

Il faut utiliser le Profiler de SQL Server. Cet outil permet de visualiser en temps réel les requêtes reçues par le serveur SQL. Une situation abusive de xLooklups sera repérée par une longue suite de requêtes identiques et dont la structure est toujours la suivante :

select COLONNE as ret from TABLE (NOLOCK) where (INDEX=@valeur_lookup)

Comment corriger cette lacune ?

Cas 1 : dans un LinesEditor, des cellules sont complétées avec le résultat d'un xLookup

Pour corriger cela, il suffit d'utiliser les colonnes read-only du LinesEditor. Elles ont été créées exactement pour répondre à cette situation. Si la valeur souhaitée n'est pas immédiatement disponible, il faut utiliser une fonction scalaire SQL. (Voir l'exemple avec XDISPO_PERSO)

Cas 2 : dans une autre grille, des cellules sont complétées avec le résultat d'un xLookup

Pour corriger cela, il faut procéder en deux temps :

  1. Via un StringUpdate, modifier la requête SQL pour amener les données souhaitées dans la DataTable utilisée par la grille. (Ajouter les colonnes souhaitées dans la DataTable)
  2. Via le customizer, ajouter ces colonnes dans la grille.

Voir ces exemples :

Cas 3 : dans une boucle parcourant les DataRows d'une DataTable, une ou plusieurs informations sont lues via xLookup

Pour corriger cela, il faut procéder comme suit :

  1. Uploader via SQL bulk une table temporaire contenant l'information minimale décrivant tous les enregistrements pour lesquels on veut obtenir les informations nécessaires.
  2. Effectuer un seul Zselect, dont la requête contient une jointure sur cette table temporaire afin d'obtenir, via une seule connexion SQL, l'ensemble des informations qui seront nécessaires dans la boucle.
  3. Dans la boucle, utiliser DataTable.Select qui permet de faire une recherche dans une DataTable (locale) afin de trouver l'information souhaitée.

Ci-dessous un exemple très simple qui permet, dans un événement BeforeSave sur un BllingEngine, de compléter le champ LIGNES_V.XXX avec la valeur de STOCK.S_XXX correspondant et LIGNES_V.YYY avec STOCK.S_YYY.

Zoom
private void BillingEngine_BeforeSave(object sender, MercatorUi.Engine.Gescom.BillingEngine.BeforeSaveEventArgs e)
{
    MercatorUi.Engine.Gescom.BillingEngine billingEngine = (MercatorUi.Engine.Gescom.BillingEngine)sender;
    DataTable dtArticles = new DataTable();
    dtArticles.Columns.Add("s_id", typeof(string));
    foreach(DataRow dr in billingEngine.LIGNES.Select("id_article<>''"))
        dtArticles.Rows.Add(new object[1] { dr["id_article"] });
    using (MercatorSqlConnection conn = new MercatorSqlConnection(MercatorUi.Globals.RepData, true))
    {
        if (conn.Connection == null)
        {
            e.CancelSave = true;  // connexion refusée par le serveur SQL -> on arrête la sauvegarde
            return;
        }
        if (!Api.BulkDataTable(dtArticles, "#art_tmp", conn.Connection, null, "STOCK"))
        {
            e.CancelSave = true; // erreur lors du bulk de la DataTable -> on arrête la sauvegarde
            return;
        }

        using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("", conn.Connection))
        {
            cmd.CommandText = "select s_id,s_xxx,s_yyy from STOCK inner join #art_tmp on (stock.s_id=#art_tmp.s_id) \r\n"
                            + "drop table #art_tmp";
            DataSet ds = Api.Zselect(conn.Connection, cmd);
            if (ds == null)
            {
                e.CancelSave = true;  // la requête ci-dessus a renvoyé une erreur -> on arrête la sauvegarde
                return;
            }
            Api.TrimEndDataTable(ds.Tables[0]);

            // fin du traitement préliminaire avant le code en boucle


            // début du traitement dans la boucle des lignes

            foreach (DataRow dr in billingEngine.LIGNES.Select("id_article<>''"))
            {
                DataRow[] foundRows = ds.Tables[0].Select(string.Format("s_id='{0}'", Api.UnquoteSql(dr["id_article")))):
                if (foundRows.Length > 0) // sera toujours le cas, car tous les articles existent
                {
                    dr["xxx"] = foundRows[0]["s_xxx"];
                    dr["yyy"] = foundRows[0]["s_yyy"];
                }
            }
        }
    }
}

 

Cas 4 : disposer dans les lignes du BillingEngine d'informations de la table STOCK quand ces champs ne sont pas présents dans LIGNES_V

L'exemple ci-dessous montre comment remplacer une multitude de xLookups :

  • dans les méthodes du BillingEngine
  • dans les modèles d'impression associés à la séquence
  • dans les formules de colonne
  • ...

La mécanique repose sur plusieurs étapes :

  1. Lire dans STOCK les colonnes nécessaires pour les articles du document que l'on initialise (liste non vide si document existant)
  2. Ajouter dans la DataTable BillingEngine.LINES les colonnes souhaitées
  3. Compléter ces colonnes avec les valeurs correspondantes.
    Ici se termine le code qui s'exécute en initialisation d'un document.
  4. Lors de l'ajout de chaque article, compléter ces colonnes avec l'information de l'article en cours d'insertion(AfterInsertItem)

Ci-dessous un exemple de customizer qui implémente ce processus :

Zoom
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using MercatorApi;
using MercatorUi;
using MercatorExtensions;
using System.Xml;

namespace Billing
{
    public class Customizer : MercatorUi.ICustomizers.IBillingEngineCreated, MercatorUi.ICustomizers.IBillingEngineClosed
    {
        public void BillingEngineCreated(MercatorUi.Engine.Gescom.BillingEngine billingEngine)
        {
            billingEngine.AfterInsertItem += BillingEngine_AfterInsertItem;

            if (!billingEngine.LIGNES.Columns.Contains("stock_xxx"))
            {
                billingEngine.LIGNES.Columns.Add("stock_xxx", typeof(string));
                billingEngine.LIGNES.Columns.Add("stock_yyy", typeof(string));

                bool billingEngineWasModified = billingEngine.IsModified;

                //Lit les champs stock absents de lignes_v:
                DataSet ds = Api.Zselect(Globals.RepData, "select s_id, s_xxx, s_yyy from STOCK (NOLOCK) "
                                                        + "where exists(select * from LIGNES_V (NOLOCK) where (id=@id) and (journal=@journal) and (piece=@piece) and (id_article=s_id))",
                                new MercatorSqlParam("@id", billingEngine.Id, SqlDbType.Char),
                                new MercatorSqlParam("@journal", billingEngine.Journal, SqlDbType.Char),
                                new MercatorSqlParam("@piece", billingEngine.Piece));
                if (ds != null)
                {
                    Api.TrimEndDataTable(ds.Tables[0]);
                    foreach (DataRow billingLine in billingEngine.LIGNES.Rows)
                    {
                        DataRow[] stock = ds.Tables[0].Select(string.Format("s_id='{0}'", Api.UnquoteSql(billingLine["id_article"].ToString())));
                        InsertStockInBillingLine((stock.Length > 0 ? stock[0] : null), billingLine, billingEngine);
                    }
                }
                if (!billingEngineWasModified)
                    billingEngine.LIGNES.AcceptChanges();
            }
        }

        public void BillingEngineClosed(MercatorUi.Engine.Gescom.BillingEngine billingEngine)
        {
            billingEngine.AfterInsertItem -= BillingEngine_AfterInsertItem;
        }

        void BillingEngine_AfterInsertItem(object sender, MercatorUi.Engine.Gescom.BillingEngine.AfterInsertItemEventArgs e)
        {
            MercatorUi.Engine.Gescom.BillingEngine billingEngine = (MercatorUi.Engine.Gescom.BillingEngine)sender;
            InsertStockInBillingLine(e.DataRowStock, e.DataRowLignes, billingEngine);
        }

        void InsertStockInBillingLine(DataRow stock, DataRow billingLine, MercatorUi.Engine.Gescom.BillingEngine billingEngine)
        {
            if (stock == null)
            {
                billingLine["stock_xxx"] = "";
                billingLine["stock_yyy"] = "";
            }
            else
            {
                billingLine["stock_xxx"] = stock["s_xxx"];
                billingLine["stock_yyy"] = stock["s_yyy"];
            }
        }
    }
}