Base de données lente
thomas87b
Messages postés
275
Date d'inscription
Statut
Membre
Dernière intervention
-
thomas87b Messages postés 275 Date d'inscription Statut Membre Dernière intervention -
thomas87b Messages postés 275 Date d'inscription Statut Membre Dernière intervention -
Bonjour,
Je suis en train de développer une application avec une base de données assez grosse (plusieurs dizaines de Mo). L'accès aux données est beaucoup trop lent.
En cherchant sur internet, j'ai trouvé plusieurs solutions :
1) Créer des index sur les colonnes.
De ce que j'ai lu, ça permettrait d'accélérer les select, mais ralentirait les insert. Dans mon cas, les deux sont trop lents. La création/suppression d'index, est-elle une opération lente ? J'imagine que supprimer un index avant un insert et le recréer après n'est pas rentable ? L'impact positif sur le select, est-il intéressant comparé à l'impact négatif sur le insert ?
2) Utiliser une base de donnée alternative.
On trouve plusieurs noms en cherchant, mais jamais de données chiffrées. Certains disent que certaines sont plus rapides, mais toujours en donnant une liste de noms, sans dire lesquelles sont les plus rapides. Avez-vous expérimenté d'autres bases de données et avez-vous eu un changement significatif sur la vitesse d'accès aux données sur certaines d'entre elles ?
Merci
Je suis en train de développer une application avec une base de données assez grosse (plusieurs dizaines de Mo). L'accès aux données est beaucoup trop lent.
En cherchant sur internet, j'ai trouvé plusieurs solutions :
1) Créer des index sur les colonnes.
De ce que j'ai lu, ça permettrait d'accélérer les select, mais ralentirait les insert. Dans mon cas, les deux sont trop lents. La création/suppression d'index, est-elle une opération lente ? J'imagine que supprimer un index avant un insert et le recréer après n'est pas rentable ? L'impact positif sur le select, est-il intéressant comparé à l'impact négatif sur le insert ?
2) Utiliser une base de donnée alternative.
On trouve plusieurs noms en cherchant, mais jamais de données chiffrées. Certains disent que certaines sont plus rapides, mais toujours en donnant une liste de noms, sans dire lesquelles sont les plus rapides. Avez-vous expérimenté d'autres bases de données et avez-vous eu un changement significatif sur la vitesse d'accès aux données sur certaines d'entre elles ?
Merci
A voir également:
- Base de données lente
- Fuite données maif - Guide
- Base de registre - Guide
- Supprimer les données de navigation - Guide
- Tnt base de données vide - Forum TNT / Satellite / Réception
- Formules mathématiques de base - Télécharger - Études & Formations
14 réponses
yg_be
Messages postés
23541
Date d'inscription
Statut
Contributeur
Dernière intervention
Ambassadeur
1 584
bonjour,
il n'y a pas de réponse toute faite à tes questions, cela dépend, évidemment, du contexte.
tout l'art est d'ajuster des choix en fonction du contexte.
comme tu ne nous décris strictement rien d'utile à propos de de ton contexte, difficile de faire une suggestion.
si tu fais un insert, cela n'a évidement pas de sens de supprimer/recréer l'index.
pour balancer l'impact positif sur le select et l'impact négatif sur le insert, il est souvent utile de savoir combien d'insert, combien de select, et, bien sûr, le type de select.
comme tu écris que les deux sont trop lents, le problème est peut-être ailleurs.
as-tu testé différentes options, pour mesurer l'impact de chacune?
as-tu essayé un select très simple, genre
il n'y a pas de réponse toute faite à tes questions, cela dépend, évidemment, du contexte.
tout l'art est d'ajuster des choix en fonction du contexte.
comme tu ne nous décris strictement rien d'utile à propos de de ton contexte, difficile de faire une suggestion.
si tu fais un insert, cela n'a évidement pas de sens de supprimer/recréer l'index.
pour balancer l'impact positif sur le select et l'impact négatif sur le insert, il est souvent utile de savoir combien d'insert, combien de select, et, bien sûr, le type de select.
comme tu écris que les deux sont trop lents, le problème est peut-être ailleurs.
as-tu testé différentes options, pour mesurer l'impact de chacune?
as-tu essayé un select très simple, genre
select 1?
Je n'ai pas essayé les différentes options parce-que je voulais d'abord voir si je pouvais avoir des retours de gens qui auraient déjà expérimenté.
Le select 1 dure 25 secondes pour une table de 1885722 entrées. Une entrée contient deux ints dont un index et deux strings.
Les insert sont faits de façon occasionnelle, mais c'est la première chose qui est faite après avoir installé l'application, et sont donc importants pour la première impression. Actuellement, ils durent quelques minutes.
Les select sont faits très fréquemment pendant l'utilisation de l'application.
Je n'ai parlé que d'une table ici pour simplifier, mais il y a une deuxième table d'association qui contient deux colonnes d'ids de la première table.
En supprimant cette deuxième table des requêtes, ça me prends 2 secondes
pour faire un ORDER BY RANDOM() LIMIT 1 et entre 5 et 15 secondes pour faire un LIKE "%xxx%" LIMIT 50
Le select 1 dure 25 secondes pour une table de 1885722 entrées. Une entrée contient deux ints dont un index et deux strings.
final Cursor cursor = mDb.rawQuery("select 1 from "+TABLE_NAME , new String[]{});
while (cursor.moveToNext()) {}
Les insert sont faits de façon occasionnelle, mais c'est la première chose qui est faite après avoir installé l'application, et sont donc importants pour la première impression. Actuellement, ils durent quelques minutes.
Les select sont faits très fréquemment pendant l'utilisation de l'application.
Je n'ai parlé que d'une table ici pour simplifier, mais il y a une deuxième table d'association qui contient deux colonnes d'ids de la première table.
En supprimant cette deuxième table des requêtes, ça me prends 2 secondes
pour faire un ORDER BY RANDOM() LIMIT 1 et entre 5 et 15 secondes pour faire un LIKE "%xxx%" LIMIT 50
Salut,
Je ne saurais t’aider seul. Mais, si tu ne connais pas Sqlpro, je te file deux liens qui pourraient t’intéresser : https://sqlpro.developpez.com/
https://blog.developpez.com/sqlpro/
Si tu n’y as pas accès, il faudra peut-être te créer un compte sur dvp.
Je ne saurais t’aider seul. Mais, si tu ne connais pas Sqlpro, je te file deux liens qui pourraient t’intéresser : https://sqlpro.developpez.com/
https://blog.developpez.com/sqlpro/
Si tu n’y as pas accès, il faudra peut-être te créer un compte sur dvp.
Bon alors pour ce qui est de la requête random (ORDER BY RANDOM() LIMIT 1) qui durait deux secondes, j'ai réussi à la baisser à 100ms en gérant le random moi-même côté java plutôt que d'utiliser celui de SQLite. Pour les autres select, je pense les charger un par un et les afficher un par un en enchainant les LIMIT 1 OFFSET x, ce qui permettra de commencer à les afficher sans faire attendre l'utilisateur (avec un LIMIT 1, il se charge en 150ms). Je ferai ça un autre jour.
tu nous a informé que tu utilisais SQLLite, c'est utile à savoir.
tu nous dit peu de chose sur la structure de ta table.
comme toutes tes requêtes étaient lentes, ma suggestion, c'était de tester
chaque INSERT dure quelques minutes? si tu fais un grand nombre d'INSERT pendant une période où tu fais peu de SELECT, il est peut-être utile de supprimer temporairement l'index.
ou d'essayer de faire des INSERT multi-enregistrements.
au moment des SELECT, les index sont utiles, ou pas, en fonction de la requête. je n'ai vu aucune requête où un index est utile.
pourquoi as-tu un index dans ta table?
pourquoi utilises-tu LIKE?
tu nous dit peu de chose sur la structure de ta table.
comme toutes tes requêtes étaient lentes, ma suggestion, c'était de tester
select 1, sans clause FROM.
chaque INSERT dure quelques minutes? si tu fais un grand nombre d'INSERT pendant une période où tu fais peu de SELECT, il est peut-être utile de supprimer temporairement l'index.
ou d'essayer de faire des INSERT multi-enregistrements.
au moment des SELECT, les index sont utiles, ou pas, en fonction de la requête. je n'ai vu aucune requête où un index est utile.
pourquoi as-tu un index dans ta table?
pourquoi utilises-tu LIKE?
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
L'ensemble des INSERT dure quelques minutes.
Tous les tutos bases de données disent d'ajouter une colonne id primary key auto increment. C'était déjà le cas à l'époque quand j'ai appris le MySQL et récemment quand j'ai appris le développement Android. Je le fais donc par réflexe.
Ma première table a un id fourni. Je l'ai donc pas mis en auto increment. Il sert à faire des recherches en utilisant la deuxième table qui continent des paires d'ids.
Par contre, sur la deuxième table, j'ai ajouté un id auto increment par reflèxe, mais je ne l'utilise jamais.
La base de données contient des phrases. La requête like permet de chercher une phrase en tapant un mot ou une partie de mot.
De mémoire (je n'ai pas le projet sous la main) :
Première table :
Id integer primary key, version integer, language text, phrase text
Deuxième table :
Id integer primary key auto increment, version integer, phrase1 integer, phrase2 integer
Les champs version servent uniquement à gérer les mises à jour de la table et sont utilisés dans des delete.
Les phrase1 et phrase2 de la deuxième table référencent des ids de la première table. Je fais des selects où je cherche les phrases liées à une autre donnée.
Tous les tutos bases de données disent d'ajouter une colonne id primary key auto increment. C'était déjà le cas à l'époque quand j'ai appris le MySQL et récemment quand j'ai appris le développement Android. Je le fais donc par réflexe.
Ma première table a un id fourni. Je l'ai donc pas mis en auto increment. Il sert à faire des recherches en utilisant la deuxième table qui continent des paires d'ids.
Par contre, sur la deuxième table, j'ai ajouté un id auto increment par reflèxe, mais je ne l'utilise jamais.
La base de données contient des phrases. La requête like permet de chercher une phrase en tapant un mot ou une partie de mot.
De mémoire (je n'ai pas le projet sous la main) :
Première table :
Id integer primary key, version integer, language text, phrase text
Deuxième table :
Id integer primary key auto increment, version integer, phrase1 integer, phrase2 integer
Les champs version servent uniquement à gérer les mises à jour de la table et sont utilisés dans des delete.
Les phrase1 et phrase2 de la deuxième table référencent des ids de la première table. Je fais des selects où je cherche les phrases liées à une autre donnée.
Pour les INSERT, j'insère d'abord les données de la première table, puis de la deuxième pour une langue donnée, puis je recommence avec d'autres langues. Ça peut aller de quelques entrées pour les langues peu connues à plus d'un million pour l'anglais. Les entrées viennent de fichiers csv. Les INSERT sont appelés avec un update en cas de conflit. En écrivant, je me rends compte qu'il y a peut-être un soucis au niveau de la deuxième table. Comme l'index est en auto increment, il n'y a jamais de conflit détecté et les données sont insérées à nouveau. Je vérifierai ça.
Pour les select, on cherche les entrées dans la première table avec un like %xxx% et éventuellement une langue spécifié, puis pour chacune d'elles, on cherche les entrées associées à l'aide de la deuxième table. Ça fait donc une nouvelle requête pour chaque entrée retournée par la première. Je pourrai poster des exemples de requêtes plus précis la prochaine fois que j'ouvrai le projet
Pour les select, on cherche les entrées dans la première table avec un like %xxx% et éventuellement une langue spécifié, puis pour chacune d'elles, on cherche les entrées associées à l'aide de la deuxième table. Ça fait donc une nouvelle requête pour chaque entrée retournée par la première. Je pourrai poster des exemples de requêtes plus précis la prochaine fois que j'ouvrai le projet
un INSERT par enregistrement?
à tout hasard: https://docs.microsoft.com/fr-ch/dotnet/standard/data/sqlite/bulk-insert
à tout hasard: https://docs.microsoft.com/fr-ch/dotnet/standard/data/sqlite/bulk-insert
Voici les tables. J'ai fait quelques modifications depuis ce topic :
Et voici un exemple de requêtes :
Après cette requête, on appelle la suivante avec l'id récupéré :
J'ai ajouté le limit 1 offset pour commencer à afficher sans attendre qu'ils soient tous chargés. Avant, j'en chargeais 50 d'un coup. Ici la première requête retourne une ou zéro réponse, et la deuxième dépasse rarement les 10 réponses. Les deux requêtes ensemble ont une durée assez aléatoire de 10ms à quelques secondes.
La recherche peut se faire par mot ou partie de mot. Si quelqu'un cherche maison, c'est bien d'avoir aussi maisons. Une table avec tous les mots, ça voudrait dire une table qui contient tous les mots existant dans toutes les langues et les signes de ponctuation ? Ça me parait assez compliqué à mettre en place, surtout pour les langues comme le japonais ou le chinois qui n'utilisent pas d'espace.
Pour le INSERT, j'ai regardé ton lien, et effectivement, j'ai pu diviser le temps par 10 en les insérant par paquets en utilisant des transactions. Merci beaucoup.
CREATE TABLE sentence (id INTEGER PRIMARY KEY, version INTEGER, language TEXT, sentence TEXT); CREATE TABLE link (version INTEGER, version INTEGER, translation INTEGER, PRIMARY KEY (sentence,translation));
Et voici un exemple de requêtes :
select _id,language,sentence from sentence where language="fra" and sentence like "%maison%" LIMIT 1 OFFSET 3
Après cette requête, on appelle la suivante avec l'id récupéré :
select sentence._id,language,sentence.sentence from sentence,link where link.sentence=1373563 and sentence._id=link.translation
J'ai ajouté le limit 1 offset pour commencer à afficher sans attendre qu'ils soient tous chargés. Avant, j'en chargeais 50 d'un coup. Ici la première requête retourne une ou zéro réponse, et la deuxième dépasse rarement les 10 réponses. Les deux requêtes ensemble ont une durée assez aléatoire de 10ms à quelques secondes.
La recherche peut se faire par mot ou partie de mot. Si quelqu'un cherche maison, c'est bien d'avoir aussi maisons. Une table avec tous les mots, ça voudrait dire une table qui contient tous les mots existant dans toutes les langues et les signes de ponctuation ? Ça me parait assez compliqué à mettre en place, surtout pour les langues comme le japonais ou le chinois qui n'utilisent pas d'espace.
Pour le INSERT, j'ai regardé ton lien, et effectivement, j'ai pu diviser le temps par 10 en les insérant par paquets en utilisant des transactions. Merci beaucoup.
moi je mettrais langage comme nombre dans la table sentence, avec un index.
en rajoutant une table langage, qui fait la correspondance entre le nom et le n° de la langue, avec un index unique sur chacune des colonnes.
deux fois 'version' dans la table link?
Une table avec des mots, ça voudrait dire une table qui contient tous les mots, où une partie des mots, sur lesquels on peut faire une recherche. de façon à lier, à l'avance, ces mots avec les phrases.
je suppose que la seconde requête est quasi instantanée. et que la durée se la première dépend de la fréquence de présence des mots cherchés. pour tester, moi je chercherais un mot qui n'existe pas, vérifier si il répond plus ou moins vite en fonction de la fréquence de présence de la langue.
peut-être envisager d'avoir une table sentence par langue, si l'index sur la langue n'aide pas assez?
en rajoutant une table langage, qui fait la correspondance entre le nom et le n° de la langue, avec un index unique sur chacune des colonnes.
deux fois 'version' dans la table link?
Une table avec des mots, ça voudrait dire une table qui contient tous les mots, où une partie des mots, sur lesquels on peut faire une recherche. de façon à lier, à l'avance, ces mots avec les phrases.
je suppose que la seconde requête est quasi instantanée. et que la durée se la première dépend de la fréquence de présence des mots cherchés. pour tester, moi je chercherais un mot qui n'existe pas, vérifier si il répond plus ou moins vite en fonction de la fréquence de présence de la langue.
peut-être envisager d'avoir une table sentence par langue, si l'index sur la langue n'aide pas assez?
Je me suis trompé en changeant les variables par les noms des champs pour les poster ici. C'est une fois version et l'autre, c'est sentence.
Je vais voir si changer language en nombre change beaucoup. Comme la nouvelle table serait très petite, il vaut peut-être mieux la mettre dans un SharedPreference et accéder à la base de données directement avec le nombre.
Une table par langue, il faut voir si c'est vraiment intéressant. La langue n'est pas toujours spécifiée dans la requête. Ça impliquerait de faire une requête sur toutes les tables.
Les mots qui n'existent pas et les mots très courts (deux ou trois lettres), même fréquents sont très rapides (entre 2 et 10ms).
Par contre, pour les mots plus longs qui existent, certains sont rapides et d'autres sont lents (jusqu'à plusieurs secondes). Je n'ai pas réussi a trouver de corrélation entre la vitesse des requêtes et la fréquence ou la longueur des mots. J'ai l'impression que c'est aléatoire, mais fixe pour un mot donné.
Est-ce qu'il y a un moyen de savoir dans quel ordre les clauses where sont exécutées et de le changer ? Si le LIKE est plus long, il devrait être testé en dernier uniquement si les autres passent.
CREATE TABLE sentence (id INTEGER PRIMARY KEY, version INTEGER, language TEXT, sentence TEXT); CREATE TABLE link (version INTEGER, sentence INTEGER, translation INTEGER, PRIMARY KEY (sentence,translation));
Je vais voir si changer language en nombre change beaucoup. Comme la nouvelle table serait très petite, il vaut peut-être mieux la mettre dans un SharedPreference et accéder à la base de données directement avec le nombre.
Une table par langue, il faut voir si c'est vraiment intéressant. La langue n'est pas toujours spécifiée dans la requête. Ça impliquerait de faire une requête sur toutes les tables.
Les mots qui n'existent pas et les mots très courts (deux ou trois lettres), même fréquents sont très rapides (entre 2 et 10ms).
Par contre, pour les mots plus longs qui existent, certains sont rapides et d'autres sont lents (jusqu'à plusieurs secondes). Je n'ai pas réussi a trouver de corrélation entre la vitesse des requêtes et la fréquence ou la longueur des mots. J'ai l'impression que c'est aléatoire, mais fixe pour un mot donné.
Est-ce qu'il y a un moyen de savoir dans quel ordre les clauses where sont exécutées et de le changer ? Si le LIKE est plus long, il devrait être testé en dernier uniquement si les autres passent.
Avec l'index, j'ai SEARCH TABLE sentence USING INDEX index_language (language=?), mais la durée est toujours aussi longue.
Le second select dépasse rarement les 2ms, donc je m'en suis pas occupé.
J'ai pas encore essayé de remplacer la langue par un nombre.
Le second select dépasse rarement les 2ms, donc je m'en suis pas occupé.
J'ai pas encore essayé de remplacer la langue par un nombre.
J'ai réduit le temps d'accès presque de moitié en remplaçant
sentence LIKE %xxx%
par
instr(sentence, "xxx") <> 0
comme indiqué sur cette page (en anglais) : https://www.greytrix.com/blogs/sagex3/2016/02/08/alternate-to-like-keyword-of-sql/
L'inconvénient, c'est que c'est sensible à la casse, contrairement à LIKE.
J'ai aussi trouvé les tables virtuelles FTS5, qui m'ont l'air adaptées à ce que je fais (en anglais aussi) : https://www.sqlitetutorial.net/sqlite-full-text-search/
Je vais essayer pour voir ce que ça donne.
sentence LIKE %xxx%
par
instr(sentence, "xxx") <> 0
comme indiqué sur cette page (en anglais) : https://www.greytrix.com/blogs/sagex3/2016/02/08/alternate-to-like-keyword-of-sql/
L'inconvénient, c'est que c'est sensible à la casse, contrairement à LIKE.
J'ai aussi trouvé les tables virtuelles FTS5, qui m'ont l'air adaptées à ce que je fais (en anglais aussi) : https://www.sqlitetutorial.net/sqlite-full-text-search/
Je vais essayer pour voir ce que ça donne.
FTS5 n'étant pas proposé par défaut sur Android, je me suis pas embêté et j'ai essayé avec FTS4. Et c'est GÉNIAL !
Les requêtes qui duraient avant plusieurs secondes durent maintenant 2 ou 3ms. Les plus longues ne dépassent pas les 10ms. C'est vraiment un truc à connaître. Pour les requêtes sur des champs qui contiennent du texte plus long qu'un simple mot, c'est indispensable. Et encore, je n'utilise pas tout le potentiel. On peut faire des recherches plus complexes comme chercher si le texte contient deux mots proches l'un de l'autre.
Le seul inconvénient est que la base de données occupe plus d'espace (environ 30% de plus). Mais pour la rapidité de recherche que ça offre, ça les vaut largement.
https://www.sqlitetutorial.net/sqlite-full-text-search/
Les requêtes qui duraient avant plusieurs secondes durent maintenant 2 ou 3ms. Les plus longues ne dépassent pas les 10ms. C'est vraiment un truc à connaître. Pour les requêtes sur des champs qui contiennent du texte plus long qu'un simple mot, c'est indispensable. Et encore, je n'utilise pas tout le potentiel. On peut faire des recherches plus complexes comme chercher si le texte contient deux mots proches l'un de l'autre.
Le seul inconvénient est que la base de données occupe plus d'espace (environ 30% de plus). Mais pour la rapidité de recherche que ça offre, ça les vaut largement.
https://www.sqlitetutorial.net/sqlite-full-text-search/
Je me suis rendu compteque FTS était plus lent sur les recherches courtes (moins de trois lettres). J'adapte donc la requête en fonction de la taille du mot.
Pour les mots de moins de trois lettres, requête normale :
Pour les mots de plus de trois lettres, requête FTS :
J'ai encore les INSERT qui prennent du temps, mais ça, je sais pas si je pourrai faire grand chose. J'ai un grand nombre d'INSERT à faire d'un coup.
Pour les mots de moins de trois lettres, requête normale :
LIKE "%xxx%"
Pour les mots de plus de trois lettres, requête FTS :
MATCH "*xxx*"
J'ai encore les INSERT qui prennent du temps, mais ça, je sais pas si je pourrai faire grand chose. J'ai un grand nombre d'INSERT à faire d'un coup.
Les données sont récupérées d'un fichier csv compressé en tar.bz2.
En décompressant d'abord et en insérant après, la mémoire était saturée pour les plus gros fichiers. Donc je commence la décompression pour quelques milliers d'entrées, puis je les insère, puis je continue la décompression.
L'algorithme de décompression est une boucle while qui produit un ByteArray. Quand ce ByteArray atteint une taille limite, je crée une liste d'éléments jusqu'au dernier \n que je trouve et je garde les bytes restants quand je reprends la décompression.
Une fois que j'ai ma liste d'éléments, je les insère avec INSERT OR REPLACE. C'est cette étape qui prend du temps.
En gros, j'ai trois boucles : décompresser, convertir ByteArray en liste d'éléments, insérer les éléments dans la base de données. Je navigue entre les trois boucles jusqu'à finir le fichier.
En décompressant d'abord et en insérant après, la mémoire était saturée pour les plus gros fichiers. Donc je commence la décompression pour quelques milliers d'entrées, puis je les insère, puis je continue la décompression.
L'algorithme de décompression est une boucle while qui produit un ByteArray. Quand ce ByteArray atteint une taille limite, je crée une liste d'éléments jusqu'au dernier \n que je trouve et je garde les bytes restants quand je reprends la décompression.
Une fois que j'ai ma liste d'éléments, je les insère avec INSERT OR REPLACE. C'est cette étape qui prend du temps.
En gros, j'ai trois boucles : décompresser, convertir ByteArray en liste d'éléments, insérer les éléments dans la base de données. Je navigue entre les trois boucles jusqu'à finir le fichier.