Discussion :

[billybobbonnet]

Salut à tous,

Je réalise une appli WPF qui effectue des traitements sur fichiers. A chaque premier traitement, je charge une table SQlite d'environ 500 000 lignes, 10 colonnes, dans un dataset en utilisant la méthode fill. Le souci, c'est que le temps de chargement de cette table représente près des deux tiers du temps total de traitement. Compte tenu du fait que cette table sert de référence et n'est pas supposée changer, est-ce possible de l'intégrer différemment de la méthode SQLite, tout en préservant la facilité de déploiement qui va avec?

Une précision: je ne peux pas travailler directement sur la table SQLite, car je présume que même avec des index le temps de requête ("select" en l'occurence) est trop long.

Merci!

[DotNetMatt]

2 questions :

1. Pourquoi recharges-tu les données à chaque premier chargement ? Si elles ne bougent pas, ce n'est pas la peine de le faire...
2. As-tu besoin d'avoir les 500 000 lignes à disposition ? Il serait probablement plus judicieux d'aller chercher les lignes 1 par 1 (ou par petits lots) uniquement quand cela est nécessaire

[billybobbonnet]

Salut Matt,

Pour répondre à tes questions:

Pourquoi recharges-tu les données à chaque premier chargement ? Si elles ne bougent pas, ce n'est pas la peine de le faire...

Je les charge dans un dataset sur lequel je crée un index à l'aide d'un dictionnaire (of String, datarow) pour améliorer le temps de récupération des données. En gros, j'ai deux types de requêtes:
- La première est pour tester l'existence d'une ligne dans la base. Je fais un monDictionnaire.contains
- La seconde est pour récupérer les données associées à l'index. Je fais un monDictionnaire.TryGetValue

A la louche, selon les fichiers, j'ai entre 1000 et un million d'itérations sur la base.

As-tu besoin d'avoir les 500 000 lignes à disposition ? Il serait probablement plus judicieux d'aller chercher les lignes 1 par 1 (ou par petits lots) uniquement quand cela est nécessaire

Je pense que oui. Mes requêtes ciblent des strings qui sont dans un ordre non prévisible. Pas moyen d'optimiser le chargement de telle ou telle partie de la base selon le lot de strings traitées. En totu cas, c'est comme ça que je comprends ta question. Si tu penses à une autre façon de faire, je suis preneur

J'ajoute que je fais ça en amateur, du coup si j'ai pu me rendre compte que certaines méthodes sont plus rapides que d'autres (par exemple dictionnaire vs requêtes select), je reste un gros ignorant sur ces questions.

[DotNetMatt]

Le problème avec SQLite c'est que c'est une base de données fichier, donc il ne faut pas en attendre les mêmes performances qu'une vraie base de données comme peut l'être SQL Server par exemple.

On a pas assez d'info sur ce que fait ton projet en détail pour pouvoir t'aider plus, mais j'ai l'impression qu'en fait tu substitues ta base SQLite par ton DataSet, ce qui au final n'est pas optimal puisqu'une base de données sera toujours plus performante qu'un DataSet ou autre, notamment de par sa capacité à réaliser des opérations ensemblistes. Via un DataSet ou n'importe quoi d'autre en code, ce sera séquentiel (ligne par ligne), donc forcément plus lent. De plus les DataSets génèrent un certain overhead car ce sont des boîtes noires qui mettent à disposition beaucoup de fonctionnalités, même si tu n'en utilises qu'une partie.

Si tu confirmes cette substitution, alors je pense qu'il faut revenir en arrière et faire travailler plus la base de données directement. Si les performances ne sont toujours pas satisfaisantes, c'est peut-être que le choix du SGBDR n'est pas bon.

SQLite est bien pour certains scénario, mais pas pour tous. Si tu veux de la puissance alors il faudra passer sur un SGBDR digne de ce nom.

[billybobbonnet]

Tu veux dire que même si SQLite est une BDD fichier, je devrais avoir de meilleures performances en l'utilisant directement qu'en passant par mon dataset? Il y a bien eu substitution à cause des performances, et je dois avouer que je ne suis pas sûr d'avoir utilisé mon index SQLite correctement. Je m'y perds un peu. J'avais fait un index du même nom que ma colonne, je suppose donc qu'il était utilisé à chaque requête type

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 SELECT String FROM MyDB WHERE String = 'MyString';

[Graffito]

Le souci, c'est que le temps de chargement de cette table représente près des deux tiers du temps total de traitement. Compte tenu du fait que cette table sert de référence et n'est pas supposée changer, est-ce possible de l'intégrer différemment de la méthode SQLite, tout en préservant la facilité de déploiement qui va avec?

Si la table est fixe, on peut passer par la sérialisation:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
// serialization : pour créer le fichier sérialisé à partir de la DataTable remplie par le fill de la table SQLITE 
FileStream fs = new FileStream(@"C:\SerializedData.bin", FileMode.Create);
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(fs, myDataTable);
fs.Close();
 
// deserialization : pour créer le dataset avec sa table en lisant le fichier sérialisé
FileStream fs = new FileStream(@"C:\SerializedData.bin", FileMode.Open);
BinaryFormatter bf = new BinaryFormatter();
DataTable myDataTable = (DataTable) bf.Deserialize(fs);
fs.Close();

[billybobbonnet]

Je suis repassé sur la fichier SQLite. Le traitement est environ 2 fois plus long, mais il n'y a plus de chargement de la base.

Ce qui me tracasse, c'est que je ne sais pas si l'index est utilisé. Je détaille un peu comment je m'y prends au cas où ça vous évoquerait un truc:

- La déclaration de l'index SQLite

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE INDEX [MyString] on [myBDD] ([MyString] COLLATE BINARY)

- Les requêtes en vb .net (avec le connecteur System.Data.SQLite) pour vérifier l'existence d'une ou plusieurs lignes, jamais plus de 3 ou 4, correspondant au mot cherché (ce qui je crois est un peu contraire à l'idée d'index > les clés ne sont pas toutes uniques)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
  Public Function IsItInBDD (ByRef MyString As String) As DataTable
        Try
            Dim ds As New DataTable
            Using c As New SQLiteConnection(ConnectionString4Read)
                c.Open()
                Dim mySelectQuery As String = "Select * From MyBDD where MyString = '" & MyString & "' ORDER BY Score DESC;"
                Dim sqCommand As SQLiteCommand = c.CreateCommand()
                sqCommand.CommandText = mySelectQuery
                Dim dataAdapter As New SQLiteDataAdapter()
                dataAdapter.SelectCommand = sqCommand
                dataAdapter.Fill(ds)
                c.Close()
                c.Dispose()
            End Using
 
            Return ds
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Return Nothing
        End Try
    End Function

Dernier point, j'ai intégré dans les ressources de mon projet .net le fichier de base de données (filetype: Binary). Si je comprends bien, en utilisant un Streamwriter comme le conseille Graphito, je pourrai extraire le fichier SQLite de mon .exe. Par contre, ce qui m'intéresserait, ce serait de mettre la BDD en mémoire vive tant pour la vitesse accrue des traitements que pour éviter qu'elle se ballade sur le disque. Je vois que SQLite permet d'accéder à une base en mémoire (http://www.sqlite.org/inmemorydb.html) mais je ne sais pas si on peut passer d'un stockage binaire à un accès à la base en mémoire (et par ailleurs si l'étape d'inscription sur le disque est obligatoire).

[DotNetMatt]

Déjà, il faut utiliser les requêtes paramétrées : .Net, SQL Server, et les requêtes paramétrées (VB.Net). Ca te protège contre les attaques par injection SQL, ça évite de se prendre le chou avec les différents formats de données, et ça stabilise le plan d'exécution de la requête.

Actuellement, à chaque fois que tu lances la requête avec un mot à la place de ta variable "MyString", il y a un plan d'exécution différent qui est utilisé. En utilisant un paramètre à la place, il n'y aura qu'un seul et unique plan d'exécution.

Après pour revenir au problème de performance, pour vérifier que ton index est bien utilisé, tu peux regarder le plan d'exécution à l'aide de l'expression EXPLAIN QUERY PLAN :
[CODE=SQL]EXPLAIN QUERY PLAN SELECT * FROM MyBDD WHERE MyString = @MyString ORDER BY Score DESC;[/QUOTE]

Sinon tu peux aussi regarder du côté de la FTS (Full Text Search) via ce lien : SQLite FTS3 and FTS4 Extensions. Il y est écrit qu'exécuter :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM MyBDD WHERE MyString = @MyString ORDER BY Score DESC;

est moins performant que d'utiliser l'opérateur MATCH :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM MyBDD WHERE MyString MATCH @MyString ORDER BY Score DESC;

De ce que j'ai pu constater sur Oracle et SQL Server, il est en effet souvent plus performant d'utiliser les fonctionnalités FTS. Mais c'est comme tout, ça dépend des cas...

Après je ne suis pas non plus spécialiste sur SQLite donc je te laisse tester pour en avoir le cœur net

[billybobbonnet]

ouhlà oui, c'est noté pour les requêtes paramétrées. Je les utilisais pour faire des inserts en batch dans une seule transaction, mais je pensais que ça n'avait pas d'intérêt pour des "select". L'article est très bien pour illustrer que j'avais tort

En utilisant EXPLAIN QUERY PLAN, j'ai pu confirmer que l'index était bien utilisé. Merci pour le tuyau!

J'ai refait ma fonction avec une requête paramétrée et j'ai gagné du temps d'exécution (pas quantifié mais c'est perceptible). Quant aux tables FTS (pour les requêtes MATCH), j'ai tenté de recréer ma base dans une table virtuelle FTS4 mais je n'ai pas trouvé de moyen pour que le MATCH ne renvoie que des égalités, pas des substrings. En faisant du post-traitement de la requête je suis à 24 secondes pour mon fichier test. Sans post-traitement (mais avec plus de lignes à traiter, du coup) je suis à 23 secondes et des poussières. En restant avec ma table classique à index, je suis à 17 secondes.

Bref, c'est une info que je garde sous le coude, mais je ne pense pas que ça convienne dans mon cas.

Je me permets de revenir sur les questions que j'avais plus tôt au cas où vous auriez une réponse.

Dernier point, j'ai intégré dans les ressources de mon projet .net le fichier de base de données (filetype: Binary). Si je comprends bien, en utilisant un Streamwriter comme le conseille Graphito, je pourrai extraire le fichier SQLite de mon .exe. Par contre, ce qui m'intéresserait, ce serait de mettre la BDD en mémoire vive tant pour la vitesse accrue des traitements que pour éviter qu'elle se ballade sur le disque. Je vois que SQLite permet d'accéder à une base en mémoire (http://www.sqlite.org/inmemorydb.html) mais je ne sais pas si on peut passer d'un stockage binaire à un accès à la base en mémoire (et par ailleurs si l'étape d'inscription sur le disque est obligatoire).

En tout cas, merci pour le coup de main, Matt!

[billybobbonnet]

J'ajoute que j'ai trouvé un moyen de faire un match de la string exacte en décomposant ma requête en deux parties

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select * from myBDD where mystring MATCH 'blabla' GROUP BY mystring HAVING mystring = 'blabla'

qui apparemment, effectue le traitement group by... having après le "select". La requête prend plus longtemps que son équivalent (je crois) simultané

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select * from myBDD where mystring MATCH 'blabla' and mystring = 'blabla'

Et cet équivalent prend lui aussi plus de temps que la requête sur index dans une table non FTS4.