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 -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Tuesday, March 03, 2009 2:00 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Slow performance with Sum function Trainor, Chris <chris.trai...@ironmountain.com> wrote: > I am trying to use the Sum function on a column in a table with ~450K > rows in it. > > Select sum(Col4) from Table1 > > It takes over 2 minutes to execute when using the original table. I > created an exact copy of the table with the same indices and > constraints and inserted all the data from the original table into > it. Summing that column on the copied table only takes a few seconds. Try running VACUUM on your database. Your original table is probably badly fragmented and results in excessive disk seeking. Igor Tandetnik _______________________________________________ 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
