SQLite augmenter énormément la vitesse en UPDATE et INSERT

Partagez votre expérience de PureBasic avec les autres utilisateurs.
Marc56
Messages : 2146
Inscription : sam. 08/févr./2014 15:19

SQLite augmenter énormément la vitesse en UPDATE et INSERT

Message par Marc56 »

Hello,

SQLite est une très bonne base de de données, mais comme on peut le constater les opération en UPDATE sont très longues.
Dans mon TreeNote 2 version SQL (en cours de développement), je met à jour tous les niveaux de l'arbre à chaque déplacement.
J'ai constaté plusieurs secondes pour mettre à jour simplement une dizaines d'enregistrements 8O (inutilisable)

Voici une solution trouvée sur StackOverflow
http://stackoverflow.com/questions/3820 ... low-update
Il faut envoyer ces deux directives

Code : Tout sélectionner

PRAGMA synchronize = OFF; 
PRAGMA journal_mode = MEMORY;
(version corrigée car l'auteur a fait une faute de frappe)

donc si vous avez une succession de requêtes en UPDATE, passez avant les updates, une première requête pragma.

Code : Tout sélectionner

DatabaseUpdate(#DB, "PRAGMA synchronize = OFF; PRAGMA journal_mode = MEMORY;")
; Puis les requêtes en update
ForEach ...
     DatabaseUpdate(#DB, "UPDATE ...
Next
Chez moi, la temps a été divisé par 7 :!:

Hope this help
:wink:

Cela dit, je n'ai pas fait l'essai de compiler toutes mes requêtes en une seule puis de l'envoyer.
Je créé toutes les requêtes dynamiquement et les envoient une par une,
c'est peut être là une mauvaise méthode ?

PS.
À propos de l'excellente méthode des requêtes préparées: est il possible de faire afficher (pour debug) le contenu de chaque variable adresse ' ? ' pour SQLite ou $1 pour PostgreSQL ?
(raison: je passe ma requête en paramètre à une procédure et j'aimerais voir ce qui est généré avant de l'exécuter)
Dernière modification par Marc56 le lun. 22/mai/2017 10:11, modifié 1 fois.
boby
Messages : 261
Inscription : jeu. 07/juin/2007 22:54

Re: SQLite augmenter énormément la vitesse en UPDATE

Message par boby »

Voila une astuce qui tombe à pic pour moi !

Je doit me bouffer d'énormes fichiers Excel pour faire des comparaison, je les stock donc dans une DB SQLite.
Je viens de tester ta méthode je passe (pour le bourrage de data dans la DB) de présque 10 min à tout juste 1 minutes.
On peut donc dire que ça fonctionne ;)

Merci à toi Marc56 !!!
Marc56
Messages : 2146
Inscription : sam. 08/févr./2014 15:19

Re: SQLite augmenter énormément la vitesse en UPDATE

Message par Marc56 »

Trouvé une autre méthode en lisant la FAQ (en cherchant autre-chose)

À part utiliser la directive, on peut faire une transaction (même si on n'en a pas besoin)
:arrow: cela permet d'écrire tous les inserts en une seule fois (la vérification disque ne se fait alors qu'à la fin)

En détail:
https://www.sqlite.org/faq.html#q19

:idea: C'est peut-être mieux, car commande SQL et donc non dépendant de la base de données.

(Pas testé pour l'instant)
:wink:
Marc56
Messages : 2146
Inscription : sam. 08/févr./2014 15:19

SQLite augmenter énormément la vitesse en UPDATE et INSERT

Message par Marc56 »

Un essai avec / sans transaction

Code : Tout sélectionner

UseSQLiteDatabase()

Filename$ = GetTemporaryDirectory() + "Test.sqlite"

If CreateFile(0, Filename$)
     Debug "Fichier de base de données créé"
     CloseFile(0)
EndIf

If OpenDatabase(0, Filename$, "", "")
     Debug "Connecté à PureBasic.sqlite"
     If DatabaseUpdate(0, "CREATE TABLE info (test VARCHAR(255));")
          Debug "Table créée"
     EndIf
EndIf

Procedure Fill_DataBase(Transact.c)
     Start = ElapsedMilliseconds()
     If Transact = 1
          DatabaseUpdate(0, "BEGIN")
     EndIf
     For i = 1 To 100
          SetDatabaseString(0, 0, FormatDate("%yyyy%mm%dd %hh:%ii%ss", Date()))
          If DatabaseUpdate(0, "INSERT INTO info VALUES (?);")
               ;Debug "INSERT"
          Else
               Debug "Erreur: " + DatabaseError()
          EndIf
     Next
     If Transact = 1
          DatabaseUpdate(0, "COMMIT")
     EndIf
     Debug "Écoulé: " + FormatNumber(ElapsedMilliseconds() - Start, 0, ".", " ") + " ms"
EndProcedure

Fill_DataBase(0)
Fill_DataBase(1)

CloseDatabase(0)
Debug "END"
Sur 100 enregistrements: 100 x + vite :!: en mode transaction

Code : Tout sélectionner

Écoulé: 9 838 ms
Écoulé: 104 ms
1000 INSERT: 1000 x+ :!:

Code : Tout sélectionner

Écoulé: 104 225 ms
Écoulé: 100 ms
Donc, si plusieurs INSERT, envoyez un BEGIN puis COMMIT, même si vous n'avez pas l'intention de revenir en arrière.

L'explication est dans la FAQ SQLite
https://www.sqlite.org/faq.html#q19

(Titre initial modifié pour ajouter: "et INSERT")

PS. Mise à jour SQLite 3.19.0

:wink:
Répondre