uggestions.
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 Ha
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 version
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 ther
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 change
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 start
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 be
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 a
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: "Th
ehalf Of P 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 wrote:
> Ok, but you might have to run this a bit longer (or more iterations).
I
ginal 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 cu
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 tes
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 started the test
>
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 Kishor
> S
.
JP
-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
users-boun...@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 t
om: sqlite-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 wrote:
> T
qlite] 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 th
I also wrote my own test in a small Qt/C++ app.
First I tried on my 10k rpm U160 SCSI drive, and it stayed roughly
between ~23 seconds per set (one set is 100 of: start
transaction->insert 1000->commit, as described). I let it run for
about 10 minutes before stopping it. It's hard drive bound on t
ete everything in my test). Maybe the 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. Creec
abase
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 oc
gt; [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] Database inserts gradually slowing down
>
> On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick wro
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 th
t; -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] Database inserts graduall
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,
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 wrote:
>
> Hmmm ...
>
> Is there a way to replace st
On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick wrote:
>
> Hmmm ...
>
> Is there a way to replace step 5 (delete everything from the table)
> with a System-Level rm / unlink and a sqlite CREATE TABLE ?
>
This shouldn't be necessary unless the table has triggers. From
http://sqlite.org/lang_de
inted 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 20
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
> Subject: Re: [sqlite] Database inserts gradually slowing down
&
n 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
>>
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 CPU bound).
>
> But t
-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
>
> Remem
s-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 implications of Moore's law and how much time has passed.
CPU speed is much faster than memory spee
cution 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:
ll 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: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down
How do you do
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 wrote:
> Hi,
>
> Can you please let me know which version of sqlite, are you using?
>
> Thank you.
> -Hiral
&g
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 and have found that
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 Hafsteinsson <
> j...@lsretail.com>
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 new to sqlite I might
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 co
39 matches
Mail list logo