Discussion :

[kilicool]

Bonjour,

J'ai une insertition massive d'information provenant d'une table vers une table temporaire

La requête insert 280 000 lignes.

Si je l'execute depuis MySQL avec la commande INSERT ... SELECT ça prends 11 secondes

Si je le programme en JAVA (avec des PreparedStatement) j'en ai pour 10 MIN !!!

Quelqu'un peut m'aider afin que cela soit fait en Java mais rapidement ?
Merci

[CinePhil]

Si les deux tables sont sur le même serveur, tu peux utiliser la syntaxe :

Code SQL :

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

1
2
3
INSERT INTO laTableTemp
SELECT lesColonnes
FROM laTableSource

En préfixant éventuellement le nom des tables par le nom de la base de données si ce n'est pas la même.

Si elles sont sur deux serveurs différents, tu peux utiliser le couple SELECT INTO OUTFILE / LOAD DATA INFILE qui transfère les données dans un fichier texte puis récupère ces données à partir du fichier texte généré à la première étape.


Si c'est purement un problème Java ==> Forum Java.

[fr1man]

Montre nous ton code java.

[kilicool]

VOila mon code

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
//..
 
ResultSet result = statement.executeQuery(sql2+ " LIMIT "+ind+","+paquet);
	String insert = "insert into tablesTemporaire.T7951254728407921N207(`clients.ville`,`clients.nom`,`polices.ancpolice`,`polices.police`,`polices.compagnie`,`flottes_principal.id_flottes`,`flottes_principal.type`,`flottes_principal.prim_an_ttc`,`flottes_principal.num_police`,`polices.coderisque`,`polices.garanties`,`flottes_principal.prim_an_ht`,`flottes_principal.etat`,`clients.numclient`,`clients.lienclient`,`polices.id`,`flottes_param.champ14`,`flottes_param.champ0`,`flottes_param.champ1`,`flottes_param.champ2`,`flottes_param.champ12`,`flottes_param.champ15`,`flottes_param.champ16`,`flottes_param.champ17`,`flottes_param.champ56`,`flottes_param.champ53`,`flottes_param.champ57`,`flottes_param.champ55`,`flottes_param.champ52`) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	con2.setAutoCommit(false);
	PreparedStatement preparedStatement = con2.prepareStatement(insert);
	while(result.next()){
		preparedStatement.setString(1, result.getString(1));
		preparedStatement.setString(2, result.getString(2));
		preparedStatement.setString(3, result.getString(3));
		preparedStatement.setString(4, result.getString(4));
		preparedStatement.setString(5, result.getString(5));
		preparedStatement.setInt(6, result.getInt(6));
		preparedStatement.setString(7, result.getString(7));
		preparedStatement.setDouble(8, result.getDouble(8));
		preparedStatement.setString(9, result.getString(9));
		preparedStatement.setString(10, result.getString(10));
		preparedStatement.setString(11, result.getString(11));
		preparedStatement.setDouble(12, result.getDouble(12));
		preparedStatement.setString(13, result.getString(13));
		preparedStatement.setLong(14, result.getLong(14));
		preparedStatement.setInt(15, result.getInt(15));
		preparedStatement.setInt(16, result.getInt(16));
 
		preparedStatement.setString(17,"");
		preparedStatement.setString(18,"");
		preparedStatement.setString(19,"");
		preparedStatement.setString(20,"");
		preparedStatement.setString(21,"");
		preparedStatement.setString(22,"");
		preparedStatement.setString(23,"");
		preparedStatement.setString(24,"");
		preparedStatement.setString(25,"");
		preparedStatement.setString(26,"");
		preparedStatement.setString(27,"");
		preparedStatement.setString(28,"");
		preparedStatement.setString(29,"");
 
		preparedStatement.addBatch();
	       // commit toutes les 100 insertions
 
		nb++;
 
		if((nb % 100) == 0) {
	    	preparedStatement.executeBatch();
	        con2.commit();
		}
 
		if(nb == paquet){
			ind += paquet;
			result = statement.executeQuery(sql2+ " LIMIT "+ind+","+paquet);
			nb = 0;
		}
	}
 
    preparedStatement.executeBatch();
    con2.commit();
 
//..

[kilicool]

Bon plus de réponses j'ai beau creuser je vois rien qui remplace le traitement effectue par la bdd avec le insert select

[JulienDuSud]

Y a quelque chose que je comprend pas... Pourquoi ne pas simplement balancer ton "insert into ... select" dans ton code java, au lieu de récupérer un à un chaque ligne, de recréer une à une des requêtes et de les envoyer une à une ?

Tu perds toute l'optimisation mysql en faisant ça, c'est pas étonnant que ça prenne 10 fois plus de plombes, t'as autant de requêts en plus...

Au lieu d'avoir 1 requête que mysql peut optimiser, tu as:

280 000 / 100 = 2800 requêtes de selection
+ 280 000 requêtes d'insertion
= 282 800 requêtes.

C'est un peu comme déplacer un sac de 300 000 graines, graine par graine.

[kenz79]

Bonjour à tous,

Je ne sais pas si le sujet à été traité ailleurs ou pas, mais j'ai trouvé ce poste qui traite un problème similaire.
Je veux insérer des lignes, beaucoup de lignes (64000) dans une tables déjà existante dans ma BDD Orcale. Ces lignes sont lues dans un fichier qui est généré par une application.

Mon fichier : "theImportFile.txt" ressemble à ça :

INSERT INTO T_TABLE ( ID_CD, CD_C2, CD_C3, CD_C4) VALUES ('01','10','11','12');
INSERT INTO T_TABLE ( ID_CD, CD_C2, CD_C3, CD_C4) VALUES ('02','15','16','17');
INSERT INTO T_TABLE ( ID_CD, CD_C2, CD_C3, CD_C4) VALUES ('03','18','19','20');
...
6400 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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

public boolean maMethode(String theTitre, String theImportFile)
    {
    Connection connexion = null;
    StringTokenizer tokenLine = new StringTokenizer(theImportFile, "\n");
    String currentRecord = null;
    Statement statement = null;
    
    try {
      connexion = getConnection();
      connexion.setAutoCommit(false);
      statement = connexion.createStatement();

      //Traitement du fichier à lire
      while (tokenLine.hasMoreTokens()) {
        currentRecord = tokenLine.nextToken();

        // on saute les lignes vides
        if ("".equals(currentRecord.trim())) {
          continue;
        }
        statement.addBatch(currentRecord);
      }
      
      int[] result = statement.executeBatch();

      if (result == null || result.length < 1) {
    	  connexion.rollback();
        throw new NotUpdatedException("echec");       
      }
      
      connexion.commit();// c'est ici que l'on valide la transaction
      connexion.setAutoCommit(true);
    
      
    } catch (BatchUpdateException bat) {
      logger.error("Insertion, erreur après la ligne : " + String.valueOf(bat.getUpdateCounts().length));
      throw new LynxTechnicalException(bat);
    } catch (SQLException sql) {
      throw new LynxTechnicalException(sql);
    } finally {
    
      close(statement);
      close(connexion);
    }

    return true;
  }

Le problème c'est que je tombe toujours sur un TimeOut après 1200 sec (20 min).
Je pensais que excuteBatch est fait spécialement pour les transaction par lots, pourtant c'est là où ça mais énormément de temps.

Alors je voudrais savoir s'il y a une solution ou un contournement à ce problème de performance, ou si quelqu'un à traiter le même sujet.
Merci d'avance pour vos réponse

[mavina]

Pourquoi pas diviser ton traitement en 10 par exemple, avec 10 thread ?

Je dis 10, mais ca peut être moins ou plus, tout dépend des perfs des threads

F.

[henolivier]

Je ne sais pas trop si c'est que tu voulais dire par thread mais pourquoi ne pas gérer plutôt des commits intermédiaires ?

Plutôt que de chercher a exécuter un insert de 6400 en une fois avec un seul commit a la fin, essaie plutôt d'exécuter 10 commit de 640 lignes (cela se ferait en ajoutant une clause de l'autocommit a false jusqu'au commit en n'oubliant pas de fermer le statement dedans).

Ça permettrait a executeBatch d'exécuter beaucoup moins de données d'un coup.

[kenz79]

Merci pour vos réponses.

Alors, concernant le traitement par thread je ne sais pas si on peut le faire sur executeBatch vu que c'est une méthode Statement.sql
, mais si tu peux me dire plus ou m'orienter ça m'aiderais plus.

Pour les commit intermédiaire,j'en ai déjà pensé mais c'est vraiment barbare comme méthode surtout que c'est l'EJB qui traite toute les transactions à chaque sortie de méthode et en plus ça ne me permet pas de gérer mais sortie d'echec.

Ce matin j'ai pensé à un truc. Je vais faire des executeBatch chaque 1000 ou 10000 insert, je vais le tester et je vous tient au courant.

Merci encore une fois et je suis toujours preneur des nouvelles idées.

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
public boolean maMethode(String theTitre, String theImportFile)
    {
    Connection connexion = null;
    StringTokenizer tokenLine = new StringTokenizer(theImportFile, "\n");
    String currentRecord = null;
    Statement statement = null;
 
    try {
      connexion = getConnection();
      connexion.setAutoCommit(false);
      statement = connexion.createStatement();
 
      //Traitement du fichier à lire
      while (tokenLine.hasMoreTokens()) {
        currentRecord = tokenLine.nextToken();
 
        // on saute les lignes vides
        if ("".equals(currentRecord.trim())) {
          continue;
        }
 
         statement.addBatch(currentRecord);
 
	// commit toutes les 10000 insertions
	if ((iInsert % 10000) == 0) {
	int[] result = statement.executeBatch();
		if (result == null || result.length < 1) {
		connexion.rollback();
		throw new NotUpdatedException("echec");
		}
	        statement.clearBatch(); // On vide le batch transaction			
	        }
   }
	// On execute les dernières lignes du script
	int[] result = statement.executeBatch();
	if (result == null || result.length < 1) {
	    connexion.rollback();
	    throw new NotUpdatedException("importerMatriceExcel echec");
	     }
	connexion.commit();// c'est ici que l'on valide la transaction
	connexion.setAutoCommit(true);
    }
 
    return true;
  }

[mavina]

Bah par exemple tu coupes ton fichier en 4 et dans chaque thread tu lis 1/4 du fichier et tu execute ton statement.

Ceci étant dit, les thread pour accéder à des fichiers, j'ai jamais essayé

Ca ira peut-être (surement) plus vite, mais de là à dire que ca sera vraiment significatif...

[Baptiste Wicht]

Comme déja dit, il faut impérativement effectuer des commits manuels tous les x insertions.

J'avais du faire un script d'insertion massive ou je bossais avant. Je faisais une extraction de toutes les données de la base de données et je les réinsérais ensuite en les anonymisant. Je me rappelle que je faisais des commits tous les 2500 INSERT, c'était ce qui prenait le moins de temps.

Par contre, j'avais pas fait en multi-thread, le fait de séparer les commits avait suffisamment amélioré les performances.

[kenz79]

Pour les thread e vais essayé ça va être un peu laborieux vu que le fichier à lire est récupéré d'une autre appli. Je vais essayé et je vous tiens informé.

Concernant les commit chaque 2500 insert (plus ou moins) c'est risqué comme manip. Surtout qu'il faut géré tout les Rolback derrière, c-a-d, en cas d'echec d'insertion dans la BDD à la ligne x faut faire des delete sur toute les lignes d'avant (x- 1).

J'ai vu quand peu faire des prepareStatement avec des executeBatch et j'espère que ça ira plus vite.
J'ai trouvé ça sur le net:
http://www.oracle.com/technology/pro...ily/jun07.html
http://fredericktang.wordpress.com/2...pdate-part-ii/

[OButterlin]

Pourquoi ne pas explorer ce que disait JulienDuSud.
Tu peux très bien passer la commande

Code :

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

1
2
 
insert into ... select * ...

L'histoire des threads me parait tirée par les cheveux... et totalement irréaliste (ou alors avec un verrouillage de la table, bonjour l'angoisse ! )

[Julien Bodin]

J'ai eu un problème similaire, et du coup j'ai généré une requête en utilisant les StringBuilder pour avoir quelque chose comme ça :

Code :

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

INSERT INTO MaTable VALUES (X, Y, Z), (Z, Y, X) [...]

Histoire de faire une seule requête gigantesque.
Pour 20 000 enregistrement je suis passé de 2 minutes à moins d'une seconde.

Ce qui prend du temps c'est l'envoi des requêtes à la BDD, même en batch ça prend du temps.

Par contre il y a une taille limite à chaque requête mais il est peut-être possible de changer ce paramètre.

Maintenant, s'il s'agit de déplacer des données d'une base vers une autre, le INSERT INTO ... SELECT [...] me parait bien plus indiqué (surtout pour 280 000 enregistrements).