Re: [sqlite] Database inserts gradually slowing down

. Cheers, JP -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dennis Cote Sent: 19. júní 2009 04:43 To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down Jens Páll Hafsteinsson

Re: [sqlite] Database inserts gradually slowing down

Jens Páll Hafsteinsson wrote: > Here's the code I'm using to run the test (it includes the schema). I've been > running this code for the last few days and am a bit baffled about my recent > results, since I'm not seeing the gradual slowing anymore. This happened > after I changed to use

Re: [sqlite] Database inserts gradually slowing down

Jay A. Kreibich wrote: > On Wed, Jun 17, 2009 at 11:52:45AM +1000, John Machin scratched on the wall: > >> On 17/06/2009 6:17 AM, Hoover, Jeffrey wrote: >> >> >>> One other note, if you have a primary key whose value is continually >>> increasing your pk index can become imbalanced and

Re: [sqlite] Database inserts gradually slowing down

Here's the code I'm using to run the test (it includes the schema). I've been running this code for the last few days and am a bit baffled about my recent results, since I'm not seeing the gradual slowing anymore. This happened after I changed to use version 3.6.15 of sqlite and even if I

Re: [sqlite] Database inserts gradually slowing down

On 17/06/2009 11:52 AM, Dennis Cote wrote: > Jens Páll Hafsteinsson wrote: >> Closing and opening again did not speed up steps 1-4, it actually slowed >> things down even more. The curve from the beginning is a bit similar to a >> slightly flattened log curve. When I closed the database and

Re: [sqlite] Database inserts gradually slowing down

On Wed, Jun 17, 2009 at 11:52:45AM +1000, John Machin scratched on the wall: > On 17/06/2009 6:17 AM, Hoover, Jeffrey wrote: > > > One other note, if you have a primary key whose value is continually > > increasing your pk index can become imbalanced and therefore > > inefficient. > > A B-tree

Re: [sqlite] Database inserts gradually slowing down

Jens Páll Hafsteinsson wrote: > Closing and opening again did not speed up steps 1-4, it actually slowed > things down even more. The curve from the beginning is a bit similar to a > slightly flattened log curve. When I closed the database and started the test > again, a similar curve appeared

Re: [sqlite] Database inserts gradually slowing down

On 17/06/2009 6:17 AM, Hoover, Jeffrey wrote: > One other note, if you have a primary key whose value is continually > increasing your pk index can become imbalanced and therefore > inefficient. A B-tree becomes imbalanced? How so? http://www.sqlite.org/fileformat.html#btree_structures says:

Re: [sqlite] Database inserts gradually slowing down

Kishor Sent: Tuesday, June 16, 2009 12:29 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down On Tue, Jun 16, 2009 at 1:59 PM, Jens Páll Hafsteinsson<j...@lsretail.com> wrote: > Ok, but you might have to run this a bit longer (or more i

Re: [sqlite] Database inserts gradually slowing down

Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wes Freeman Sent: Tuesday, June 16, 2009 4:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down VACUUM cleaned up the file in my current

Re: [sqlite] Database inserts gradually slowing down

VACUUM cleaned up the file in my current test, after 1200 iterations, making it run at ~4.6seconds again, rather than ~5.1. It seemed to get it almost back to the performance of a clean file. Didn't know about the vacuum command--Cool. By the way, the vacuum operation takes ~1.6 seconds for my

Re: [sqlite] Database inserts gradually slowing down

On Tue, Jun 16, 2009 at 2:51 PM, Jens Páll Hafsteinsson wrote: > Closing and opening again did not speed up steps 1-4, it actually slowed > things down even more. The curve from the beginning is a bit similar to a > slightly flattened log curve. When I closed the database and

Re: [sqlite] Database inserts gradually slowing down

    29,807 > 8       31,944 > 9       32,422 > 10      34,144 > > Increasing like crazy (but seems to level off later at 40 seconds). > > JP > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P

Re: [sqlite] Database inserts gradually slowing down

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wes Freeman Sent: 16. júní 2009 18:01 To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down I also wrote my own test in a small Qt/C

Re: [sqlite] Database inserts gradually slowing down

...@sqlite.org] On Behalf Of Konrad J Hambrick Sent: 16. júní 2009 17:43 To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down Jens -- I have seen Index and Record Fragmentation cause the kind of slowdowns you are describing. Especially when

Re: [sqlite] Database inserts gradually slowing down

-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: 16. júní 2009 16:55 To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down On Tue, Jun 16, 2009 at 10:57 AM, Jens Páll Hafsteinsson<j...@lsretail.

Re: [sqlite] Database inserts gradually slowing down

inserts gradually slowing down On 16 Jun 2009, at 1:06pm, Jens Páll Hafsteinsson wrote: > 1. start a transaction > > 2. insert 1000 records > > 3. commit > > 4. repeat steps 1-3 100 times > > 5. delete everything from the table > &g

Re: [sqlite] Database inserts gradually slowing down

he culprit is >> that I delete everything from the table? >> >> JP >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Matthew L. Creech >> Sent: 16. júní 2009 15:51 &g

Re: [sqlite] Database inserts gradually slowing down

Subject: Re: [sqlite] Database inserts gradually slowing down Jens -- I have seen Index and Record Fragmentation cause the kind of slowdowns you are describing. Especially when there are a lot deletes followed by a 'largish' (100,000-record) batch of inserts. I have found on occasion

Re: [sqlite] Database inserts gradually slowing down

boun...@sqlite.org] On Behalf Of Matthew L. Creech > Sent: 16. júní 2009 15:51 > To: kon...@payplus.com; General Discussion of SQLite Database > Subject: Re: [sqlite] Database inserts gradually slowing down > > On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick<kon...@payplus.com> wr

Re: [sqlite] Database inserts gradually slowing down

On 16 Jun 2009, at 4:31pm, Konrad J Hambrick wrote: > Is there a way to replace step 5 (delete everything from the table) > with a System-Level rm / unlink and a sqlite CREATE TABLE ? JP can totally do that, but his original question was why the system gradually gets slower and slower. and

Re: [sqlite] Database inserts gradually slowing down

able? > > JP > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Matthew L. Creech > Sent: 16. júní 2009 15:51 > To: kon...@payplus.com; General Discussion of SQLite Database > Subject: Re: [sqlite

Re: [sqlite] Database inserts gradually slowing down

On 16 Jun 2009, at 1:06pm, Jens Páll Hafsteinsson wrote: > 1. start a transaction > > 2. insert 1000 records > > 3. commit > > 4. repeat steps 1-3 100 times > > 5. delete everything from the table > > 6. Start again at step 1 Okay, so do that until it's slow,

Re: [sqlite] Database inserts gradually slowing down

] On Behalf Of Matthew L. Creech Sent: 16. júní 2009 15:51 To: kon...@payplus.com; General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick<kon...@payplus.com> wrote: > > Hmmm ... > > Is ther

Re: [sqlite] Database inserts gradually slowing down

out to me. Thanks again. JP -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kees Nuyt Sent: 16. júní 2009 15:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Database inserts gradually slowing down On Tue, 16 Jun 2009 14

Re: [sqlite] Database inserts gradually slowing down

gt; > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita > Sent: 16. júní 2009 14:06 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Database inserts gradually slowing down > >

Re: [sqlite] Database inserts gradually slowing down

it shouldn't. >> >> JP >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita >> Sent: 16. júní 2009 14:06 >> To: General Discussion of SQLite Database >> S

Re: [sqlite] Database inserts gradually slowing down

On Tue, 16 Jun 2009 14:23:47 +, Jens Páll Hafsteinsson wrote: > Yes, I'm perfectly aware of this and hence I > would expect the disk to be sweating like hell > running this test while the CPU is relatively > relaxed (given that sqlite is disk bound in > this case and not

Re: [sqlite] Database inserts gradually slowing down

nal Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita > Sent: 16. júní 2009 14:06 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Database inserts gradually slowing down > > Remember the im

Re: [sqlite] Database inserts gradually slowing down

] On Behalf Of Adam DeVita Sent: 16. júní 2009 14:06 To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down Remember the implications of Moore's law and how much time has passed. CPU speed is much faster than memory speed. Memory speed is much faster

Re: [sqlite] Database inserts gradually slowing down

tion time. I > want to run this a bit longer to be absolutely sure and will let you know. > > JP > > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: 16. júní 2009 12:15 > To:

Re: [sqlite] Database inserts gradually slowing down

--Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: 16. júní 2009 12:15 To: General Discussion of SQLite Database Subject: Re: [sqlite] Database inserts gradually slowing down How do you do step 5? Like "de

Re: [sqlite] Database inserts gradually slowing down

Subject: Re: [sqlite] Database inserts gradually slowing down Sorry I got it. Did you tried with 3.6.14.2. Thank you. -Hiral On Tue, Jun 16, 2009 at 5:39 PM, hiral <hiralsmaill...@gmail.com> wrote: > Hi, > > Can you please let me know which version of sqlite, are you using

Re: [sqlite] Database inserts gradually slowing down

How do you do step 5? Like "delete from table" or "delete from table where ..."? Do you see any degrade in disk space used by database along with slowness? Pavel On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll Hafsteinsson wrote: > Hi > > I've been running some tests against sqlite

Re: [sqlite] Database inserts gradually slowing down

Sorry I got it. Did you tried with 3.6.14.2. Thank you. -Hiral On Tue, Jun 16, 2009 at 5:39 PM, hiral wrote: > Hi, > > Can you please let me know which version of sqlite, are you using? > > Thank you. > -Hiral > > On Tue, Jun 16, 2009 at 5:36 PM, Jens Páll

Re: [sqlite] Database inserts gradually slowing down

Hi, Can you please let me know which version of sqlite, are you using? Thank you. -Hiral On Tue, Jun 16, 2009 at 5:36 PM, Jens Páll Hafsteinsson wrote: > Hi > > I've been running some tests against sqlite and have found that inserts are > gradually slowing down. Since I'm

[sqlite] Database inserts gradually slowing down

Hi I've been running some tests against sqlite and have found that inserts are gradually slowing down. Since I'm new to sqlite I might be doing something wrong and thought I'd ask here if anyone has seen this before or know what might be causing this. The test I'm running creates a database