Re: [sqlite] Slow performance with Sum function

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Trainor, Chris Sent: Wednesday, March 11, 2009 5:31 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Slow performance with Sum function > Do not be temp

Re: [sqlite] Slow performance with Sum function

> Do not be tempted by the incremental vacuum feature. Incremental > vacuum will reduce the database size as content is deleted, but it > will not reduce fragmentation. In fact, incremental vacuum will > likely increase fragmentation. Incremental vacuum is just a variation > on

Re: [sqlite] Slow performance with Sum function

Hello! On Wednesday 04 March 2009 17:19:09 Jim Wilcoxson wrote: > Have you tried changing the page size to 4096 or 8192?  Doing this > with my SQLite application and increasing the transaction size > decreased runtime from over 4 hours to 75 minutes.    The runtime for > my app writing the same

Re: [sqlite] Slow performance with Sum function

Have you tried changing the page size to 4096 or 8192? Doing this with my SQLite application and increasing the transaction size decreased runtime from over 4 hours to 75 minutes.The runtime for my app writing the same amount of data to flat files was 55 minutes, so the time penalty for

Re: [sqlite] Slow performance with Sum function

Hello! On Wednesday 04 March 2009 04:44:05 D. Richard Hipp wrote: >  One could envision future versions   > of SQLite that allowed you to preallocate a large database files such   > that the database always stayed less than 80% full.  Then we could use   > filesystem techniques to keep

Re: [sqlite] Slow performance with Sum function

On Mar 3, 2009, at 8:01 PM, Trainor, Chris wrote: > 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.

Re: [sqlite] Slow performance with Sum function

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin Sent: Tuesday, March 03, 2009 7:53 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Slow performance with Sum function On 4/03/2009 5:52 AM, Trainor

Re: [sqlite] Slow performance with Sum function

: 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 <gregoryl

Re: [sqlite] Slow performance with Sum function

On 4/03/2009 5:52 AM, Trainor, Chris 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 > > Where Table1 looks like this: > > Create TABLE Table1 ( > Col1 INTEGER NOT NULL, > Col2 INTEGER NOT NULL, >

Re: [sqlite] Slow performance with Sum function

On Tue, Mar 3, 2009 at 6:36 PM, Greg Palmer 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

Re: [sqlite] Slow performance with Sum function

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?

Re: [sqlite] Slow performance with Sum function

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

Re: [sqlite] Slow performance with Sum function

Trainor, Chris 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

[sqlite] Slow performance with Sum function

I am trying to use the Sum function on a column in a table with ~450K rows in it. Select sum(Col4) from Table1 Where Table1 looks like this: Create TABLE Table1 ( Col1 INTEGER NOT NULL, Col2 INTEGER NOT NULL, Col3 INTEGER NOT NULL, Col4 BIGINT NOT NULL,