If your application can reasonably multi-leave "in-engine" operations -vs- out-of-engine operations, then you might want to try multiple threads against a single connection. In any case, make sure that you are not opening and closing connections. Open the connections when your application starts, and close them when it is done. The page cache will have almost no effect if you delete it between each use (think of the page cache as loading the database into RAM -- when you open a connection, the entire database must be copied into RAM -- when you close the connection, you are discarding that copy. If you then open another connection, you have to load all your data from disk into that connection too. As you can see, opening and closing connections will incur a significant overhead to repetitively read data which should already be in the cache, were it not for you discarding it from memory.) As long as you are properly resetting and disposing of your prepared statements and make su re that you do not keep "unnecessary crap" (like forgetting to commit transactions or reset statements), keeping a connection open for minutes/hours/days/years/decades/centuries does not have any ill effect (though it will make your programming errors jump out at you, often with odd behaviour and crashes).
--- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Wayne Bradney > Sent: Saturday, 12 January, 2013 12:36 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Concurrent read performance > > Increasing cache_size didn't seem to have an effect. I think I'm going to > lean towards taking the 15% or so hit and use a file-backed db without shared > cache for now -- I'll have to see what the impact of that will be on write > performance. If that's OK, then maybe a ramdisk will get back some of that > performance. I guess I was hoping that a memory-backed db would simply behave > exactly like a ramdisk in the first place, but the shared cache requirement > kills us. > > From: mdblac...@yahoo.com > > To: sqlite-users@sqlite.org > > Date: Sat, 12 Jan 2013 12:02:25 -0600 > > Subject: Re: [sqlite] Concurrent read performance > > > > Also...does increasing cache_size help? > > Are you able to use a RAM disk? > > > > > > -----Original Message----- > > From: sqlite-users-boun...@sqlite.org > > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wayne Bradney > > Sent: Saturday, January 12, 2013 11:39 AM > > To: sqlite-users@sqlite.org > > Subject: [sqlite] Concurrent read performance > > > > I have a requirement for which I'm proposing SQLite as a solution, and > would > > appreciate some feedback to clarify my thinking. The application is a > shared > > service (.NET/WCF) that caches relational data in-memory and serves it up > > when (read-only) requests come in (via operation contracts) from multiple > > clients. Such client requests are high-level, not relational, and could > > result in several (potentially many) individual queries to the cache > itself. > > These individual cache queries are dynamically generated and could be > > arbitrarily complex, but are all essentially relational in nature. The > > service itself will periodically load data from an external data source, > > transform it and update the cache. There's no requirement currently for the > > cache to ever be persisted - it can be reloaded from the external source if > > necessary, but performance (especially read performance) is critical. The > > amount of data/indexes to cache potentially could be quite large (of the > > order of several gigabytes, let's > > say). I've already worked an initial implementation that uses an in-memory > > SQLite database via System.Data.SQLite (1.0.82.0). The service maintains a > > "mode=memory&cache=shared" database, and each SQL query happens on its own > > connection, and in its own thread. Some observations of our read > performance > > (when the cache is fully populated and there are no writers): [Experiment > 1: > > Memory-backed, single query] > > For simple client requests that only result in a single (albeit complex) > SQL > > query to the database, performance is excellent, and I'm very happy to get > > the maintenance benefits of using a flexible query language against the > > cache. [Experiment 2: Memory-backed, concurrent queries] > > For any client request that results in multiple simultaneous SQL queries to > > the database, those queries seem to be serialized rather than concurrent, > > and the whole request actually performs much worse than the old > > home-grown-but-horribly-limited caching/querying mechanism that was in > place > > beforehand, and I'm sad. [Experiment 3: File-backed, concurrent queries, > > with shared cache] > > I switched to a file-backed database (but still "cache=shared") and it > > appears that the queries are still being serialized, and is overall about > > 15% slower than Experiment 2. [Experiment 4: File-backed, concurrent > > queries, without shared cache] > > I switched to a file-backed database without a shared cache, and > performance > > improved dramatically (about 70% faster than Experiment 3). It appears that > > the queries are now truly happening concurrently. So it appears that, > since: > > 1. when shared cache is enabled, all reads are serialized, and > > 2. there doesn't seem to be any way to have a memory-backed database that > > can be accessed by multiple connections without using a shared cache, then > > I guess I MUST use a file-backed database to get concurrent reads, even > > though I don't need the persistence and don't want to take the I/O hit. Am > I > > making any sense? Anything I'm missing? > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
