I don't know if it will help but I have a similar product in that it runs 24/7/365 and generates gobs of rows a day. What I did was create a new database for each month, like FA0901.bsa would be Jan 09. FA0810.bsa was Oct 08.
This allowed performance to stay really high, allowed for end-user grooming (just delete the old db files) and backup/restore was just as simple since the files were small, they would easily fit on a CD. When a query was done, all I did was create a temp database and insert data into it for each month and serve that database up. This made subqueries much faster since there wasn't 20 gazillion rows to munge thru. Granted, I did keep my database schema flat for so I could do this easily. I don't know if it will help you, but that's what I did. Good luck, Gene -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Pisati Sent: Tuesday, February 17, 2009 7:00 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Hundreds of thousands of INSERTs Igor Tandetnik wrote: > "Paolo Pisati" <p.pis...@oltrelinux.com> wrote > in message news:499a954e.5020...@oltrelinux.com > >> I've a perl script that reads rows from a file, does some processing >> and then insert the resulting data in a db, one INSERT per row. >> Usually the input file is made of hundreds of thousands, thus a >> typical run consists of hundreds of thousands of INSERTions. >> >> the performance was good until i decided to build indexes: >> > > Try creating indexes at the end, after all the inserts are done. > unfortunately, that's not an option for me: in the middle of all these inserts, someone could want to retrieve some data and without indexes the operation would be painfully slow - at minimum, this db has to hold around 14 days worth of logs, thus a select without indexes could take too long and block the remaining inserts. -- bye, P. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
