GB3- Optimiser la vitesse de traitement base sqlite

( Temps de lecture estimé à 4 min 01 sec )

Bonjour,

Aujourd'hui nous allons optimiser la vitesse de traitement de notre programme qui gère une base de données sqlite.

1- Hypothèse

Soit une base de données sqlite de 2500 lignes dont je veux modifier la ligne 120, 130, 156, 2120 et 2000 en mettant la valeur "My Value" dans la colonne mycolumn.

2- Création de la liste des lignes

Plutôt que de gérer le ligne par ligne à savoir un programme comme ceci:

Private Sub myvar()
myline = 120
Databasemodify()
myline = 130
Databasemodify()
myline = 156
Databasemodify()
myline = 2120
Databasemodify()
myline = 2000
Databasemodify()
End

Private Sub Databasemodify()
'ouverture de la base de données
'modification de ma base de données
'Fermeture de la base de données
End

qui vous le voyez bien est répétitif.

3 - Optimisation du code

Il est plus simple d'utiliser une liste comme ceci :

Private myline As Integer = [120,130,156,2120,2000]

Private Sub myvar()
Dim c As Integer
For c = 0 To 4
    myline = myline[c]
    Databasemodify()
Next
End

Private Sub Databasemodify()
'ouverture de la base de données
'modification de ma base de données
'Fermeture de la base de données
End

Le code semble plus simple, mais il y a un problème.
Nous ouvrons et fermons sans cesse la base de données pour des accès disques.
Pour notre exemple il s'agit de modifier 5 valeurs, mais si nous devions modifier 1500 valeurs le temps ainsi gaspillé nous ralentirait notre programme.

4 - Changement de la requête.

En temps normal j'utilise les méthode Edit, Delete, Add de gambas pour travailler sur les bases de données.

Un exemple pour modifier une valeur:

myconnect.Type = Lower("sqlite3")
myconnect.Host = Application.Path
myconnect.Name = "mydatabase.db"
myconnect.Login = ""
myconnect.Password = ""
myconnect.Port = ""
myconnect.Open()
myresult = myconnect.edit("mytable", "id=" & myline)
myresult!mycolumn = "My Value"
myresult.Update()
myconnect.Commit()
myconnect.Close()

Remarque: Nous pourrions écrire une boucle dans laquelle nous passerions les valeurs respectives de myline dans les 3 lignes suivantes :

myresult = myconnect.edit("mytable", "id=" & myline)
myresult!mycolumn = "My Value"
myresult.Update()

5 - Tout écrire en une seule fois

La puissance des requêtes sqlite nous donne tout un pannel de solutions possibles.
Ainsi en une seule requête, nous pouvons modifier tout un tas de données.
Pour ceux qui connaissent bien sqlite, (et merci à patrick pour son aide ;-) ), nous allons écrire la requête suivante :

Private sql As String
sql = "UPDATE mytable SET mycolumn='My Value' WHERE id='120' OR id='130' OR id='156' OR id='2120' OR id='2000'"

Vous l'aurez compris, le code ci dessous :

Private sql As String
myconnect.Type = Lower("sqlite3")
myconnect.Host = Application.Path
myconnect.Name = "mydatabase.db"
myconnect.Login = ""
myconnect.Password = ""
myconnect.Port = ""
myconnect.Open()
sql = "UPDATE mytable SET mycolumn='My Value' WHERE id='120' OR id='130' OR id='156' OR id='2120' OR id='2000'"
myresult = myconnect.Exec(sql)
myconnect.Close()

produit le même résultat que le code du paragraphe 4 mais en une passe.
pour une modification de 1500 lignes, je peux vous dire que le résultat est particulièrement ultra rapide.

Remarque : la remarque pertinente serait de dire que j'a construit de toute pièce la requête et qu'elle n'es pas modifiable en l'état.

6- Création de la requête

C'est pourquoi je vous propose la combinaison du code du paragraphe 3 et 5 à savoir :

- Création d'une liste des lignes
- Création de la requête sqlite
- Modification de la base de données

Ecrivons le code :

 
 Private myline As Integer = [120,130,156,2120,2000]
 
 Private Sub buildsqlrequest()
Dim sql As String
Dim c As Integer

sql = "UPDATE mytable SET mycolumn='My Value' WHERE id='"

For c = 0 To 4
    sql = sql & myline[c]  &  "' OR id='"
Next
End

Voici notre requête ainsi générée :

 "UPDATE mytable SET mycolumn='My Value' WHERE id='120' OR id='130' OR id='156' OR id='2120' OR id='2000' ' OR id='"

Il faut enlever le trop rempli pour les 8 caractères restants comme ceci :

sql = Left(sql, Len(sql) - 8)

La requête ainsi créée est la suivante :

 "UPDATE mytable SET mycolumn='My Value' WHERE id='120' OR id='130' OR id='156' OR id='2120' OR id='2000'"

un :

myresult = myconnect.Exec(sql)
myconnect.Close()

et tout fonctionne!

!! CQFD !!

7- CONCLUSION

Voici le code complet de l'exemple :

 Private Myconnect As Connection
 Private Myresult As Result
 Private myline As Integer = [120,130,156,2120,2000]

 Private Sub Wdata() 'Write data
Dim sql As String
Dim c As Integer
myconnect.Type = Lower("sqlite3")
myconnect.Host = Application.Path
myconnect.Name = "mydatabase.db"
myconnect.Login = ""
myconnect.Password = ""
myconnect.Port = ""
myconnect.Open()
sql = "UPDATE mytable SET mycolumn='My Value' WHERE id='"
For c = 0 To 4
    sql = sql & myline[c]  &  "' OR id='"
Next
sql = Left(sql, Len(sql) - 8)
myresult = myconnect.Exec(sql)
myconnect.Close()
End

Personnellement je préfère utiliser les requêtes classiques sqlite plutôt que les commandes classiques gambas pour des opérations compliquées sur les bases de données, c'est bien plus rapide, compréhensible et bien plus facile à mettre en oeuvre.

Merci pour votre attention.