Activer/Désactiver les index [Résolu]

oneagaindoguys · 11/01/2011, 11h11

Bonjour, ma problématique se trouve au niveau des performances lorsque je souhaite insérer des données en masse dans mon système. J'ai un programme qui lit un fichier texte et insère les lignes les unes à la suite des autres. Pour insérer une ligne, j'ai besoin de 1 seconde !! Sur ma relation, j'ai un trigger qui duplique les lignes dans une table similaire (elle utilise un moteur MyISAM qui ne gère pas les clés étrangères et non le moteur Inno DB). Ces deux relations sont indexés sur pas mal d'attributs.

Je cherche donc une alternative pour gagner en rapidité 40 000 lignes = 40 000 secondes soit plus de 11h !

Je pense que désactiver les index serait déjà beaucoup mieux.

Merci d'avance

CinePhil · 11/01/2011, 11h47

Pour insérer des données en masse, il y a LOAD DATA INFILE au lieu de faire ça ligne par ligne !

Il vaut mieux insérer en masse dans une table temporaire puis, à l'aide d'une procédure SQL, éventuellement assitée d'un programme externe, répartir les données comme il faut dans les bonnes tables avec les bonnes valeurs suite aux éventuels bons calculs... bref, ce que doit faire ton trigger (encore que j'ai des crainte sur ton terme de "duplication" qui laisse penser à de la redondance de données, ce qui est totalement contraire au premier principe des bases de données !).

oneagaindoguys · 11/01/2011, 12h02

Merci pour ta réponse !

Il faut savoir que le fichier est un fichier excel, je vais voir si je peux le transformer en fichier texte puis l'insérer avec LOAD DATA INFILE. Sachant que je n'insère les valeurs dans une seule table.
La lecture est instantannée c'est l'écriture qui est longue.
Concernant la redondance, tu as raison, cette table est clonée. A chaque INSERT, UPDATE et DELETE, les deux tables sont affectées. J'ai bien conscience que cette solution n'enchante personne, moi le dernier mais il me faut gérer des clés étrangères et des index fulltext sur cette Table...

CinePhil · 11/01/2011, 12h09

Il faut enregistrer ton fichier Excel en format .csv.

oneagaindoguys · 11/01/2011, 12h20

Difficile d'imposer ça à un utilisateur à moins que le programme ne s'en charge.

ced · 11/01/2011, 17h13

Citation:

Envoyé par oneagaindoguys

Difficile d'imposer ça à un utilisateur à moins que le programme ne s'en charge.

Alors là, bon courage...
Je ne sais pas dans quel langage est le programme, mais pour avoir ouvert des fichiers Excel en Java et accédé aux données qu'ils contenaient, il y a quelques années de ça, ce n'est pas forcément infaisable, mais c'est fastidieux...

ced

oneagaindoguys · 11/01/2011, 17h25

Le problème ne provient pas du programme car en désactivant l'écriture dans la base (en générant la requete, et en l'imprimant) le travail est ultra rapide c'est le temps pris le SGBD d'insérer, de dupliquer et d'indexer qui est lourd.

The zxeno prophet · 11/01/2011, 19h44

J'ai moi aussi des problématique de ce genre au travail, on part souvent de base de donnée en fichier plat que l'on veut charger dans un SGBD classique. Ce qu'on fait en général c'est détourner less outil de BI/d'ETL, le premier exemple qui me vient en tête c'est le projet sur lequel je bosses, je charge depuis une base Access avec de la transformation de donnée lourde plus de 100000 lignes en moins de 5min

J'ai vu qu'il y avait des solutions open source si tu n'a pas de budget, mais les outils ETL sont vraiment fait pour ça (bon à la base plus pour faire des stats mais l'idée est la même)

Page wikipédia sur les ETL en fr
Version english plus fournis

Edit: Du coup petit ajout, la plupart des outils ETL charge nativement les excels

oneagaindoguys · 11/01/2011, 21h02

En effet, ça pourrait faire l'affaire. Tu utilise lequel ? Et comment je peux m'en servir ? Le plus important c'est qu'il faut que ce soit transparent pour l'utilisateur.

The zxeno prophet · 12/01/2011, 09h06

J'utilises MyReport de ReportOne, qui reste relativement simple à utiliser et pas cher. Question transparence ça vas l'être pas de soucis, si le fichier est toujours au même endroit, par contre si tu veux pouvoir récupérer les données il faudra recharger régulièrement les données, donc ta seulle table qui as des triggers pour dupliquer les données seras remplis tout le temps et devras être vidée régulièrement aussi

oneagaindoguys · 12/01/2011, 11h18

J'ai fini par trouver une autre solution qui est aussi pas mal, j'ai fais un peu de ménage sur le serveur et ça va beaucoup mieux !!

The zxeno prophet · 12/01/2011, 12h39

Citation:

Envoyé par oneagaindoguys

J'ai fini par trouver une autre solution qui est aussi pas mal, j'ai fais un peu de ménage sur le serveur et ça va beaucoup mieux !!

C'est quoi du coup, comme je fais pas mal de transfères de données de base à base dans le genre, ça m'intéresse

11/01/2011, 11h11	#1
oneagaindoguys Membre éprouvé Ingénieur développement logiciels Inscription : avril 2009 Messages : 501 Détails du profil Informations personnelles : Sexe : Localisation : France, Sarthe (Pays de la Loire) Informations professionnelles : Activité : Ingénieur développement logiciels Secteur : Service public Informations forums : Inscription : avril 2009 Messages : 501 Points : 490 Points : 490	Activer/Désactiver les index Bonjour, ma problématique se trouve au niveau des performances lorsque je souhaite insérer des données en masse dans mon système. J'ai un programme qui lit un fichier texte et insère les lignes les unes à la suite des autres. Pour insérer une ligne, j'ai besoin de 1 seconde !! Sur ma relation, j'ai un trigger qui duplique les lignes dans une table similaire (elle utilise un moteur MyISAM qui ne gère pas les clés étrangères et non le moteur Inno DB). Ces deux relations sont indexés sur pas mal d'attributs. Je cherche donc une alternative pour gagner en rapidité 40 000 lignes = 40 000 secondes soit plus de 11h ! Je pense que désactiver les index serait déjà beaucoup mieux. Merci d'avance
	00

11/01/2011, 11h47	#2
CinePhil Modérateur Philippe Leménager Ingénieur d'études en informatique Inscription : août 2006 Messages : 10 985 Détails du profil Informations personnelles : Nom : Philippe Leménager Âge : 48 Localisation : France, Haute Garonne (Midi Pyrénées) Informations professionnelles : Activité : Ingénieur d'études en informatique Secteur : Enseignement Informations forums : Inscription : août 2006 Messages : 10 985 Points : 18 232 Points : 18 232	Pour insérer des données en masse, il y a LOAD DATA INFILE au lieu de faire ça ligne par ligne ! Il vaut mieux insérer en masse dans une table temporaire puis, à l'aide d'une procédure SQL, éventuellement assitée d'un programme externe, répartir les données comme il faut dans les bonnes tables avec les bonnes valeurs suite aux éventuels bons calculs... bref, ce que doit faire ton trigger (encore que j'ai des crainte sur ton terme de "duplication" qui laisse penser à de la redondance de données, ce qui est totalement contraire au premier principe des bases de données !). __________________ Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework... « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau) À la maison comme au bureau, j'utilise Mandriva Linux ou Mageïa ! Soutenons l'industrie logicielle française ! Linuxiens, comptez-vous !
	00

11/01/2011, 12h09	#4
CinePhil Modérateur Philippe Leménager Ingénieur d'études en informatique Inscription : août 2006 Messages : 10 985 Détails du profil Informations personnelles : Nom : Philippe Leménager Âge : 48 Localisation : France, Haute Garonne (Midi Pyrénées) Informations professionnelles : Activité : Ingénieur d'études en informatique Secteur : Enseignement Informations forums : Inscription : août 2006 Messages : 10 985 Points : 18 232 Points : 18 232	Il faut enregistrer ton fichier Excel en format .csv. __________________ Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework... « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau) À la maison comme au bureau, j'utilise Mandriva Linux ou Mageïa ! Soutenons l'industrie logicielle française ! Linuxiens, comptez-vous !
	00

11/01/2011, 12h02	#3
oneagaindoguys Membre éprouvé Ingénieur développement logiciels Inscription : avril 2009 Messages : 501 Détails du profil Informations personnelles : Sexe : Localisation : France, Sarthe (Pays de la Loire) Informations professionnelles : Activité : Ingénieur développement logiciels Secteur : Service public Informations forums : Inscription : avril 2009 Messages : 501 Points : 490 Points : 490	Merci pour ta réponse ! Il faut savoir que le fichier est un fichier excel, je vais voir si je peux le transformer en fichier texte puis l'insérer avec LOAD DATA INFILE. Sachant que je n'insère les valeurs dans une seule table. La lecture est instantannée c'est l'écriture qui est longue. Concernant la redondance, tu as raison, cette table est clonée. A chaque INSERT, UPDATE et DELETE, les deux tables sont affectées. J'ai bien conscience que cette solution n'enchante personne, moi le dernier mais il me faut gérer des clés étrangères et des index fulltext sur cette Table...
	00

11/01/2011, 12h20	#5
oneagaindoguys Membre éprouvé Ingénieur développement logiciels Inscription : avril 2009 Messages : 501 Détails du profil Informations personnelles : Sexe : Localisation : France, Sarthe (Pays de la Loire) Informations professionnelles : Activité : Ingénieur développement logiciels Secteur : Service public Informations forums : Inscription : avril 2009 Messages : 501 Points : 490 Points : 490	Difficile d'imposer ça à un utilisateur à moins que le programme ne s'en charge.
	00

11/01/2011, 17h25	#7
oneagaindoguys Membre éprouvé Ingénieur développement logiciels Inscription : avril 2009 Messages : 501 Détails du profil Informations personnelles : Sexe : Localisation : France, Sarthe (Pays de la Loire) Informations professionnelles : Activité : Ingénieur développement logiciels Secteur : Service public Informations forums : Inscription : avril 2009 Messages : 501 Points : 490 Points : 490	Le problème ne provient pas du programme car en désactivant l'écriture dans la base (en générant la requete, et en l'imprimant) le travail est ultra rapide c'est le temps pris le SGBD d'insérer, de dupliquer et d'indexer qui est lourd.
	00

11/01/2011, 19h44	#8
The zxeno prophet Membre du Club Inscription : octobre 2009 Messages : 59 Détails du profil Informations forums : Inscription : octobre 2009 Messages : 59 Points : 69 Points : 69	J'ai moi aussi des problématique de ce genre au travail, on part souvent de base de donnée en fichier plat que l'on veut charger dans un SGBD classique. Ce qu'on fait en général c'est détourner less outil de BI/d'ETL, le premier exemple qui me vient en tête c'est le projet sur lequel je bosses, je charge depuis une base Access avec de la transformation de donnée lourde plus de 100000 lignes en moins de 5min J'ai vu qu'il y avait des solutions open source si tu n'a pas de budget, mais les outils ETL sont vraiment fait pour ça (bon à la base plus pour faire des stats mais l'idée est la même) Page wikipédia sur les ETL en fr Version english plus fournis Edit: Du coup petit ajout, la plupart des outils ETL charge nativement les excels
	00

11/01/2011, 21h02	#9
oneagaindoguys Membre éprouvé Ingénieur développement logiciels Inscription : avril 2009 Messages : 501 Détails du profil Informations personnelles : Sexe : Localisation : France, Sarthe (Pays de la Loire) Informations professionnelles : Activité : Ingénieur développement logiciels Secteur : Service public Informations forums : Inscription : avril 2009 Messages : 501 Points : 490 Points : 490	En effet, ça pourrait faire l'affaire. Tu utilise lequel ? Et comment je peux m'en servir ? Le plus important c'est qu'il faut que ce soit transparent pour l'utilisateur.
	00

12/01/2011, 09h06	#10
The zxeno prophet Membre du Club Inscription : octobre 2009 Messages : 59 Détails du profil Informations forums : Inscription : octobre 2009 Messages : 59 Points : 69 Points : 69	J'utilises MyReport de ReportOne, qui reste relativement simple à utiliser et pas cher. Question transparence ça vas l'être pas de soucis, si le fichier est toujours au même endroit, par contre si tu veux pouvoir récupérer les données il faudra recharger régulièrement les données, donc ta seulle table qui as des triggers pour dupliquer les données seras remplis tout le temps et devras être vidée régulièrement aussi
	00

12/01/2011, 11h18	#11
oneagaindoguys Membre éprouvé Ingénieur développement logiciels Inscription : avril 2009 Messages : 501 Détails du profil Informations personnelles : Sexe : Localisation : France, Sarthe (Pays de la Loire) Informations professionnelles : Activité : Ingénieur développement logiciels Secteur : Service public Informations forums : Inscription : avril 2009 Messages : 501 Points : 490 Points : 490	J'ai fini par trouver une autre solution qui est aussi pas mal, j'ai fais un peu de ménage sur le serveur et ça va beaucoup mieux !!
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email