Discussion :

[Eric.H]

Bonjour

On commence, enfin, notre migration BDE > DBX… et les performances sont médiocres dans le cas d’INSERT avec paramètres. Donc je me demande si je n’ai pas oublié quelque chose (et j’ai fouillé sur le net…)

Les temps moyens sont 3.19 pour DBX, 0.89 en BDE et 0.90 en ADO, test effectués 20 fois, sur un serveur en réseau et en prod, avec insertion des mêmes données dans les trois cas. DBX serait donc, pour ce cas, 3.6 fois plus lent ?

Une simple requête,de test, avec deux paramètres :

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
  WITH Query DO // Query BDE, DBX ou ADO
    BEGIN
      Prepared:=False;
      SQL.Text:='SET NOCOUNT ON';
      SQL.Add(  'DELETE OF_VAL WHERE NUMERO_OF=:NUMERO_OF');
      SQL.Add(  '');
      SQL.Add(  'INSERT OF_VAL(NUMERO_OF,VALEUR_STOCK_PF)');
      SQL.Add(  'VALUES (:NUMERO_OF,:VALEUR_STOCK_PF)');
      Prepared:=True;
    END;
  Sds_Ligne.First;
  WHILE NOT Sds_Ligne.EOF DO
    BEGIN
      WITH Query DO
        BEGIN
          ParamByName('NUMERO_OF').AsString:=Trim(Sds_Ligne.FieldByName('NUMERO_OF').AsString);
          ParamByName('VALEUR_STOCK_PF').AsCurrency:=Sds_Ligne.FieldByName('VALEUR_STOCK_PF').AsCurrency;
          ExecSQL;
        END;
      Sds_Ligne.Next;
    end;

Via le profiler MSSQL, le BDE Remplaçant les paramètres on se retrouve avec cette requête :

Code :

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

1
2
3
4
SET NOCOUNT ON
DELETE OF_VAL WHERE NUMERO_OF='-1019401011'
INSERT OF_VAL(NUMERO_OF,VALEUR_STOCK_PF)
VALUES ('-1019401011',378.42)

en DBX :

Code :

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

1
2
3
4
5
exec sp_executesql N'SET NOCOUNT ON
DELETE OF_VAL WHERE NUMERO_OF= @P1 
INSERT OF_VAL(NUMERO_OF,VALEUR_STOCK_PF)
VALUES ( @P2 , @P3 )
',N'@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 float',N'-1031201012',N'-1031201012',201,9906

En ADO :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 varchar(11),@P2 varchar(11),@P3 float',N'SET NOCOUNT ON
DELETE OF_VAL WHERE NUMERO_OF=@P1
INSERT OF_VAL(NUMERO_OF,VALEUR_STOCK_PF)
VALUES (@P2,@P3)
','-1009301011','-1009301011',2069,6028999999999
select @p1
 
// puis 
exec sp_unprepare 2

Malgre le "prepared:=true" DBX s'en tape... et ADO aussi (il ne devrait pas y avoir sp_prepare, sp_execute, sp_execute... et sp_unprepare ?)

J’ai loupé quelque choses ?
Vu la gestion des paramètres de ADO ( un parambyname(‘x’).As_x :=’x’ ne remplaçant que la première occurrence recherchée) , je suis plus parti pour faire ma propre fonction de remplacement de paramètres…. Mais je voudrais avoir d’autres avis spécialisé

Merci

[ShaiLeTroll]

Le prepare explicite c'est pourtant exactement utile si l'on conserve l'objet Query et que l'on execute la même requete préparée avec des valeurs de paramètres différents !

le Driver DBX SQL Server semble ne pas gérer du tout le Prepare !
Cela me rappele IBX sur IB6, il fallait faire un prepare explicite car un simple Exec refaisait le tout, le gain était par 10

En ADO, je vois sp_prepexec (sp_prepare et sp_execute en une seule operation) et sp_unprepare, c'est vrai qu'il semble tout faire une fois
tu dois avoir une série de SET @... et sp_prepexec pour les itérations suivantes et finir par un sp_unprepare

Je ne connais pas la syntaxe exacte du DELETE avec jointure, ça doit ressembler à

Code sql :

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

DELETE OF_VAL FROM OF_VAL INNER JOIN Sds_Ligne ON OF_VAL.NUMERO_OF = Sds_Ligne .OF_VAL WHERE ... critere sur Sds_Ligne ...

INSERT SELECT

Code sql :

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

1
2
INSERT OF_VAL(NUMERO_OF,VALEUR_STOCK_PF)
     (SELECT NUMERO_OF, VALEUR_STOCK_PF FROM Sds_Ligne WHERE ... critere sur Sds_Ligne ... )

tu peux mettre une TEMPORARY TABLE stockage le SELECT Sds_Ligne WHERE ... critere sur Sds_Ligne ... utile si le where est complexe, mais tous les systèmes de DB, ne supporte pas d'Index sur une TEMPORARY du coup, le gain gagné sur le WHERE sera peut-être perdu sur la jointure

J'avais fait un rustine pour les paramètres doublé en ADO : ORACLE même SQL lancé via BDE ou ODA = DataSet différent

Insipé de TQuery.SetParamsFromCursor au lieu de TADOQuery.SetParamsFromCursor:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
   DataLinkDataSource := DetailDataSet.DataSource;
   DetailDataSet.DataSource := nil;
   if DataLinkDataSource <> nil then
   begin
     DataSet := DataLinkDataSource.DataSet as TCustomADODataSet;
     if DataSet <> nil then
       for I := 0 to DetailDataSet.Parameters.Count - 1 do
         with DetailDataSet.Parameters.Items[I] do
           Assign(DataSet.FieldByName(Name));
   end;

[philnext]

Pour moi le 'prepare' ne sert que lors d'utilisation multiple d'une même requête paramétrée.
Sinon, pour des requêtes sensible pour le performance :
* Je n'utilise jamais de requête paramétrées, car les premiers essais que j'ai faits laissaient voir une perte de perf.
* Le plus rapide (en général) était de passer par ADO.

[Eric.H]

tu dois avoir une série de SET @... et sp_prepexec pour les itérations suivantes et finir par un sp_unprepare

Ben c'est ce que je pensais mais non un sp_prerexec et un sp_unprepare a chaque fois.

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
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 varchar(11),@P2 varchar(11),@P3 float',N'SET NOCOUNT ON
DELETE OF_VAL WHERE NUMERO_OF=@P1
INSERT OF_VAL(NUMERO_OF,VALEUR_STOCK_PF)
VALUES (@P2,@P3)
','-1002501011','-1002501011',963,89999999999998
select @p1
go
exec sp_unprepare 1
go
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 varchar(11),@P2 varchar(11),@P3 float',N'SET NOCOUNT ON
DELETE OF_VAL WHERE NUMERO_OF=@P1
INSERT OF_VAL(NUMERO_OF,VALEUR_STOCK_PF)
VALUES (@P2,@P3)
','-1009301011','-1009301011',2069,6028999999999
select @p1
go
exec sp_unprepare 2
go

Je ne connais pas la syntaxe exacte du DELETE avec jointure, ça doit ressembler à

Code sql :

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

DELETE OF_VAL FROM OF_VAL INNER JOIN Sds_Ligne ON OF_VAL.NUMERO_OF = Sds_Ligne .OF_VAL WHERE ... critere sur Sds_Ligne ...

La table TSimpledataset Sds_Liste sert uniquement pour mon jeu de test avec données identiques pour les trois tests. En réalité ce sont des variables issues de calculs dans le programme. Le calcul (tous les 8h) se fait en moyenne sur 40000 "NUMERO_OF" car ils sont recalculés à -200 jours (param du notre client), donc les lignes OF_VAL pour un n°OF sont susceptible d'existait à 98%. Mais en effet dans le réel faire la jointure pour le delete pourrait faire gagne du temps, mais je pensais plutôt faire un IF EXISTS, UPDATE, INSERT.Bien sur la table OF_VAL a plus de deux champs dans la réalité.

J'avais fait un rustine pour les paramètres doublé en ADO : ORACLE même SQL lancé via BDE ou ODA = DataSet différent

Vu, si on part vers ADO (pas envie) j'allais faire le même genre de rustine

[ShaiLeTroll]

Pour moi le 'prepare' ne sert que lors d'utilisation multiple d'une même requête paramétrée..

C'est son cas

* Je n'utilise jamais de requête paramétrées, car les premiers essais que j'ai faits laissaient voir une perte de perf..

Cela dépend de la couche d'acces et de l'implementation dans la SGBD
Certains SGBD conservent en cache la requête même si pas explicitement préparé, si la requête change peu (juste les valeurs), certain SGBD sont assez malin pour réutiliser le cache

* Le plus rapide (en général) était de passer par ADO.

Pour un accès générique SQL, j'ai fais le même constat !
Pour un accès spécifique à un moteur, les DAC de Devart\CoreLab sont nettement meilleurs !

EDIT : Ah, c'est l'optimisation des Tests automatisés !
Il y en a qui vont loin ! J'ai pas de tests depuis 2 ans, c'était en PHPUnit, j'aimerais bien en faire sous C++Builder pour mes couches d'objet métier, pour au moins garantir leur stabilité, vu le nombre d'objet qui sont basés dessus !
Ce n'est pas la "méthode" où je travaille, c'est considéré comme du temps perdu surtout que j'aimerais même le faire en TDD mais comme j'ai pas de specs, c'est pas facile de voir où je vais !

le test de la cohérence des données, ouais c'est horrible, j'avais des codes de tests plus long que le code réel (un système de formulaire dynamique genre Google Form ou XMLRad), et encore, je considérais cela comme des tests d'intégration, car cela testait la fonctionnalité à haut niveau mais pas les méthodes privées de façon unitaire !

Avec DUnit, j'ai pratiqué pour voir, je mettais le code de Test dans le même unit que le code réel (avec directive), je testais principalement les private, les publiques souvent n'étaient qu'une succession d'appel de private, j'ai laché prise quand il a fallu tester la DB et monter un contexte de donnée à chaque test, oui c'était TRES long !

[Eric.H]

Pour moi le 'prepare' ne sert que lors d'utilisation multiple d'une même requête paramétrée.
Sinon, pour des requêtes sensible pour le performance :
* Je n'utilise jamais de requête paramétrées, car les premiers essais que j'ai faits laissaient voir une perte de perf.
* Le plus rapide (en général) était de passer par ADO.

Apres lecture aide BDE/DBX/ADO tu as raison sur le prépare.
On utilise historiquement les requêtes paramétrées pour ne pas être embêté avec les champs Datetime, float et C°. Et oui ADO semble le plus rapide (aussi rapide que le bon vieux BDE qui lui remplace les params lui même).
Par contre les proc SQL sp_prepare, sp_execute et sp_prepare devraient permettre de faire des insertion avec des params qui change (pas eu le temps de tester)

[ShaiLeTroll]

[quote=Eric.H;7107456]Ben c'est ce que je pensais mais non un sp_prerexec et un sp_unprepare a chaque fois.
QUOTE]

C'est franchement à chier !
On perd tout l'intéret de la préparation pour gérer un ensemble de valeur

aussi rapide que le bon vieux BDE qui lui remplace les params lui même

en même temps pour MS SQL, le BDE utilse ODBC, entre ADO et BDE sur ce coup là, cela ne change pas grand chose
Mais la même chose en Paradox, disons que passer 100 000 lignes, le SQL commençait à être un peu lent !

[Eric.H]

C'est franchement à chier !

C'est peut-être moi qui ais zapper quelque chose, car moi aussi je suis étonné.
C'est pour cela que je demande de l'aide

[Eric.H]

en même temps pour MS SQL, le BDE utilse ODBC, entre ADO et BDE sur ce coup là, cela ne change pas grand chose
Mais la même chose en Paradox, disons que passer 100 000 lignes, le SQL commençait à être un peu lent !

J'ai testé ADO en SQLCLI10 et en OLEDB, j'ai teste dbx en OLEDB (via dbx Devart), les résultats était les mêmes.
Le BDE remplacé lui même les params dans son pilote natif SQLMSS32.DLL (l'install d'un ODBC n'etait pas nécessaire), un client sql natif était ok, de memoire c'est la ntwdblib qui était/est utilisé pour l’accès SQL

[Edit] mon client m'indique que la moyenne des 40000 lignes de faisait en 1'20... maintenant il pleur (pour le moment)