Base de données lente

Signaler
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
-
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
-
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

14 réponses

Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712
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
?
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
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
Messages postés
199
Date d'inscription
samedi 12 septembre 2020
Statut
Membre
Dernière intervention
25 octobre 2020
11
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.
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
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.
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712
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
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?
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
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.
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712
reste à nous montrer à quoi ressemblent les requêtes INSERT et SELECT.
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64 >
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020

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
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712 >
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020

un INSERT par enregistrement?
à tout hasard: https://docs.microsoft.com/fr-ch/dotnet/standard/data/sqlite/bulk-insert
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712 >
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020

le LIKE doit être assez couteux. ce sont des recherches par mots? pourrait-on imaginer d'avoir une table avec tous les mots?
peut-être utile d'ajouter des index sur les champs integer de la seconde table qui servent dans un WHERE.
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
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.
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712
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?
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
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.
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712
je suis surpris que les mots qui n'existent pas sont très rapides dans un like...

EXPLAIN QUERY PLAN peut être utilisé pour comprendre comment la requête va être traitée.
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
EXPLAIN QUERY PLAN me dit juste SCAN TABLE sentencedans tous les cas, les lents comme les rapides
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712
même avec le premier select quand il y a un index sur le langage?
et idem second select?
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
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.
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
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.
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
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/
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712 >
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020

et l'INSERT reste raisonnable?
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712
peut-être:
instr(lower(sentence), "xxx") <> 0

ou
instr(lower(sentence), lower("Xyz")) <> 0
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
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.
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712
Peux-tu donner plus d'info sur comment/quand/pourquoi tu fais ces INSERT massifs? A partir de quoi?
Cela aidera peut-être à imaginer une alternative.
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
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.
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712
pas possible de récupérer les fichiers de la base de données?
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
J'ai un fichier .db, mais il est trop gros pour le transférer ici
Messages postés
12763
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 octobre 2020
712
je voulais dire: utiliser un fichier db au lieu de csv.
Messages postés
275
Date d'inscription
mardi 2 septembre 2008
Statut
Membre
Dernière intervention
23 octobre 2020
64
Non. Je n'ai aucun contrôle sur ces fichiers