Optimisation requête SQL - champ binaire cbDO_PIECE

Bonjour,

J'ai plusieurs en bases en SQL et je voudrais optimiser les performances des mes requêtes dessus.*
Les magiciens de chez SAGE génèrent des champs cb[Nomduchamp] quand c'est du texte et le stockent dans une variable de type binary.

Tous les index sont placés sur les champs binary cbDO_PIECe, cb_DO_TIERS etc... ce qui fait que lorsqu'on fait une requête toute bête pour obtenir les lignes d'un document:

Code: Tout sélectionner

select * from F_DOCLIGNE
where DO_type=7 and DO_Piece='FA088829'


Le moteur sql n'utilise pas d'index particulier et cela n'est pas terrible au niveau des performances.

Savez-vous recalculer le cb quelquechose d'un champ texte pour pouvoir optimiser les accès à la base SQL? (je suis en VBNET)

Autrement comment faites vous?

Cordialement

Bonjour, (au fait, lire les règles du forum en haut de chaque page (règles de politesse, version logicielle, etc.))

Avant de parler INDEX, déjà ne pas faire du SELECT * et préciser les colonnes que l'on veut voir remonter.
Ensuite, sauf si tu as un serveur poussif ou une térachiée de données, c'est le genre de requête qui répond assez bien (sur 900 000 lignes de docs, j'ai un résultat en moins d'1/4 de seconde, donc, faut pas être trop difficile).
Et puis, en SQL (mais là, vaut mieux s'adresser aux spécialistes sur des sites full SQL), les statistiques générées par le serveur optimisent la remontée des résultats (plus on fait de fois une même requête, ou approchante, mieux les statistiques fournissent le meilleur chemin de résolution au moteur SQL).
Lance 2 fois la requête et, en général, la seconde fois, les résultats sortent plus vite...

Sinon, pour le codage des champs cbXXX, pas très sorcier, Codage ASCII de la chaine, il me semble.

Bon, et puis, maintenant que je suis lancé ...

On peut imaginer optimiser en passant d'abord par une sélection des entêtes (il y en a moins que de ligne en général) puis faire la jointure sur le do_Type et le cbdo_Piece ....
Mais les résultats ne sont pas toujours ceux escomptés !

Une attaque directe des F_DOCLIGNE, comme tu le fais, mais avec sélection des 'bons' champs semble le plus efficace (voir dernière requête).

Exemple de code pour vérifier les temps de réponse :

Code: Tout sélectionner

Declare @tStart datetime2
Declare @tEnd datetime2
Declare @sMsg varchar(500)
-- * Jointure
Set @sMsg= '* avec jointure '
Set @tStart=SYSDATETIME()
   SELECT *
   FROM F_DOCENTETE
INNER JOIN F_DOCLIGNE ON F_DOCENTETE.cbDO_Piece = F_DOCLIGNE.cbDO_Piece AND F_DOCENTETE.DO_Type=F_DOCLIGNE.DO_Type
Set @tEnd=SYSDATETIME()
Print @sMsg + CAST(DATEDIFF(millisecond,@tStart,@tEnd) AS VARCHAR(50)) + ' millisecondes'

-- Champs nommés + Jointure
Set @sMsg= 'Champs nommés + Jointure '
Set @tStart=SYSDATETIME()
   SELECT F_DOCENTETE.DO_Type, F_DOCENTETE.DO_Piece, F_DOCLIGNE.AR_Ref, F_DOCLIGNE.DL_Qte
   FROM F_DOCENTETE
INNER JOIN F_DOCLIGNE ON F_DOCENTETE.cbDO_Piece = F_DOCLIGNE.cbDO_Piece AND F_DOCENTETE.DO_Type=F_DOCLIGNE.DO_Type
Set @tEnd=SYSDATETIME()
Print @sMsg + CAST(DATEDIFF(millisecond,@tStart,@tEnd) AS VARCHAR(50)) + ' millisecondes'

-- * sur F_DOCLIGNE
Set @sMsg= '* sur F_DOCLIGNE '
Set @tStart=SYSDATETIME()
   SELECT * FROM F_DOCLIGNE
Set @tEnd=SYSDATETIME()
Print @sMsg + CAST(DATEDIFF(millisecond,@tStart,@tEnd) AS VARCHAR(50)) + ' millisecondes'

-- Champs nommés + Jointure + clause WHERE sur DO_Type
Set @sMsg= 'Champs nommés + Jointure sur DO_Type=7 '
Set @tStart=SYSDATETIME()
   SELECT F_DOCENTETE.DO_Type, F_DOCENTETE.DO_Piece, F_DOCLIGNE.AR_Ref, F_DOCLIGNE.DL_Qte
   FROM F_DOCENTETE
   INNER JOIN F_DOCLIGNE ON F_DOCENTETE.cbDO_Piece = F_DOCLIGNE.cbDO_Piece
   WHERE F_DOCENTETE.DO_Type=7
Set @tEnd=SYSDATETIME()
Print @sMsg + CAST(DATEDIFF(millisecond,@tStart,@tEnd) AS VARCHAR(50)) + ' millisecondes'

-- * sur F_DOCLIGNE sur DO_Type=7
Set @sMsg= '* sur F_DOCLIGNE sur DO_Type=7 '
Set @tStart=SYSDATETIME()
   SELECT * FROM F_DOCLIGNE
   WHERE DO_Type=7
Set @tEnd=SYSDATETIME()
Print @sMsg + CAST(DATEDIFF(millisecond,@tStart,@tEnd) AS VARCHAR(50)) + ' millisecondes'

-- Champs nommés + Jointure + Where Do_Type et DO_Piece
Set @sMsg= 'Champs nommés + Jointure + Where Do_Type et DO_Piece '
Set @tStart=SYSDATETIME()
   SELECT F_DOCENTETE.DO_Type, F_DOCENTETE.DO_Piece, F_DOCLIGNE.AR_Ref, F_DOCLIGNE.DL_Qte
   FROM F_DOCENTETE
   INNER JOIN F_DOCLIGNE ON F_DOCENTETE.cbDO_Piece = F_DOCLIGNE.cbDO_Piece
   WHERE F_DOCENTETE.DO_Type=7 AND F_DOCENTETE.DO_Piece='FA009433'
Set @tEnd=SYSDATETIME()
Print @sMsg + CAST(DATEDIFF(millisecond,@tStart,@tEnd) AS VARCHAR(50)) + ' millisecondes'

-- Champs nommés sur F_DOCLIGNE +  Where Do_Type et DO_Piece
Set @sMsg= '* sur F_DOCLIGNE +  Where Do_Type et DO_Piece '
Set @tStart=SYSDATETIME()
   SELECT * FROM F_DOCLIGNE
   WHERE DO_Type=7 AND DO_Piece='FA009433'
Set @tEnd=SYSDATETIME()
Print @sMsg + CAST(DATEDIFF(millisecond,@tStart,@tEnd) AS VARCHAR(50)) + ' millisecondes'

-- Champs nommés sur F_DOCLIGNE +  Where Do_Type et DO_Piece
Set @sMsg= 'Champs sur F_DOCLIGNE +  Where Do_Type et DO_Piece '
Set @tStart=SYSDATETIME()
   SELECT DO_Type,DO_Piece,AR_Ref,DL_Qte FROM F_DOCLIGNE
   WHERE DO_Type=7 AND DO_Piece='FA009433'
Set @tEnd=SYSDATETIME()
Print @sMsg + CAST(DATEDIFF(millisecond,@tStart,@tEnd) AS VARCHAR(50)) + ' millisecondes'


Résultats :

Code: Tout sélectionner


(893130 ligne(s) affectée(s))
* avec jointure 128321 millisecondes

(893130 ligne(s) affectée(s))
Champs nommés + Jointure 8557 millisecondes

(893130 ligne(s) affectée(s))
* sur F_DOCLIGNE 65823 millisecondes

(420147 ligne(s) affectée(s))
Champs nommés + Jointure sur DO_Type=7 3975 millisecondes

(420147 ligne(s) affectée(s))
* sur F_DOCLIGNE sur DO_Type=7 31618 millisecondes

(10 ligne(s) affectée(s))
Champs nommés + Jointure + Where Do_Type et DO_Piece 407 millisecondes

(10 ligne(s) affectée(s))
* sur F_DOCLIGNE +  Where Do_Type et DO_Piece 279 millisecondes

(10 ligne(s) affectée(s))
Champs sur F_DOCLIGNE +  Where Do_Type et DO_Piece 143 millisecondes


Configuration de test :

Code: Tout sélectionner

Machine :
* HP ProBook 4330s
* Core i3-2330M / 2.20GHz
* 4 Go RAM
* Indice de performance = 4.7 (voir 5.9 hors Aero)
Système :
* Windows Seven x64 SP1 Pro (toutes mises à jour système effectuées)
Sage version :
* Sage Etp 16.05
Base de données :
* Microsoft Std 2008 SP1 (celui fourni par l'éditeur SAGE) limité à 2Go de RAM
* Mode de récupération : Simple
* Niveau de compatibilité : SQL Server 2000 (80)
(Bref, configuration de base selon préco. éditeur)
* Nombre de documents = 91271 / nombre de lignes de documents = 893130
(c'est pas bijou ...)


Bref, c'est bien souvent l'environnement qui pénalise les temps de réponse (SQL mal paramétré pour SAGE, problèmes de lenteurs réseau (résolution DNS ou un vieux switch qui traîne par là) (on en voir de toute les couleurs))

Essaye d'effectuer ces tests directement sur le serveur SQL, puis (en ayant installé le SQL Client pour lancer le SQL Management Studio sur un poste client) à partir d'un poste réseau.

Cela peut démontrer de défaillance coté paramétrage réseau.

Ces petits exemples m'ont occupé environ 2H, tu me dois donc 2x1452.99€ (et encore, là, je ne compte pas en millisecondes )

Pour ma gouverne, es-tu un utilisateur SAGE (client ou service informatique client), un revendeur SAGE, un CCS SAGE ou autre ?

Cordialement,

Bonjour,

Si on parle de perfs au niveau d'un SELECT, les utilisateurs doivent parfois trouver le temps long eux aussi, non ?

Petites vérifications (en vrac) :

- Le plan de maintenance doit reconstruire les index au moins une fois par semaine (REBUILD)
- S'il y a beaucoup de mouvement au quotidien, une défragmentation des index (DEFRAG) suivie d'une MAJ des statistiques est un plus.

- Le plan de maintenance NE DOIT PAS réduire la taille des bases de données. Une base de données doit être dimensionnée pour x années d'exploitation dans l'idéal.
- Il est tout à fait possible de créer des index supplémentaires dans une BDD sage MAIS ils doivent être supprimés pour faire les MAJ de version puis recréés.

Enfin, si a fortiori il y plusieurs bases dans la requête, il est possible de lier les vues au schéma (CREATE VIEW... WITH SCHEMABINDING) => Si on appelle un champ indexé, on bénéficie directement de l'index. Je manque un peu de pratique sur ce dernier point mais un forum dédié SQL devrait apporter des réponses plus précises.

HTH,

Merci pour vos réponses...

J'ai trouvé !
Je suis d'accord avec la plupart de vos affirmations après pour la forme, j'ai appris à connaitre asr31 au fil de mes lectures (et peu de mes participations)
Mais s'il y a un index dans SAGE sur les lignes autant l'utiliser si on ne veut que les lignes :

Il faut mettre en critères: convert(varbinary(255),' ' + 'FA060682') et dans ce cadre SQL SERVER utilise l'index ID_LIGNE.
Sur une table à 3 millions de lignes avec une quinzaine d'utilisateurs et quand les données ne sont pas en cache... ça impacte un poil l'affichage.

Code: Tout sélectionner

declare @tStart datetime2
Declare @tEnd datetime2
Declare @sMsg varchar(500)


-- Champs nommés sur F_DOCLIGNE +  Where Do_Type et DO_Piece + cbChamp
Set @sMsg= 'Champs sur F_DOCLIGNE +  Where Do_Type et cbDO_Piece '
Set @tStart=SYSDATETIME()
   SELECT DO_Type,DO_Piece,AR_Ref,DL_Qte FROM F_DOCLIGNE
   WHERE DO_Type=7 AND cbDO_Piece =convert(varbinary(255),' ' + 'FA060682')
Set @tEnd=SYSDATETIME()
Print @sMsg + CAST(DATEDIFF(millisecond,@tStart,@tEnd) AS VARCHAR(50)) + ' millisecondes'

-- Champs nommés sur F_DOCLIGNE +  Where Do_Type et DO_Piece
Set @sMsg= 'Champs sur F_DOCLIGNE +  Where Do_Type et DO_Piece '
Set @tStart=SYSDATETIME()
   SELECT DO_Type,DO_Piece,AR_Ref,DL_Qte FROM F_DOCLIGNE
   WHERE DO_Type=7 AND DO_Piece='FA060682'
Set @tEnd=SYSDATETIME()
Print @sMsg + CAST(DATEDIFF(millisecond,@tStart,@tEnd) AS VARCHAR(50)) + ' millisecondes'

ce qui donne en résultat

Code: Tout sélectionner


(25 ligne(s) affectée(s))
Champs sur F_DOCLIGNE +  Where Do_Type et cbDO_Piece 0 millisecondes

(25 ligne(s) affectée(s))
Champs sur F_DOCLIGNE +  Where Do_Type et DO_Piece 154 millisecondes


Bonjour,

Bien vu.

Cordialement,