Cette semaine, j’ai du mettre à jour un batch d’import de données d’un fichier plat dans une base de données. L’optimisation de ce script n’ayant jamais été une priorité, le batch importait environ 60000 lignes et prenait entre 15 et 20 minutes. En quelques ajustement, j’ai réussit à importer plusieurs centaines milliers de lignes en à peine quelques minutes. Aussi étonnant que cela puisse paraître, l’optimisation d’un tel script ne nécessite pas de connaissances particulières ni même de mettre en place une solution de multi-threading utilisant les ordinateurs de la NASA ou ceux de Google. Tout est proposé de manière native au sein de la librairie JDBC (depuis la version 2) et permet le traitement par lot des requêtes SQL . Par conséquent, toute les solutions reposant sur cette API de connexion à un base de donnée peuvent également utiliser cette fonctionnalité, que ce soit JdbcTemplate pour Spring ou encore Hibernate.

Le script utilisait une fonction d’insertion Sql unitaire, c’est à dire qu’elle créait pour chaque ligne un statement pour effectuer l’insert. Heureusement qu’un système de pool avait été mis en place pour gérer les connexions sinon la base serait probablement tombée depuis longtemps. Il est possible que ce que vous venez de lire vous ai fait sursauter de frayeur, mais malheureusement, il existe encore aujourd’hui de nombreux scripts non optimisés.

Tous les exemples de code ci-dessous reposent sur le même cas d’utilisation. Il s’agit d’un import d’une liste de produits sous forme d’un fichier plat contenant 2 champs: le nom et la designation.  Dans notre exemple, l’import se déroule en 2 étapes:

• Lecture complète du fichier, et mise en mémoire dans une liste des Produits
• Importation dans la base de la liste des produits d’un seul coup

 

Avant même d’utiliser le traitement par batch, une simple optimisation de code en utilisant un PreparedStatement peut parfois améliorer significativement les performances. C’est d’ailleurs l’unique objectif du PrepareStatement. Le code ci-dessous est un exemple très simple d’utilisation du PreparedStatement. Attention, l’exemple ci-dessous ne gère pas les exceptions et ne s’assure pas que les objets soient correctement fermés (Connections,Statement, etc).

	public void grosImportProduits(final List<produit> listeProduits) {

		Connection connexion=GestionSGBD.getConnexion();
		PreparedStatement ps=connexion.prepareStatement("INSERT INTO GROSSETABLEPRODUIT WorkCdms (produitNom,produitDescription) values (?,?)");
		for (Produit produit : listeProduits) {
			ps.setString(1, produit.getProduitNom());
			ps.setString(2, produit.getProduitDesignation());
			ps.execute();
		}

	}

Cette solution peut donc s’appliquer pour de petits jeu de données (Maximum 1000 lignes), et encore, vous risquez de vous confronter au nombre d’exécutions maximums limité par la base de donnée pour un même preparedStatement.

Dans tous les cas, les méthodes de batch sont plus optimisées et amélioreront significativement les performances sans changer fondamentalement vos scripts comme le montre l’exemple -ci-dessous:

	public void grosImportProduits(final List<Produit> listeProduits) {

		Connection connexion=GestionSGBD.getConnexion();
		PreparedStatement ps=connexion.prepareStatement("INSERT INTO GROSSETABLEPRODUIT WorkCdms (produitNom,produitDescription) values (?,?)");
		for (Produit produit : listeProduits) {
			ps.setString(1, produit.getProduitNom());
			ps.setString(2, produit.getProduitDesignation());
			ps.addBatch();
		}

		ps.executeBatch();
	}

Si vous utilisez les JdbcTemplates de Spring, c’est encore plus simple puisque la méthode batchUpdate peut gérer directement une liste de d’objet à l’aide d’un BatchPreparedStatementSetter.

	public int[] grosImportProduits(final List<Produit> listeProduits) {

		int[] updateCounts = getJdbcTemplate()
				.batchUpdate(
						"INSERT INTO GROSSETABLEPRODUIT (produitNom,produitDescription) values (?,?)",
						new BatchPreparedStatementSetter() {
							public void setValues(PreparedStatement ps, int i) throws SQLException {
								ps.setString(1, listeProduits.get(i).getProduitNom());
								ps.setString(2, listeProduits.get(i).getProduitDesignation());
							}

							public int getBatchSize() {
								return listeProduits.size();
							}
						});
		return updateCounts;
	}

Ces solutions permettent de charger les données beaucoup plus rapidement. A titre indicatif, le temps d’importation de ma liste de 60000 produits est passé de plusieurs dizaines de minutes à 1 ou 2 minutes.

Et si on importait plus de données?

Maintenant, si nous passons le nombre de produits à importer à un million au lieu des 60000 du départ, que se passerait-il? Et bien, c’est simple, soit votre serveur est costaud et absorbe sans problème un million de Produits dans un tableau ou une liste, soit vous aurez une belle erreur du type OutOfMemoryError ou Java Heap Space. Y’a pas de raison que vous ayez un PC plus puissant que le mien ;-).

Il n’est donc pas possible de conserver le fonctionnement de départ qui consistait à importer intégralement le fichier avant de l’insérer dans la base. Il faut donc régulièrement inserer notre liste de produits dans la base pour pouvoir vider notre tableau temporaire, et ensuite recommencer.

Mais dans ce cas, à quel moment faut-il effectuer l’insertion dans la base? Toutes les 1000 ? 5000? 10 000 ou 100 000 lignes?

Pour répondre à cette question, j’ai donc effectuer un rapide test de performance sur l’insertion de 100 000 lignes. Tous les x produits, j’importais les donnés dans la base, vidais la lister, et appelait manuellement le Garbage Collector (juste au cas où :-))

Nb items / lot	Tps 1(en ms)	Tps 2 (en ms)	Moy Tp1 +Tps2	Appro Mémoire occupée
1000	34709	33434	34071,5	70Mo
5000	25475	24260	24867,5	70Mo
10000	24136	26288	25212	80Mo
50000	27857	23090	25473,5	130Mo
100000	31015	33887	32451	150Mo

Tout d’abord, je tiens à préciser qu’il s’agit là d’un tableau donné à titre indicatif. Les performances et surtout la quantité de mémoire utilisée peut varier d’un environnement à l’autre. Si vous avez vous-même modifié certains paramètres permettant d’améliorer ou de modifier les performances de votre JVM, les résultats peuvent être considérablement différents, d’autant que le nombre de paramètre pour la JVM sont nombreux (-Xms,-Xmx,-XX:PermSize,-XX:MaxPermSize, etc..). Les résultats données içi sont donc basé sur les paramètres par défaut de la JVM.

Comme on peut le voir, un lot trop petit de produits n’est pas très performant. L’augmentation du nombre de produits par lot n’induit pas nécessaire un augmentation significative de la mémoire occupé. Il semble donc préférable d’avoir des lots compris entre 10 000 et 50 000 produits. Avec les paramètres par défaut de la JVM, la différence de mémoire  utilisé pour une liste de 10 000 ou 50 000 produit est insignifiant au regard des performances. Au delà, la mémoire gonfle très rapidement, et pourrait donner lieu à des dépassement mémoire. Il est bien évident que le problème ne se pose pas si vous avez beaucoup de mémoire sur votre PC ou votre serveur. Il faut donc adapter le paramètre en fonction de l’infrastructure sur lequel tourne votre batch d’importation. Si votre infrastructure et les paramètres de votre JVM vous le permettent, vous pouvez bien évidement augmenter le nombre de produits pour chaque lot.

Pour vérifier l’utilisation mémoire, j’ai effectué un second test où j’ai importé 200 000 produits. J’ai conservé le même nombre de produits dans mes lots.

Nb items / lot	Tps 1(en ms)	Mémoire occupée
1000	56563	70Mo
5000	43559	70Mo
10000	39858	80Mo
50000	40946	130Mo
100000	58202	150Mo

Comme on peut le voir, la mémoire utilisé reste sensiblement la même, et n’augmente pas. J’ai fait le même constat en important 800 000 produits. Il est donc possible avec cette solution d’importer de plus en plus de produits sans provoquer de dépassement mémoire. Les performances restent très largement remarquable par rapport à une importation simple ligne par ligne ou même par l’intermédiaire du PreparedStatement. Il est donc évident qu’il vous faut préférer cette solution dès qu’il s’agit d’importation massive.

 

Sur le même sujet:

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