Fast batch insert to SQLite database

Recently I am working with SQLite. Like many of you do, I sometimes need to insert large amount of data to the database. There may be thousands, or even more, of records waiting for you. Inserting them to a SQLite database could be a nightmare that it would work like forever. Asking Google about this you will be probably instructed to fine tune the SQLite parameters. Yes, this is one way but if you are not doing this too frequently, I may have another, somehow dirty, suggestion for you.

Here is what I did:

1. Somehow you prepared the insert statements in text file (e.g. insert.sql). I got it from my colleague who dumped the whole database with tons of records into SQL format using the .dump command.
   
2. Use sqlite3.exe to load your database, to which the data is inserting:
   

   C:\> sqlite3.exe mydb.db

   
   
3. Backup the database using the .backup command into a file (e.g. original.db):
   

   sqlite> .backup original.db

   
   
4. If the target tables are not empty, dump them into SQL format using the .dump command too (e.g. data.sql). Better dump other related tables if you have set certain kinds of constrains (e.g. foreign key) too. Then you may quit the utility:
   

   sqlite> .output data.sql
   sqlite> .dump table1 tabe2
   sqlite> .output stdout

   
   
5. Run sqlite3.exe again without specifying the database file, so that a memory database will be used:
   

   C:\> sqlite3.exe

   
   
6. Restore from your database backup (original.db in the example) using .restore command:
   

   sqlite> .restore original.db

   
   
7. If you have done step 4, drop those target tables (and related ones). Load and execute the table data backup with .read command:
   

   sqlite> DROP TABLE1;
   sqlite> DROP TABLE2;
   sqlite> .read data.sql

   
   
8. Now load and execute the insert statements with .read command. Hey, don't plan to go for tea, it will finish shortly:
   

   sqlite> .read insert.sql

   
   
9. Backup the database using the .backup command:
   

   sqlite> .backup new.db

   
   
10. Now you can use to restore the backup to anywhere you like. I restored to a new database file and then replaced my existing one.
    

As you can see, the idea here is actually very simple: insert to a memory database instead of a file, but this does improve the performance dramatically.

BTW, later I found out that it may be related to the transactions. Making the whole batch as a single transaction may help as suggested in the FAQ. Journal file may also take some time . So temporarily turning off the journal with the PRAGMA journal_mode = OFF; statement before inserting might actually help too. I didn't verified these... Feel free to try it out :p

Windows 7 Shortcuts...

Yes. I have switched to Windows 7.

The Master List of New Windows 7 Shortcuts

Really a useful list. I only know some of them before.

Among the full list. below are those I think to be a must know:

• Win + Home: Hide all windows except the active one. Same effect as you drag and and shake a window. Useful in place like... your office? Hah
• Win + Space: Temporary shows your desktop by temporarily making all windows transparent. Same effect as putting your mouse cursor on the bottom right corner of your task bar
• Win + Left/Right: Dock the window to left/right
• Double clicking the top window browder/Shift+Win+Up: Maximizing the window vertically
• Shift + Win + Left/Right: Moving window between monitors
• Shift + (Win + number) or left click: Starting a new instance of n-th application application on task bar
• Shift + Ctrl + Win + number) or left click: Starting a new instance of n-th application application on task bar as administrator
• Ctrl + Shift + N: Create new folder in Windows Explorer
• Shift + Right click on something: Give you extra useful items in menu
• Win + (plus or minus): Launch magnifier to zoom in/out. First launch need press Win + plus twice.

Also some other tricks you should know:

• The application switching dialog and the fancy switching screen now support mouse click :)
• Ctrl + (Alt/Win) + Tab: Can make the application switching dialog or the fancy switching screen stay after you releasing the keys, and allow you to use mouse scroll to go through them

And I think here are some useful shortcuts/feature Windows 7 is still lacking:

• Multiple desktop workspace like OS X and Linux does.
• Shortcut to switch between windows of the same application
• Shortcut to expose all windows, or windows of the current application :)