On Sat, Mar 21, 2009 at 01:55:32PM -0500, P Kishor wrote: > On Sat, Mar 21, 2009 at 1:25 PM, Nicolas Williams > <nicolas.willi...@sun.com> wrote: > > On Sat, Mar 21, 2009 at 12:14:43PM -0500, P Kishor wrote: > >> If I can't improve 33 ms per query, then I can experiment with > > > > 33ms per-query sounds like you're not caching enough of the database in > > memory. What's the cache size? Can you jack it up? > > hmmm... never thought of the cache size. From the docs... > > "PRAGMA default_cache_size = Number-of-pages; > [...] > So, any suggestions on what cache size I should experiment with? And, > does this have to be set *before* the db is created? From the above > description it sounds like I can set the cache_size at any time.
Well, take the number of pages in your DB (for a vacuumed DB: file size / page size) and see if setting cache_size == that improves things. (default_cache_size is stored in the DB so that connections that don't specify a cache_size get what default_cache_size says.) > > Does the entire > > dataset fit in memory? If so, why isn't it all in memory? Or if it is > > all in memory, what's SQLite3 spending its time on? > > How do I take a db on disk and load it all in memory? How is that > done? I have never done that before. Seems like I can create a db in > memory with "The online-backup interface can be used to copy content from a disk file into an in-memory database or vice versa and it can make a hot backup of a live database." http://sqlite.org/backup.html But the thing is, you might just set the cache size large enough and let it warm up as you go -- the effect should be the same if your DB doesn't grow very fast. > Also, isn't > there a 2 GB limit to the amount of RAM that 32-bit processes can > address? Even so, 1GB of cache is much better than the 2000 page (x 1KB page size == 2MB) default. Also, you might consider going 64-bit. > By the way, even though I have a lot of computing horsepower, I would > like to work toward a solution that would work reasonably well even > without access to a cluster. While no one would expect lightning fast > responses for model runs over millions of cells, it would be nice to > cut the time from several hours down to sub-hour levels. But that is a > longer road to tread. Memory is the key for a large DB using SQLite3. If you're building a distributed application the SQLite3 is probably the wrong tool to use (though you could use SQLite3 with a local copy of a DB if replication is easy because your dataset is mostly read-only, say) Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
