Insertions en masse plus rapides dans sqlite3 ?

Question

J'ai un fichier d'environ 30000 lignes de données que je veux charger dans une base de données sqlite3. Existe-t-il un moyen plus rapide que de générer des instructions d'insertion pour chaque ligne de données ?

Les données sont délimitées dans l'espace et correspondent directement à une table sqlite3. Existe-t-il une méthode d'insertion en masse pour ajouter des données volumineuses à une base de données ?

Quelqu'un a-t-il imaginé un moyen détourné et merveilleux de faire cela si ce n'est pas intégré ?

Avant de commencer, je dois demander s'il existe un moyen C++ de le faire à partir de l'API.

Answer 1

• regrouper tous les INSERTs dans une transaction, même s'il n'y a qu'un seul utilisateur, c'est beaucoup plus rapide.
• utiliser des déclarations préparées.

Answer 2

Vous voulez utiliser le .import commandement. Par exemple :

$ cat demotab.txt
44      92
35      94
43      94
195     49
66      28
135     93
135     91
67      84
135     94

$ echo "create table mytable (col1 int, col2 int);" | sqlite3 foo.sqlite
$ echo ".import demotab.txt mytable"  | sqlite3 foo.sqlite

$ sqlite3 foo.sqlite
-- Loading resources from /Users/ramanujan/.sqliterc
SQLite version 3.6.6.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from mytable;
col1    col2
44      92
35      94
43      94
195     49
66      28
135     93
135     91
67      84
135     94

Notez que cette commande de chargement en masse n'est pas SQL mais plutôt une fonctionnalité personnalisée de SQLite. En tant que telle, elle a une syntaxe bizarre parce que nous la transmettons par l'intermédiaire de echo à l'interpréteur de ligne de commande interactif, sqlite3 .

En PostgreSQL, l'équivalent est COPY FROM : http://www.postgresql.org/docs/8.1/static/sql-copy.html

Dans MySQL, c'est LOAD DATA LOCAL INFILE : http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Une dernière chose : n'oubliez pas de faire attention à la valeur de l'option .separator . C'est un piège très courant lorsque l'on fait des insertions en vrac.

sqlite> .show .separator
     echo: off
  explain: off
  headers: on
     mode: list
nullvalue: ""
   output: stdout
separator: "\t"
    width:

Vous devez explicitement définir le séparateur comme étant un espace, une tabulation ou une virgule avant d'utiliser la commande .import .

Answer 3

Vous pouvez également essayer réglage de quelques paramètres pour en tirer une vitesse supplémentaire. Plus précisément, vous voulez probablement PRAGMA synchronous = OFF; .

Answer 4

• Augmenter PRAGMA cache_size à un nombre beaucoup plus important. Cela va augmenter le nombre de pages mises en cache en mémoire. REMARQUE : cache_size est un paramètre par connexion.

• Regroupez toutes les insertions en une seule transaction plutôt que d'effectuer une transaction par ligne.

• Utilisez des instructions SQL compilées pour effectuer les insertions.

• Enfin, comme nous l'avons déjà mentionné, si vous êtes prêt à renoncer à une conformité ACID complète, définissez le paramètre PRAGMA synchronous = OFF; .

Answer 5

RE : "Existe-t-il un moyen plus rapide que de générer des instructions d'insertion pour chaque ligne de données ?"

Premier : Réduire le nombre de requêtes SQL à 2 en utilisant la fonctionnalité de Sqlite3 API de table virtuelle par exemple

create virtual table vtYourDataset using yourModule;
-- Bulk insert
insert into yourTargetTable (x, y, z)
select x, y, z from vtYourDataset;

L'idée est d'implémenter une interface C qui lit votre ensemble de données source et le présente à SQlite sous la forme d'une table virtuelle, puis vous effectuez une copie SQL de la source vers la table cible en une seule fois. Cela semble plus difficile que ça ne l'est réellement et j'ai mesuré d'énormes améliorations de vitesse de cette façon.

Deuxièmement : utilisez les autres conseils fournis ici, à savoir les paramètres de pragma et l'utilisation d'une transaction.

Troisièmement, voyez si vous pouvez supprimer certains des index de la table cible. De cette façon, sqlite aura moins d'index à mettre à jour pour chaque ligne insérée.