CODEWITHSUNDEEP
sqlitemono
Jan Slodicka
Jan Slodicka

Reputation: 1515

System.Data.Sqlite (C#) vs. Sqlite (C) performance

Situation: The .Net client app (C#, Mono) downloads data from the web service (SOAP) and stores it into an Sqlite DB. The DB interface is System.Data.Sqlite that under the hood uses sqlite3.dll.

The DB (130 MB) has a few dozens of tables. One table is particularly large and takes 90% of the DB size - 10000 of records with some blob columns. (Largest blob has 260K.)

Download on the iPad takes 22 min. When I commented out actual writing into DB, it took some 11 min, so it looks like the DB takes about 11 min, too. By "DB" I mean some layer above System.Data.Sqlite. Don't know the details yet. All I know is that all DB commands are in a transaction and there are just a few transactions involved. (In other words transactions are not a problem.)

When I dumped the DB using Sqlite shell and measured the C code that calls sqlite3_exec() with the dumped string (this code is far from being optimal), I got about 50 secs (iPad). It means sqlite C code can create the DB very fast.

Another interesting problem: The download is organized table by table. All the tables (some of them having a few MB) work ok. Except the largest table, where the download had to be reorganized to a few items (5) at a time. Without this measure the download failed with insufficient memory. Most probable explanation is memory fragmentation. (Mono has problems with GC and does not provide good memory info.)

My feeling is that the download process itself is responsible for a smaller part of the problem. It downloads roughly the same size as the DB. Fragmentation should not be the problem. But the single-thread organization adds on the latency.

However, I feel that the biggest problem is the data processing in the application.

We'll be doing more tests later, but right now I would like to ask you for ideas. For example I would welcome performance comparison of System.Data.Sqlite vs. raw Sqlite. Anybody?

Upvotes: 0

Views: 1966

Answers (1)

David Knight
David Knight

Reputation: 761

I've been doing some tests in the past few days with Mono.Data.Sqlite and found that insert performance is terrible.

I'm only seeing ~14,000 inserts per second with C# compared to ~38,000 with the C equivalent. This is with an insert of string, string, string, string, string, string, DateTime, in a transaction using a paramaterised statement inserting 1,024,000 rows. (same performance dropping the number of rows down a lot)

Commenting out the actual ExecuteNonQuery and both C and C# get through the same number of iterations for me of around 112,000 per second (generating the strings) so in my case the problem is coming from however mono is handling the interop with sqlite.

These tests have been under MacOSX and I have not tried on an actual device due to not having a paid monotouch license to see if this would improve.

Upvotes: 1

Related Questions