Chargement de données: ligne à ligne ou bulk ?

1- Introduction
2- Conditions du test
3- Résultats
Annexe
- Historique
- Liens

1- Introduction

La métaphore de la boulangère, voir article sur la métaphore de la boulangère», a montré l'inefficacité naturelle d'interrogations ligne à ligne d'un SGBD. L'ecriture de données, en particulier l'insertion massive possède les mêmes caractéristiques : le mode ligne à ligne est incomparablement moins efficace que le mode par lots, autrement appelé Bulk.

Démonstration pour convaincre si besoin.

2- Conditions du test

2-1- La table de test TEST_TABLE

Sur une instance Sybase ASE 15.0.2 64 bits, une table simple TEST_TABLE, une douzaine de colonnes, pas de typage exotique, table dont voici la structure :

sp__help TEST_TABLE

Column name          Type         I Null Dflt Rule Table                Num        
 -------------------- ------------ - ---- ---- ---- -------------------- -----------
 a                    int          0 No             TEST_TABLE                     1
 b                    int          0 No             TEST_TABLE                     2
 c                    int          0 Yes            TEST_TABLE                     3
 d                    datetime     0 Yes            TEST_TABLE                     4
 e                    char(8  )    0 Yes            TEST_TABLE                     5
 f                    float        0 No             TEST_TABLE                     6
 g                    datetime     0 Yes            TEST_TABLE                     7
 h                    int          0 Yes            TEST_TABLE                     8
 i                    int          0 No             TEST_TABLE                     9
 j                    int          0 No             TEST_TABLE                    10
 k                    float        0 Yes            TEST_TABLE                    11
 l                    int          0 Yes            TEST_TABLE                    12
 m                    int          0 No             TEST_TABLE                    13

Le test va consister à insérer 100 000 lignes dans la table TEST_TABLE par 4 méthodes différentes :

par ordre SQL simple ( literal autoparam off ) : test #1
par ordre SQL simple ( literal autoparam on ) : test #2
par appel d'une procédure stockée : test #3
par bcp : test #4

2-2- Les données

Les données sont préalablement extraites sous forme de fichier plat délimité.

La vue suivante est créée de façon à mettre en forme les données, dans le cas présent les colonnes de type date

create view v_test_table
as
select  a, b, c,
        convert(varchar(8),d,112) d,
        e, f, 
        convert(varchar(8),g,112) g,
        h,i,j,k,l,m 
from TEST_TABLE
go

Puis le fichier CSV de données en généré en s'appuyant sur cette vue.

bcp test..v_test_table out data.bcpc -c -t';' -S ... -U ... -P ...

Le fichier CSV a alors la structure type suivante :

head data.bcpc

117769;27466512;95;20090811;01:15:22;.35311999999999999;20090812;;0;11833;4.125;19393;2026
121600;24414704;95;20090527;00:41:35;.19444444;20090601;;0;11833;8.75;19393;1952
117769;27466511;95;20090812;01:35:43;.37077599999999999;20090813;;0;11833;4.125;19393;2027
121600;24414703;95;20090527;00:41:35;.19444444;20090601;;0;675;8.75;;889
117769;27466510;95;20090811;01:15:22;.35311999999999999;20090812;;0;675;4.125;;963
121605;24414706;95;20090527;00:41:35;.96250000000000002;20090601;;0;11833;7.875;19393;1952
117769;27466509;95;20090812;01:35:43;.37077599999999999;20090813;;0;675;4.125;;964
121605;24414705;95;20090527;00:41:35;.96250000000000002;20090601;;0;675;7.875;;889
117836;2913586;95;20070405;20:11:02;0.0;20070404;;0;675;0.0;;194
117836;2913587;95;20070405;20:11:02;0.0;20070404;;0;11833;0.0;19393;1257

awk va permettre de transformer ce fichier de données en ordres SQL :

 cat csv2sql.awk

BEGIN {
        FS=";"
}

function quote(x) {
        if ( x == "" ) { x="NULL" } else { x="'" x "'" }
        return x
}

function isnull(x) {
        if ( x == "" ) { x="NULL" }
        return x
}

{ 
        $1=isnull($1)
        $2=isnull($2)
        $3=isnull($3)
        $4=quote($4)
        $5=quote($5)
        $6=isnull($6)
        $7=quote($7)
        $8=isnull($8)
        $9=isnull($9)
        $10=isnull($10)
        $11=isnull($11)
        $12=isnull($12)
        $13=isnull($13)
        printf "insert TEST_TABLE ( a,b,c,d,e,f,g,h,i,j,k,l,m ) values ( %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s )\n", $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13

        if ( (NR % 50 )  == 0 ) { print "go" }
}

END {
        print "go"
}

Dans le script ci-dessus les actions sont regroupées par lots de 50 insertions.

SHELL> nawk -f csv2sql.awk data.bcpc > insert.sql

Pour le test #3 - appel de procédure stockée, l'instruction insert into TEST_TABLE ... est remplacée par exec proc_insert... dans la commande printf finale.

2-3- Tests #1 et #2 : par ordre SQL simple

Les 100 000 insertions sont réalisés par lots de 50. Le paramètre literal autoparam va permettre de mesurer le coût de la compilation des ordres SQL.

head insert.sql

insert TEST_TABLE ( a,b,c,d,e,f,g,h,i,j,k,l,m ) values ( 117769,27466512,95,'20090811','01:15:22',.35311999999999999,'20090812',NULL,0,118
33,4.125,19393,2026) 
insert TEST_TABLE ( a,b,c,d,e,f,g,h,i,j,k,l,m ) values ( 121600,24414704,95,'20090527','00:41:35',.19444444,'20090601',NULL,0,11833,8.75,1
9393,1952) 
insert TEST_TABLE ( a,b,c,d,e,f,g,h,i,j,k,l,m ) values ( 117769,27466511,95,'20090812','01:35:43',.37077599999999999,'20090813',NULL,0,118
33,4.125,19393,2027) 
go
insert TEST_TABLE ( a,b,c,d,e,f,g,h,i,j,k,l,m ) values ( 121600,24414703,95,'20090527','00:41:35',.19444444,'20090601',NULL,0,675,8.75,NUL
 ...

2-4- Test #3 : par appels de procédure

Les 100 000 insertions sont réalisées par lots de 50. L'ordre SQL est remplacé par un appel de procédure stockée dont voici le code:

create proc proc_insert
 @a  int          , @b  int          , @c  int          , @d  datetime     ,
 @e  char(8  )    , @f  float        , @g  datetime     , @h  int          ,
 @i  int          , @j  int          , @k  float        , @l  int          ,
 @m  int          
as
begin
insert TEST_TABLE ( a,b,c,d,e,f,g,h,i,j,k,l,m ) 
values ( @a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l,@m )
end
go

head proc.sql

exec proc_insert  117769,27466512,95,'20090811','01:15:22',.35311999999999999,'20090812',NULL,0,11833,4.125,19393,2026
exec proc_insert  121600,24414704,95,'20090527','00:41:35',.19444444,'20090601',NULL,0,11833,8.75,19393,1952
exec proc_insert  117769,27466511,95,'20090812','01:35:43',.37077599999999999,'20090813',NULL,0,11833,4.125,19393,2027
go
 ...

2-5- Test #4 : par lots avec le binaire bcp

Les 100 000 insertions sont réalisées avec le binaire bcp qui va interpreter le fichier csv et l'intégrer en base par lot de 1000 lignes

bcp test..TEST_TABLE in data.bcpc -c -t';' -S <ServerName> -U <UserName> -P <Password> -b1000

3- Résultats

Les 4 tests ont été lancés 5 fois consécutivement, entre chaque lancement la table est vidée. Chacun des tests a été exécuté avec et sans clé primaire sur la table.

3-1- Données

100 000 insert	Durée ( HH:MM:SS )	%
sql autoparam off	00:01:58	118%
sql autoparam on	00:01:46	106%
proc	00:01:40	100%
bcp	00:00:02	2%

Le résultat est sans appel. La méthode ensembliste est 50 fois plus rapide que l'unitaire.

On observe néanmoins des caractéristiques moteur interressantes :

Sur ce traitement ultra-simple, le paramètre autoparam procure un gain de 10% de performance par rapport au fonctionnement historique. La différence est le coût de la paramétrisation de la requête ( remplacement d'une variable litérale - 2, 14, 0.123 par @@1, @@2, @@3 , ... )
L'appel de procédure est systématiquement plus performant que l'appel SQL direct avec un ordre INSERT.

3-2- Comment ça marche ?

Le mode bulk diffère essentiellement de deux manières du mode d'insertion classique :

En allouant non pas des pages de données quand nécessaire mais des extents ( 8 pages). Cette valeur d'allocation est d'ailleurs paramétrable (number of prealocated extents )
En ne journalisant pas les insertions, mais seulement les allocations de page.

D'autres facteurs entrent en compte pour expliquer la différence : les triggers/rules ne sont pas déclenchés, la taille du packet réseau entre le client et le serveur (paramètre -A) influe également sur la rapidité d'echange entre le client et le serveur.

3-3- Que "dit" sp_sysmon ?

Les extraits de sp_sysmon (cf § 3-3-1-, § 3-3-2- et §3-3-3-) sont des captures prises pendant une minute au moment de l'insertion de données.

Les durées reportées ci-dessus sont confirmées techniquement par l'observation de comportement différents de ces trois méthodes vis-à-vis :

de la gestion transactionnelle (+ de 50000 transactions pour les méthode Procédure stockée/ordres SQL Insert contre moins de 20 pour bcp )
de la pose de verrous (+ de 300 000 verrous en mode ligne à ligne contre 1100 en mode bulk )
des interactions réseau (plus de 10000 Network requests en ligne à ligne contre 4000 en mode bulk)
des interactions disque (plus de 50000 I/Os en ligne à ligne, moins de 1000 I/Os en bulk )

Les méthodes employées par le moteur sont dont radicalement différentes et expliquent les différences de performances incomparables.

3-3-1- sp_sysmon : mesure durant l'insertion par ordres SQL INSERT

=============================================================================== 
Transaction Profile
-------------------
 
  Transaction Summary             per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
    Committed Xacts                 870.1           n/a       52203     n/a     
 
  Transaction Detail              per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
    Inserts
      APL Heap Table                889.4           1.0       53365      89.7 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table          102.6           0.1        6154      10.3 %
      Fast Bulk Insert                0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
    Total Rows Inserted             992.0           1.1       59519      96.0 %
 ...
===============================================================================  
Transaction Management
----------------------
 ...
  Transaction Log Writes            966.7           1.1       58002       n/a   
  Transaction Log Alloc             161.4           0.2        9684       n/a   
 ...
=============================================================================== 
Lock Management
---------------
 
  Lock Summary                    per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
  Total Lock Requests              5528.7           6.4      331721       n/a 
=============================================================================== 
Disk I/O Management
-------------------
 ...
  Total Requested Disk I/Os        1032.8           1.2       61967             
 ...
=============================================================================== 
Network I/O Management
----------------------
 
  Total Network I/O Requests        244.6           0.3       14678       n/a

3-3-2- sp_sysmon : mesure durant l'insertion par procédures stockées

=============================================================================== 
Transaction Profile
-------------------
 
  Transaction Summary             per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
    Committed Xacts                 928.7           n/a       55721     n/a     
 
  Transaction Detail              per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
    Inserts
      APL Heap Table               1222.2           1.3       73330      95.5 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table           57.1           0.1        3428       4.5 %
      Fast Bulk Insert                0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
    Total Rows Inserted            1279.3           1.4       76758      97.3 %
 ...
===============================================================================  
Transaction Management
----------------------
 ...
  Transaction Log Writes           1026.7           1.1       61601       n/a   
  Transaction Log Alloc             163.5           0.2        9808       n/a   
 ...
=============================================================================== 
Lock Management
---------------
 
  Lock Summary                    per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
  Total Lock Requests              5834.9           6.3      350093       n/a 
=============================================================================== 
Disk I/O Management
-------------------
 ...
  Total Requested Disk I/Os        1107.1           1.2       66426             
 ...
=============================================================================== 
Network I/O Management
----------------------
 
  Total Network I/O Requests        197.4           0.2       11843       n/a

3-3-3- sp_sysmon : mesure durant l'insertion par lots via bcp

=============================================================================== 
Transaction Profile
-------------------
 
  Transaction Summary             per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
    Committed Xacts                   1.4           n/a          14     n/a     
 
  Transaction Detail              per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
    Inserts
      APL Heap Table               1748.4        1248.9       17484      14.9 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.7           0.5           7       0.0 %
      Fast Bulk Insert            10000.0        7142.9      100000      85.1 %
  -------------------------  ------------  ------------  ----------  ---------- 
    Total Rows Inserted           11749.1        8392.2      117491     100.0 %
 ...
===============================================================================  
Transaction Management
----------------------
 ...
  Transaction Log Writes              3.2           2.3          32       n/a   
  Transaction Log Alloc               2.8           2.0          28       n/a   
 ...
=============================================================================== 
Lock Management
---------------
 
  Lock Summary                    per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
  Total Lock Requests               110.0          78.6        1100       n/a 
 ...
=============================================================================== 
Disk I/O Management
-------------------
 ...
  Total Requested Disk I/Os          65.7          46.9         657             
 ...
=============================================================================== 
Network I/O Management
----------------------
 
  Total Network I/O Requests        400.8         286.3        4008       n/a

Annexe

Historique

Version	Date	Commentaires
1.0	11/2009	Version initiale

Liens

SQLPAC : Le cache de requêtes
SQLPAC : La paramétrisation littérale
Sybase ASE 15.0.2 BOL, Utility guide : Using bcp to Transfer Data to and from Adaptive Server