Re: [sqlite] how long should a single insert take?
Inserting 1000,001 records each with Autocommit off, I get
PRAGMA off: 11 wallclock secs (11.19 usr + 0.35 sys = 11.54 CPU)
PRAGMA normal: 14 wallclock secs (11.36 usr + 0.38 sys = 11.74 CPU)
PRAGMA full: 14 wallclock secs (11.41 usr + 0.37 sys = 11.78 CPU)
in other words, speed ranges from 0.011 ms to 0.014 ms per
transaction. My table is a simple "CREATE TABLE foo (a, b);" and my
INSERTs are simple
$sth->$dbh(prepare(qq{ INSERT INTO foo (a, b) VALUES (?, ?) });
for (0 .. 1000,000) {
$sth->execute( $_, rand($_) );
}
my resulting database with 3 million and 3 records is almost 10 Mb in size.
I am running this on Macbook Pro 2.33 Ghz core 2 duo with 2 Gb ram and
Tiger. This is DBD::SQLite 1.13 which is really SQLite 3.3.7 (I
believe).
On 1/5/07, Sean Payne <[EMAIL PROTECTED]> wrote:
Jay,
I think you are right - it's the syncing that's taking too long.
Turning that off speeds things up substantially (see below for
details). Turning it off works for my requirements as it would
require an OS crash to corrupt my db.
I wonder if this timing profile is characteristic of the OSX builds
or all builds... so if anyone has a similar test they would like to
share, I'm curious.
Details - Timings after manipulating synchronicity...
I just retested using different settings for the
PRAGMA synchronous flag (http://www.sqlite.org/pragma.html)
with
* PRAGMA synchronous = OFF: single inserts are now in the 2ms range!
* PRAGMA synchronous = NORMAL : inserts are 140 ms range
* PRAGMA synchronous= FULL : inserts are in the 200ms range
On 5-Jan-07, at 6:37 PM, Jay Sprenkle wrote:
> On 1/5/07, Sean Payne <[EMAIL PROTECTED]> wrote:
>> Hi,
>>
>> I'm new to the list and to SQLITE. I am using it as a backend for a
>> gui program I'm working on.
>>
>> However, I am measuring a single trivial insert using the C
>> sqlite3_exec as taking 190-200ms. I am not interested in bulk
>> transactions. Using a prepared insert doesn't seem to reduce the
>> time very much (to possibly 170ish ms). Select statements are nice
>> and fast taking less than 1ms.
>>
>> So my question is if this ~200ms range for single-inserts to be
>> expected? I am using sqlite 3.3.9 (and 3.3.8) on a 2.16 Intel Core
>> 2 duo OSX build.
>>
>> If this is to be expected, how do you make it more responsive? I'd
>> like the user to be able to press a button to get an insert and have
>> no noticeable delay (maybe 50ms?)
>
> That seems a bit long, though if it's flushing the data to disk and
> ensuring
> that it's written before returning, it's probably not unreasonable.
> I would be
> really surprised if any user would be able to see any difference
> between
> 50ms and 150ms when it's a single event. Human reaction time is
> generally
> somewhere about 100ms I thought.
>
>
>
>
> --
> The PixAddixImage Collector suite:
> http://groups-beta.google.com/group/pixaddix
>
> SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
> http://www.reddawn.net/~jsprenkl/Sqlite
>
> Cthulhu Bucks!
> http://www.cthulhubucks.com
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] how long should a single insert take?
Jay, I think you are right - it's the syncing that's taking too long. Turning that off speeds things up substantially (see below for details). Turning it off works for my requirements as it would require an OS crash to corrupt my db. I wonder if this timing profile is characteristic of the OSX builds or all builds... so if anyone has a similar test they would like to share, I'm curious. Details - Timings after manipulating synchronicity... I just retested using different settings for the PRAGMA synchronous flag (http://www.sqlite.org/pragma.html) with * PRAGMA synchronous = OFF: single inserts are now in the 2ms range! * PRAGMA synchronous = NORMAL : inserts are 140 ms range * PRAGMA synchronous= FULL : inserts are in the 200ms range On 5-Jan-07, at 6:37 PM, Jay Sprenkle wrote: On 1/5/07, Sean Payne <[EMAIL PROTECTED]> wrote: Hi, I'm new to the list and to SQLITE. I am using it as a backend for a gui program I'm working on. However, I am measuring a single trivial insert using the C sqlite3_exec as taking 190-200ms. I am not interested in bulk transactions. Using a prepared insert doesn't seem to reduce the time very much (to possibly 170ish ms). Select statements are nice and fast taking less than 1ms. So my question is if this ~200ms range for single-inserts to be expected? I am using sqlite 3.3.9 (and 3.3.8) on a 2.16 Intel Core 2 duo OSX build. If this is to be expected, how do you make it more responsive? I'd like the user to be able to press a button to get an insert and have no noticeable delay (maybe 50ms?) That seems a bit long, though if it's flushing the data to disk and ensuring that it's written before returning, it's probably not unreasonable. I would be really surprised if any user would be able to see any difference between 50ms and 150ms when it's a single event. Human reaction time is generally somewhere about 100ms I thought. -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how long should a single insert take?
You are experiencung the ACID feature of Sqlite. To insert faster group your inserts into a single transaction. Sean Payne wrote: Hi, I'm new to the list and to SQLITE. I am using it as a backend for a gui program I'm working on. However, I am measuring a single trivial insert using the C sqlite3_exec as taking 190-200ms. I am not interested in bulk transactions. Using a prepared insert doesn't seem to reduce the time very much (to possibly 170ish ms). Select statements are nice and fast taking less than 1ms. So my question is if this ~200ms range for single-inserts to be expected? I am using sqlite 3.3.9 (and 3.3.8) on a 2.16 Intel Core 2 duo OSX build. If this is to be expected, how do you make it more responsive? I'd like the user to be able to press a button to get an insert and have no noticeable delay (maybe 50ms?) Thanks, Sean - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] how long should a single insert take?
On 1/5/07, Sean Payne <[EMAIL PROTECTED]> wrote: Hi, I'm new to the list and to SQLITE. I am using it as a backend for a gui program I'm working on. However, I am measuring a single trivial insert using the C sqlite3_exec as taking 190-200ms. I am not interested in bulk transactions. Using a prepared insert doesn't seem to reduce the time very much (to possibly 170ish ms). Select statements are nice and fast taking less than 1ms. So my question is if this ~200ms range for single-inserts to be expected? I am using sqlite 3.3.9 (and 3.3.8) on a 2.16 Intel Core 2 duo OSX build. If this is to be expected, how do you make it more responsive? I'd like the user to be able to press a button to get an insert and have no noticeable delay (maybe 50ms?) That seems a bit long, though if it's flushing the data to disk and ensuring that it's written before returning, it's probably not unreasonable. I would be really surprised if any user would be able to see any difference between 50ms and 150ms when it's a single event. Human reaction time is generally somewhere about 100ms I thought. -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] how long should a single insert take?
Hi, I'm new to the list and to SQLITE. I am using it as a backend for a gui program I'm working on. However, I am measuring a single trivial insert using the C sqlite3_exec as taking 190-200ms. I am not interested in bulk transactions. Using a prepared insert doesn't seem to reduce the time very much (to possibly 170ish ms). Select statements are nice and fast taking less than 1ms. So my question is if this ~200ms range for single-inserts to be expected? I am using sqlite 3.3.9 (and 3.3.8) on a 2.16 Intel Core 2 duo OSX build. If this is to be expected, how do you make it more responsive? I'd like the user to be able to press a button to get an insert and have no noticeable delay (maybe 50ms?) Thanks, Sean - To unsubscribe, send email to [EMAIL PROTECTED] -
