Hi Serena, I hope that more expert users will chime in here, but my initial thoughts were:
-- 1 Gbyte is not at all too big for Sqlite to handle in one file. -- Some portion (large??) of the performance advantages of transactions will be lost in opening/closing files, even if transactions were allowed across multiple files. -- The other advantages of transactions, such as atomicity, may be important to your application. For these reasons, might you consider using a single database rather than multiple files if you're up against performance bottlenecks? [opinions are my own, not my company's] -----Original Message----- From: Serena Lien [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 04, 2006 8:59 AM To: sqlite-users@sqlite.org Subject: [sqlite] Optimize performance - reading from multiple database files, processing and writing to separate results database file? Hello, I have many databases, all in separate files. I want to choose a subset of them based on some query, and read the input data one at a time, process it somehow and write the results to another database file. The problems are caused by: - May have a large number of inputs (eg > 32 databases to attach) - May be a lot of data (eg > 1GB total), too much to copy the sources into a table in an in-memory database, then processing each row in this table. - Don't particularly want to duplicate the source data, by copying sources into a temporary database on disk, unless there is no better method - Need to use transactions when inserting processed data into a table in the results database I have tried the following: - Attach results database - Begin transaction - Attach first source database and select its data - Process data and bind results for insertion into results db, call step - Detach source database and attach next source database, repeat etc - End transaction But this won't work, because trying to attach a database gives the error: Cannot attach database within transaction! But I really need transactions for performance because I am inserting a lot of data (more rows than I am reading from my inputs) I would appreciate any suggestions on how best to do this. Serena. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
