Base de données lenteFermé

Question

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

yg_be · Answer

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 select 1?

thomas87b · Answer

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.
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

dachiasse · Answer

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.

thomas87b · Answer

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.

thomas87b · Answer

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.

thomas87b · Answer

Voici les tables. J'ai fait quelques modifications depuis ce topic :
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.

thomas87b · Answer

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.

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.

thomas87b · Answer

EXPLAIN QUERY PLAN me dit juste SCAN TABLE sentencedans tous les cas, les lents comme les rapides

thomas87b · Answer

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.

thomas87b · Answer

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.

thomas87b · Answer

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 :
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.

thomas87b · Answer

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 
 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.

thomas87b · Answer

J'ai un fichier .db, mais il est trop gros pour le transférer ici

thomas87b · Answer

Non. Je n'ai aucun contrôle sur ces fichiers

Base de données lente

14 réponses

Discussions similaires

Newsletters