On Wed, 27 Jan 2010 23:20:01 -0500, Trapper Schuler <trapper.schu...@technosoft.com> wrote:
>Hello, > >I am new to SQLite, but have experience with other database management >systems. Performance is very critical in my application, but the data >is getting too large to keep in memory. My application is not just a >database front end. The database is just a part of the overall application. > >What is the best way to "buffer" the database in memory while still >being able to save the database to disk when needed later? The data is too large to keep in memory, so the in-memory database can only contain part of it. You can attach a second, file based database to flush unused data to disk with INSERT INTO filedb.tbl .. (SELECT .. FROM memdb.tbl ..) and DELETE FROM memdb.tbl ... , but that's not a simple action. You have to age the data and decide what to keep. Luckily, an in-memory database is not needed for this purpose. SQLite will maintain a cache of recently used database pages in a smart way, with some priority for index pages. That cache works perfectly if the database is only used by one process, your application. >========================================== >Some Relevant Links: >========================================== >http://www.sqlite.org/inmemorydb.html >http://www.sqlite.org/faq.html (See #19 "INSERT is really slow...") >http://www.sqlite.org/c3ref/funclist.html >========================================== > >I have read about "In-Memory Databases". > >I have read about "Temporary Databases". This seems to be useful. How >well does it detect "memory pressure"? How large is a database that >becomes "too large"? In my application, the database will have to share >memory with other parts of my application. How soon does it detect >"memory pressure" before the application itself starts to use Virtual >Memory? I don't think SQLite detects memory pressure. Your application can. SQLite, the cache and housekeeping data is in the memory footprint of your application. The only thing you have to take care of is the size of the cache (PRAGMA default_cache_size and/or PRAGMA cache_size). That's deterministic. Also, have a look at the sqlite3_memory_() functions. >If I use a "Temporary Database" or an "In-Memory Database", is there an >easy way to take that database and write it to disk when I need to >(programmatically)? (Is there a database copying function that will >take a "From" handle and a "To" handle or something similar?) (When I >state "write it to disk", I want to write it to a file with a specific >name.) The easy way would be the backup interface, but that saves all of the database. And you already told us the in-memory database can't hold all the data you need, because it is too large. So, the easy way is not feasible. HTH -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
