Améliorer la performance de vos travaux de fin de journée par “JDBC Batch” et Spring

L’une des fonctionnalités la moins connu de JDBC est sa capacité à exécuter des opérations SQL par Lot en combinat les méthodes addBatch et executeBatch de Statement (et les autres variantes de Statement).

Je présente dans cette étude une expérience simple qui montre l’utilité, d’un point de vue temps de réponse globale, de l’exploitation judicieuse de cette fonctionnalité.

Exécution d'opérations par lot

Pour améliorer les performances lors de la réalisation de mises à jour multiples d'une base de données, un grande nombre de pilote JDBC (Oraxcle, PostgrSQL, MS SQL) offre la possibilité de soumettre plusieurs mises à jour comme un seul travail, appelé également lot.

La méthode addBatch permet d'ajouter une commande.

La méthode executeBatch permet de soumettre toutes les commandes pour traitement.

Seules des instructions DDL (Data Definition Language, langage de définition de données) et DML (Data Manipulation Language, langage de manipulation de données) qui retournent un seul nombre de mises à jour peuvent être exécutées dans un lot.

La méthode executeBatch retourne un tableau de valeurs int correspondant au nombre de mises à jour de chaque commande.

JDBC et Exécution d'opérations par lot

Regroupement de plusieurs mises à jour:

connexion.setAutoCommit(false);

Statement st = connexion.createStatement();

st.addBatch("INSERT ..."); // Ajoute une requête SQL pour executeBatch

st.addBatch("UPDATE ..."); // Ajoute une requête SQL pour executeBatch

int[] nb = st.executeBatch(); // retourne le nombre de Mise à jour effectuées

Méthode « appropriée pour le codage » : exploiter l’abstraction de l’Ioc avec Spring

Je recommande d’utiliser la combinaison suivante

a) PreparedStatement

b) Spring templates pour les Batch updates

Ainsi, On peut combiner des « Prepared Statement » et des « Batch updates ».

DataSource dataSource = (DataSource) context.getBean("dataSource"); int nombreDefois = 2000; int batchSize =10; BatchInsert batchInsert = new BatchInsert(dataSource, batchSize); testBatchInsert(nombreDefois, batchInsert); /// private static void testBatchInsert(int count, BatchInsert batchInsert) throws DataAccessException { for (int i = 0; i < count; i++) { batchInsert.update(new Object[]{i + 10L, "a" + i, i}); } } 100 String SQL_INSERT_ARTICLE = "INSERT INTO article(id,label,price)VALUES(?,?,?)"; BatchInsert(DataSource dataSource, int batchSize) { super(dataSource, SQL_INSERT_ARTICLE); declareParameter(new SqlParameter(Types.BIGINT)); declareParameter(new SqlParameter(Types.VARCHAR)); declareParameter(new SqlParameter(Types.NUMERIC)); setBatchSize(batchSize); }

Outils de mesure :

Le plus simple c’est d’utiliser un profiler ou bien l’AOP avec Spring.

Dans cette présentation, j’ai utilisé NetBeans profiler version 6.5.1.

24 mesures ont été effectuées dans les conditions suivants

Un Laptop avec 2Go De RAM (les mesures n’ont pas de valeurs intrinsèques, c’est la comparaison des résutlats qui m’interesse)

Une base de données PostgrSQL sous Windows

La table est vidée après chaque exécution (avoir les mêmes conditions)

Les paramètres qui ont été variés

Le nombre d’insertion : de 100 à 10000

La valeur batchsize (taille du lots d’opérations SQL à batcher ) : de 1 à 200

Les résultats sont les suivants :

nombre de fois	Batchsize (ms)
	1	10	50	200
	336	223	215	-
500	914	386	281	253
1000	1627	792	349	320
2000	2867	760	485	446
5000	7801	1751	1022	871
10000	15901	2697	1529	1269

L’impact de

Ramené à l’unité : temps de réponse moyen d’une seule insertion

nombre de fois	Batchsize (ms) pour une seule insertion
	1	10	50	200
100	3,36	2,23	2,15	-
500	1,828	0,772	0,562	0,506
1000	1,627	0,792	0,349	0,32
2000	1,4335	0,38	0,2425	0,223
5000	1,5602	0,3502	0,2044	0,1742
10000	1,5901	0,2697	0,1529	0,1269

Si on estime le gain en % de temps de réponse pour une insertion, on remarque l’impact de cette méthode sur le résultat final pour les grands nombre d’insertion (jusqu’à 90% !!).

gain en % en fonction du batchSize pour une seule insertion

nombre de fois	batchsize
	1	10	50	200
100	3,36	33,6%	36,0%	-
500	1,83	57,8%	69,3%	72,3%
1000	1,63	51,3%	78,5%	80,3%
2000	1,43	73,5%	83,1%	84,4%
5000	1,56	77,6%	86,9%	88,8%
10000	1,59	83,0%	90,4%	92,0%

Observations annexes :

La création d’objets relatifs à l’opération JDBC, java.sql.*, permet de comprendre comment se passe les opérations.

Cas : batch size = 1 et 10000 insert

Cas : batch size = 50 et 10000 insert

Cas : batch size = 200 et 10000 insert

Objets crées par Spring

Confirmation du nombre d’appel à getConnection() : dans ce cas 10 fois moins d’appel à la connexion à la base de données que d’appel à la méthode update (qui contient la requête SQL SQL)

Conclusion

Pour améliorer les performances lors de la réalisation d’un grand nombre de mises à jour d'une base de données, il est fortement recommandé d’exploiter la capacité de JDBC à réaliser des mises à jour par lot.