-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of anjela patnaik Sent: Thursday, March 19, 2009 11:15 AM To: [email protected] Subject: [sqlite] memory and db size
Hello all, I'm working on a GUI application using Tcl/Tk with sqlite back end on windows. Now, multiple users will be running this and all users must have a copy of the db local to their PC or in a shared drive. At least that is my understanding of how sqlite works. 1. What are my options if the db gets very large (say > 1 G) and we don't want users to allocate that much disk space and RAM? 2. How does sqlite allocate RAM when doing select statements? what about when opening the db ? 3. Are there any ways to compress db files in disk (for free) and then is sqlite able to still get to the data easily? Thank you! =============================================== =============================================== Hi Anjela, I'm probably going to answer only part of your questions. Maybe others will reply or you will want to reformulate some questions. Regarding: "...all users must have a copy of the db local to their PC or in a shared drive." I guess it goes without saying that if each user keeps a local copy there will be no (automatic) communication of data among these separate databases. On the other hand, performance will be decreased, of course, if each user opens a common database file on a shared drive -- though this may well be acceptible in your application, and some operating systems have network file locking that has been called into question. Some folks have implemented sqlite server-like daemons or have written web-based applications so that only a local program needs to open the database. 1. Regarding: "What are my options if the db gets very large...? I'm not sure I understand. Are you asking just in general, or is there something relating to sqlite that you had in mind? The obvious answers that spring to mind may not be helpful, such as: -- buy more disk space -- it's rather cheap nowadays. -- Keep less data by periodically purging old data. -- Reevaluate your design to see if a portion of the data is better not being saved. -- (see #3 below) If you want sqlite to return an error and refuse to add too much data, perhaps you want to look at: PRAGMA max_page_count = N; 2. Sqlite and RAM.. Again, I may not understand the question. The answer on one level is, "malloc -- and the programmer can replace the ram allocation routines easily if needbe." Another type of answer would be to ask if you've reviewed: http://www.sqlite.org/pragma.html#modify PRAGMA cache_size; PRAGMA default_cache_size; and, while you're at it: PRAGMA page_size; Are you in an embedded environment (e.g. cellphone, mp3 player)? If so, there are some additional considerations. 3. Regarding: "Are there any ways to compress db files in disk (for free)..." I don't think you mentioned what operating system you're using, but: -- For Windows, you can simply set the COMPRESSED attribute on the database (or perhaps it's entire folder if you want to compress the journal files as well). This is "free" (once you've bought Window$) but will carry a little performance penalty MOST of the time, unless the reduction in disk movement with the compressed database outweighs the added CPU time. It's very easy to test, though. -- For *nix there may well be some similar "application transparent" disk compression arrangements -- I don't really know. -- There are various free program source code options for compressing a column before your write it to disk, then decompressing it when you retrieve it. You can do this in your application (what language??) or you can add a function right into sqlite. Indexed columns, of course, need to remain in the clear. Maybe this helps, Donald _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
