I'm not sure how much we can do about preventing adds and deletes. It *may* be possible to replace them with updates, but I am not sure yet. These adds and deletes are happening in a different table than the one being summed. This other table contains a large blob column. Would changing to updates help or will updates fragment the database as much as adds and deletes?
The second option is the one I am considering. It looks like there might be a good time to run vacuum. I need to do some more timings to tell for sure. Thanks for the suggestions. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Tuesday, March 03, 2009 7:41 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Slow performance with Sum function On Tue, Mar 3, 2009 at 6:36 PM, Greg Palmer <gregorylpal...@netscape.net> wrote: > Trainor, Chris wrote: >> After running vacuum, sum is fast on the original table. However, >> running vacuum took a long time, so I'm not sure if that is a feasible >> solution. Is there any way to prevent fragmentation in the first place? >> If not for the whole database, then for a specific table? (e.g. is it >> possible to preallocate space for a table?) >> >> Thanks >> > I'm not an expert on SQLite but generally speaking fragmentation in a > database is usually a result of records being added and deleted. Are you > doing a lot of these and if so, can you change your algorithm to cut > down on this activity? exactly the right approach. Even better, make your application do the vaccuming when your users are away, much like housekeeping in a hotel. > > Regards, > Greg > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users The information contained in this email message and its attachments is intended only for the private and confidential use of the recipient(s) named above, unless the sender expressly agrees otherwise. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws by means of email or in an attachment to email you must select a more secure alternate means of transmittal that supports your obligations to protect such personal data. If the reader of this message is not the intended recipient and/or you have received this email in error, you must take no action based on the information in this email and you are hereby notified that any dissemination, misuse, copying, or disclosure of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email and delete the original message. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
