Optimisation de performances sur une requête SQLite
Salut,
Je voudrais des idées pour améliorer les performances de mon app lors de la requête principale SQLite qui s'effectue quand je change de filtre.
Je vous joins pour commencer une capture d'Instruments. On voit les pics en haut correspondants à ces changements de filtre (chaque fois que la requête est exécutée et que l'affichage est modifié). Je vous ai déroulé les méthodes à partir du changement de filtre.
J'ai du mal à voir si c'est l'exécution de la requête elle-même qui est longue ou le parcours de la liste des résultats dans sa boucle avec [dbRows next].
J'ai testé sur un iPhone 4 pour avoir les performances les plus médiocres.
Connectez-vous ou Inscrivez-vous pour répondre.
Réponses
J'ai fait 2 tests tout bêtes.
Dans le premier, je commente le contenu de la boucle en laissant l'itération travailler. C'est un peu mieux.
On ne voit pas le temps que prend le executeQuery.
On voit que le "next" (sqlite3_step) prend 1,8 s sur 4,3 s que dure ton changement de filtre.
Il faudrait voir l'autre partie parce que tu vas pas pouvoir optimiser la partie sqlite3 à moins de modifier ta requête, les 2,5 secondes manquantes.
T'es obligé de faire un alloc de "found" à chaque itération ? et les NSNumber c'est obligatoire ?
1.En fait je suis sûr que la requête n'est pas le problème. C'est l'itération.
2.Attention quand même, les secondes sont cumulées sur plusieurs appels de la méthode. Dans la réalité, c'est environ 1 seconde de délai.
Il me faut créer un objet found pour chaque résultat.
Oui, j'y tiens.
Par contre, je peux un peu optimiser en appelant directement strForIdx et IntForIdx à la place de ObjForIdx qui teste le type.
Nouvelle capture de Instruments.
Après d'autres tests, bien que Instruments montre que c'est sqlite3_step qui est lent, c'est bien la complexité de la requête SQLite ET/OU des manques d'index (foreign) sur des tables qui semble être la cause.
Un bête SELECT * FROM table qui renvoie le même nombre d'enregistrements a pris 4ms au lieu de 200ms.
Donc je vais voir ce que je peux optimiser de ce côté-là .
https://www.google.fr/#q=sqlite3_step+slow
Donc si dans ta requête tu as par exemple un champ avec des données binaires volumineuses, la requête en elle-même peut être rapide (car les critères de ta requête sont sur un champ indexé, genre "WHERE id<50" par exemple), mais à chaque fois que tu demandes le prochain résultat, il va devoir récupérer les données binaires de la base et les copier dans un void* ou un NSData.
Du coup si, la requête peut avoir une incidence, si tu demandes trop de champs volumineux à binder par exemple.
Une autre raison que j'imagine pourrait expliquer la durée de "next" c'est côté FMDatabase, peut-être que ce que FMDB fait sous le capot pour encapsuler tout le sqlite est chronophage (j'avoue j'ai pas étudié ton output Instruments et je n'ai pas regardé le code de FMDB), en particulier, dans le cas d'un champ d'un certain type ils ont fait un truc pas optimisé qui prend du temps sous le capot ?
D'après toutes les discussions que j'ai pu lire (et je suis allé voir le code aussi), FMDB n'y est pour rien dans les mauvaises performances.
C'est encore une autre piste.
Mais je demande beaucoup de champs (70 ?) mais ils ne sont pas volumineux du tout.
Le [FMDatabase executeQuery] appelle sqlite3_prepare_V2 qui ne fait que "compiler" ta requête dans une structure interne à l'API SQlite.
Le [FMDatabase next] appelle sqlite3_step qui réalise le travail. D'ailleurs, j'imagine que le premier step doit être un peu plus lent que les suivants.
C'est donc bien ta requête qui prend du temps (cette fois on est d'accord
.
Cependant d'après Instrument, elle ne prend que la moitié du temps de ta fonction filterHasChanged, donc tu dois pouvoir optimiser autre chose que ta requête dans cette fonction. Je pense surtout à des allocations d'objets ou des mutable array.
Il faudrait que tu refasses une capture en repliant le sqlite3_step (sur lequel on ne peut agir à moins de modifier la requête) pour qu'on puisse voir le reste.
- si ton premier "next" prend *beaucoup* plus de temps que les autres, et que les suivants sont quasi-insignifiants (dans ce cas je pense qu'on peut supposer que c'est quelquechose comme ta clause WHERE ou autre qui est longue à évaluer pour sélectionner les bonnes lignes de ta base, une fois qu'il a sélectionné quelles lignes de la base sont dans ton FMResultSet et qu'il ictère dessus c'est rapide)
- si ton premier "next" ne prend qu'un peu plus de temps que les autres "next", mais que finalement même les autres "next" semble quand même pas si insignifiants que ça, c'est que le binding des champs lui aussi prend du temps, soit parce que tu en as beaucoup, soit parce que tu as des champs qui sont longs à binder (string, binary data, ...)
Essaye de remplacer un "SELECT *" par un "SELECT champ1, champ2, champ3, ..." ou vice-versa peut-être ?
Apparemment c'est l'affichage de mes cells qui prend aussi un peu de temps, malgré l'utilisation du reuseIdentifier. (mais je n'ai pas scrollé dans cette capture)
Si je prends tous les champs de Wines (30 environ) avec SELECT * FROM wines, je tombe à 4ms sur la boucle (avec le même nombre de records bien sûr). Donc ça doit être les nombreux INNER JOIN ou des WHERE le problème.
Si il me faut rajouter des INDEX, c'est le bordel en SQLite, il faut recréer les tables.
Par contre, je n'ai pas d'idée sur comment analyser la vitesse du premier next() ?
T'es sûr que t'as bien mis des index sur toutes tes Primary Keys mais aussi tes Foreign Keys utilisées par les JOINs ?
oui
non, un oubli à la création... (pas oublié en MySQL sur le serveur bizarrement)
Et du coup, c'est le bordel pour ajouter des foreign keys en SQLite, ça n'a pas la souplesse de MySQL.
Faut reconstruire les tables.
Si tu peux le seul truc c'est de bien mettre la ligne "[db closeOpenResultSets];" pour être sur que la base soit dispo pour créer l'index je le fait sur mon application et quand j'ouvre la base de données l'index apparait bien.
Si je suis parti là -dessus.
Je me suis mélangé les pieds avec les foreign keys ce matin...
Je vous tiens au courant sur les perfs dans pas longtemps.
Bon, je viens de créer tous mes index et ça ne change rien à l'exécution de ma requête.
Je suis déçu, j'y ai cru dur comme fer.
Pourtant j'ai lu que les index sur les jointures et les where étaient très bénéfiques en terme de performance.
Qu'est-ce que tu utilises pour créer/gérer les BDD SQLite ? Moi, j'ai découvert Base http://menial.co.uk/base/, qui me convient, Je l'utilise aussi pour vérifier les fichiers de stockages Core Data
J'utilise SQLite Pro que je trouve très bien:
https://www.sqlitepro.com
Pour que ce soit plus parlant, je viens de faire un dernier test avec un client à "grosse cave" (500 vins, 2500 bouteilles).
La capture ci-dessous montre la différence entre l'iPhone 4 et l'iPhone 6 (3 changements de filtre en tout et donc 3 requêtes SQL).
Je pense par exemple à AUTO_VACUUM ou INCREMENTAL_VACUUM, etc...
---
Aussi, es-tu sûr que tu as bien créé ton index ? Est-ce que le CREATE INDEX n'aurait pas pu échouer, ce qui fait qu'en fait tu n'as pas créé l'index comme tu le penses ?
J'ai un message d'erreur en théorie si l'index n'est pas créé (je l'ai eu la première fois car le nom de la table n'était pas le bon).
PaR exemple:
[[[Database sharedDatabase] database] executeUpdate:@CREATE INDEX IF NOT EXISTS db_user.contact_id ON wines (contact_id)];
Ensuite, j'ai créé les index avec l'application citée plus haut pour partir sur une base vierge... et j'ai lancé une synchro dessus pour la remplir. Le résultat est le même.
Je vais regarder les pragma, car même si les index sont créés, est-ce qu'ils ont été "remplis" ?
Faudrait que j'analyse la taille du fichier avec et sans index.
Je confirme que:
- les index ont bien été créés et qu'ils n'apportent rien (d'après mes lectures SQLite, si on index "une clé étrangère" qui se rapporte à une clé primaire d'une autre table, ça n'apporte rien à la vitesse de SQLite car elle utilise déjà la clé primaire de l'autre table pour les jointures et les where)
- l'ancienne version de mon app est 2 ou 3 fois plus rapide (je vais enquêter là -dessus, la requête n'a pratiquement pas changé, c'est ce qui m'énerve le plus)
Un EXPLAIN QUERY PLAN avec tes deux requêtes pourrait peut être d'aider à comprendre ce qui à changé.
Oui je l'ai aussi essayé mais ça ne m'a pas appris grand-chose, tout a l'air logique sur l'ordre des requêtes.
Et ce qui a changé, c'est que j'ai quelques jointures en plus (entre autres).
Dans ta dernière capture, l'affichage des cellules (methode cellForRowAtIndexPath: ...) de la tableview prend du temps.
Mais on ne voit pas trop combien de cellules ont été affichées.
Est-ce le fait de selectionner une cellule par code qui fait défiler la tableview ?
4 à 10 cellules affichées, pas de scroll, et si sélection d'une cellule, l'animated est à NO, donc pas de scroll.