On Thu, Mar 25, 2010 at 05:22:04PM +0100, Akbar Syed scratched on the wall: > I have been trying to improve the performance and memory usage for my > application whereby i have maximum of 30 databases attached. In total I have > 31 databases with 30 databases attached to the first one. Each database has > a single table with approx 65 columns and the records in each table may go > upto 50,000. I have 31 connections for 31 databases i.e. 1 connection for > one database. For each connection I have a cache size of 500 pages (1 Page = > 1KB), temporary cache 500 pages and for each attached connection cache size > of 100 pages. My efforts to minimize memory usage as much as I can also the > speed of reading is tolerable. I dont mind the writing speed, but I do care > for reading speed. In one attempt, I would select all the records from all > the databases and thats the purpose I am using attached databases with a > single query.
If you want to improve utilization memory, just put everything in one database. Each attached database in each connection has its own cache. With that many data sources you're going to get a very uneven distribution of cache utilization, and very high worst-case usage. > In one attempt i tried to fetch just 250 records of 65 columns from 31 > databases and I observed that I spend approx 1-5 seconds in the first call > to sqlite3_step() function and the subsequent calls to sqlite3_step() are > some microseconds. "Just 250 records" implies a sort. Unless an index is available, a sort requires doing the whole query and sorting the results. That's the large lead time. After that it is just popping records off the stack, so to speak. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
