Databases
StackOverflow2013
Postsdbo
POSystem.Data.Sqlite (C#) vs. Sqlite (C) performance
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSystem.Data.Sqlite (C#) vs. Sqlite (C) performance
    primarykey
    data
    text
    <p>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. </p> <p>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.)</p> <p>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.)</p> <p>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.</p> <p>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.)</p> <p>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.</p> <p>However, I feel that the biggest problem is the data processing in the application. </p> <ul> <li>It has to parse several protocols (http, SOAP, Xml) </li> <li>The data processing in System.Data.Sqlite and calling sqlite3.dll. This probably involves allocations, marshalling etc.</li> </ul> <p>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?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDavid Knight
    1. USJan Slodicka
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSystem.Data.Sqlite (C#) vs. Sqlite (C) performance
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POSystem.Data.Sqlite (C#) vs. Sqlite (C) performance
      1. USGovert
      1. VTFavorite
    1. COHm, are you adding a huge amount of rows in every transaction or are you splitting them up a bit? Transaction logging may be your problem if you're using huge transactions.
      singulars
      1. POSystem.Data.Sqlite (C#) vs. Sqlite (C) performance
      1. USJoachim Isaksson
    2. COI thought about it. But take the described test with the dumped string. Everything there (1000s of commands, the dump string itself is roughly 130 MB) are within a single transaction. And the result is perfect.
      singulars
      1. POSystem.Data.Sqlite (C#) vs. Sqlite (C) performance
      1. USJan Slodicka
    3. COSorry, then I got nothing :) Never tried that kind of sized transactions in sqlite on a mobile platform.
      singulars
      1. POSystem.Data.Sqlite (C#) vs. Sqlite (C) performance
      1. USJoachim Isaksson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload