RE: [sqlite] Optimize performance - reading from multiple database files, processing and writing to separate results database file?
Hrm... I didn't realize that #define was limited to values less than 32. That being the case, you probably can't avoid starting multiple transactions. -Tom > -Original Message- > From: Serena Lien [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 04, 2006 9:57 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Optimize performance - reading from > multiple database files, processing and writing to separate > results database file? > > Yes, I could indeed determine which databases I want to > attach to, but there > is a limit to the number of databases you can attach (I think > it's 32?) and > I might have more than that. So I thought the safer route was > to attach as I > go.. > > Serena. > > > On 10/4/06, Tom Briggs <[EMAIL PROTECTED]> wrote: > > > > > >Do you know all of the databases that you want to attach > to at the > > start of processing? If so, there's no reason you can't > simply attach > > them all beforehand, start your transaction, and complete all your > > processing. If you have to decide dynamically, based on > the data, which > > databases you'll need, then you have no choice but to start and end > > multiple transactions in order to attach the databases as you go. > > > >-Tom > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimize performance - reading from multiple database files, processing and writing to separate results database file?
Yes, I could indeed determine which databases I want to attach to, but there is a limit to the number of databases you can attach (I think it's 32?) and I might have more than that. So I thought the safer route was to attach as I go.. Serena. On 10/4/06, Tom Briggs <[EMAIL PROTECTED]> wrote: Do you know all of the databases that you want to attach to at the start of processing? If so, there's no reason you can't simply attach them all beforehand, start your transaction, and complete all your processing. If you have to decide dynamically, based on the data, which databases you'll need, then you have no choice but to start and end multiple transactions in order to attach the databases as you go. -Tom
RE: [sqlite] Optimize performance - reading from multiple database files, processing and writing to separate results database file?
Do you know all of the databases that you want to attach to at the start of processing? If so, there's no reason you can't simply attach them all beforehand, start your transaction, and complete all your processing. If you have to decide dynamically, based on the data, which databases you'll need, then you have no choice but to start and end multiple transactions in order to attach the databases as you go. -Tom > -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] -
