System.Data.Sqlite: Cache Size=... connections string parameter doesn't seem to work

I'm comparing speeds of disk and in-memory databases. I'm trying to insert approximately 8mb of data to a sqlite3 db with the following connection string:

<configuration>
    <connectionStrings>
      <add name="Server" connectionString="Data Source=db.db3;Version=3;" providerName="System.Data.SQLite" />
   </connectionStrings>
</configuration>

It gets my data inserted in around 4 seconds.

When I change my connection string to the following one:

<configuration>
    <connectionStrings>
      <add name="Server" connectionString="Data Source=:memory:;Version=3;" providerName="System.Data.SQLite" />
   </connectionStrings>
</configuration>

I get my data inserted in around 0.5 seconds.

But when I change the settings the following way (note Cache Size=16777216)

<configuration>
    <connectionStrings>
      <add name="Server" connectionString="Data Source=db.db3;Version=3;Cache Size=16777216;" providerName="System.Data.SQLite" />
   </connectionStrings>
</configuration>

This results 4 seconds again, and result is independent of the amount of cache. In my understanding having the cache size twice as large as the开发者_如何学编程 amount of data should make my data settle in memory. And disk migrations should occur when no more cache left...

How can I make sqlite more responsive?

It's slower because :memory means the database in memory, disk will always be slower. You may be able to speed things up a bit by wrapping the inserts in a transaction as it will only flush to disk once at the end of the transaction. Turning of the transaction journal will help as well but will make it more prone to issues due to crashes and power loss.

It's unlikely the cache will speed anything up because you aren't using the same page of data more than once.

According to the SQL Lite Documentation the setting of cache size is a suggestion only. "Whether or not this suggestion is honored is at the discretion of the Application Defined Page Cache"