Change SQLiteConnectionStringBuilder parameters make write to database faster in C#

Question

I create a SQLite database for mobile application with third party application in C# language. I insert map data in database. it is very slow and lazy in inserting data (map tiles) and take long time for inserting data. Is there any recommendation in database creation to increase write speed of SQLite database? (for example size of database file or create cache file or ...)

This is my connection string builder parameters:

SQLiteConnectionStringBuilder conString = new SQLiteConnectionStringBuilder();
conString.DataSource = databaseFileName;
conString.DefaultTimeout = 5000;
conString.FailIfMissing = false;
conString.ReadOnly = false;

Have you wrapped all inserts in one transaction explicitely? It makes it faster. — Szymon, Mar 09 '14 at 10:45
In other project i pass an DataTable to DbAdapter.Update() and it update database.But it is so lazy. — Mohamed Salemyan, Mar 09 '14 at 11:19
possible duplicate of [How do I improve the performance of SQLite?](http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite) — Alberto, Mar 10 '14 at 10:38
That question is general and have a lot of answers. This question is specific and brief. — Mohamed Salemyan, Mar 10 '14 at 10:56

Mohamed Salemyan · Accepted Answer · 2014-03-10T16:47:43.367

4

I had found some connection string settings that extremely increase my insert speed:

Connection string for better performance:

SQLiteConnectionStringBuilder conString = new SQLiteConnectionStringBuilder();
conString.DataSource = databaseFilePath;
conString.DefaultTimeout = 5000;
conString.SyncMode = SynchronizationModes.Off;
conString.JournalMode = SQLiteJournalModeEnum.Memory;
conString.PageSize = 65536;
conString.CacheSize = 16777216;
conString.FailIfMissing = false;
conString.ReadOnly = false;

edited Mar 10 '14 at 16:47

answered Mar 10 '14 at 10:35

Mohamed Salemyan

691
2
15
31

Which file are you referencing? Microsoft.Data.Sqlite? System.Data.Sqlite? – Mehdi Anis Feb 25 '20 at 18:40
Dear Mehdi Microsoft.Data.Sqlite release date is 6/27/2016 and my question date is 4/09/2014. Off course i use System.Data.Sqlite.Thanks for your appropriate question. – Mohamed Salemyan Feb 26 '20 at 07:58
1

Microsoft.Data.Sqlite does not have connection string keyword for journal mode. you should set this parameters with command object. – Mohamed Salemyan Feb 26 '20 at 09:56
Thanks Mohammed Salemyan. I am using System.Data.Sqlite and can load 7Million lines with 14 columns in 16 minutes. I am using Batch Insert of 10,000 rows at a time using Transaction. – Mehdi Anis Feb 28 '20 at 15:20

Change SQLiteConnectionStringBuilder parameters make write to database faster in C#

1 Answers1