System.Data.SQLite

Hello, I have a mobile application. My client has a large data set ~100.000 records. It's updated frequently. When we sync we need to copy from one database to another. I have attached the second database to the main, and run a insert into table select * from sync.table This is extremely slow, it takes about 10 minutes I think. I noticed that the journal file gets increased step by step. How can I speed this up? Regards, Pentium10

 Hi!

 Take a look at the following link:

http://www.sqlite.org/pragma.html

Especially the journal_mode, synchronous and temp_store pragmas.

In your case, I think you should tell the connection to create temp tables in memory, switch journal mode to memory and turn off synchronous mode:

string cmdText = "PRAGMA temp_store=2;
PRAGMA journal_mode=memory;
PRAGMA synchronous=0;";

If you use ATTACH to work with more than one db inside an already opened SQLiteConnection, don't forget to adjust journaling mode for the attached db within the same command text!

string cmdText = "ATTACH DATABASE 'dbFilePath' AS db2;
PRAGMA db2.journal_mode=memory;";

When detaching, I would switch back journal mode to default (delete)

string cmdText = "PRAGMA db2.journal_mode=delete;
DETACH DATABASE db2;";

Using these PRAGMAs, the speed of db writes will be much much faster!

 

Take a time and test all combinations of the mentioned PRAGMAs, because in my case, it was enough to turn synchronous mode off, and I did not have to play with journal_mode, it did not make a difference, but used significantly more memory to store TEMP tables.

I hope it was helpful.

1. not sure why you use ps. prefix here, but I used my alias for the table
PRAGMA ps.journal_mode=delete
changed in
PRAGMA db2.journal_mode=delete



2. when using temp store in memory, or journal mode off, I get critical low memory limit
then a malloc() failed, out of memory

3. Not if this counts, but my database files are 12-15 megabytes. The inventory table is the biggest.

1.  You are right, I shoul have written "db2." instead of "ps.".
(I've corrected the "ps." prefix to "db2 in my original post.)

 

2. try not using memory for temp store, just the journal mode off. It makes a significant difference in speed.

3. Our database sizes are 10-50 MBytes on clients and we are using synchronous mode off without memory problems.

I tried the 2nd step. I had initially journal mode off. I am able to test only on Treo 750, which has 60MB inbuilt memory, but still have memory out limit. I am wondering does it count how much free space you have on your memory? As that isn't 60MB only 10MB.

I've checked the description of journal_mode at
http://www.sqlite.org/pragma.html
and I realized I was wrong about its meaning, I mixed it with synchronous PRAGMA.

You should use synchronous = 0 instead of journal_mode=off !!!

synchronous=off is the one, that tells the SQLite engine not to wait until the IO writes finish. It means that it sends the IO write requests to the file system and immediately returns from the method cal, thus making IO writes really faster!

"On the other hand, some operations are as much as 50 or more times faster with synchronous OFF. "

If you use transactions that can fail, DON'T TURN journal_mode OFF !!!
(Read the consequences at the above linked PRAGMA page on sqlite.org)

Yeah, I noticed that. I've tried but still the same, just a slight speed gain.