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.
Conditions du test
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 :
exec sp__help TEST_TABLEColumn 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
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 est 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.bcpc117769;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 :
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.
nawk -f csv2sql.awk data.bcpc > insert.sqlPour 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.
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.sqlinsert 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
...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 procedure 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
gohead proc.sqlexec 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
...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> -b1000Ré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.
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
autoparamprocure 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.
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 preallocated 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’échange
entre le client et le serveur.
Que "dit" sp_sysmon ?
Les extraits de sp_sysmon 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.
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 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 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