Re: [sqlite] Speed.html

[D. Richard Hipp]

I just ran a speed comparison between version 2.8.16 and 3.2.3.
Version 3.2.3 is faster in almost every case.  See

  http://www.sqlite.org/speed-2816-v-323.html

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Speed.html

[D. Richard Hipp]

On Tue, 2005-08-23 at 17:36 +0200, Steven Van Ingelgem wrote:
> Hi, 
>  
> I just redid the speedtest.tcl-script in PHP 
> (http://www.karels0ft.be/tmp/speed_script.html).
> 
> Can  anyone check why the SQLite3's are so slow compared to SQLite2's ?
> 
> 

Aha!  Probably you are using an older test script that tries
to run of synchronous using

   PRAGMA default_synchronous=OFF;

The "default_synchronous" pragma was dropped form SQLite 3
because it was considered too dangerous.  There was too much
risk of data loss and database corruption after a power
failure.  Thus the synchronous behavior is never being
disabled in your tests and you are reporting synchronous
times in the "no-sync" column.

Another thing to note is that SQLite3 has

   PRAGMA synchronous=FULL

by default whereas version 2 had synchronous=ON by default.
FULL is a little slower to commit, but it is also safer in
the face of power failures.  In spite of this handicap,
SQLite version 3 still manages to be faster than version 2
in many tests.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Speed.html

[D. Richard Hipp]

On Tue, 2005-08-23 at 17:36 +0200, Steven Van Ingelgem wrote:
> Hi, 
>  
> I just redid the speedtest.tcl-script in PHP 
> (http://www.karels0ft.be/tmp/speed_script.html).
> 
> Can  anyone check why the SQLite3's are so slow compared to SQLite2's ?
> 
> 

Most of the important SQLite3 times are faster than SQLite2's.
What times are you specifically concerned about?

Perhaps you are concerned about Test1 with SQLite3 set to
no-sync.  It appears that the no-sync is not working.  I'll
look into it.

On the other hand, you should always leave synchronization 
enabled to prevent data loss after a power failure.  So this
is not a real high priority.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Speed.html

[Robert Simpson]

- Original Message - 
From: "Steven Van Ingelgem" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, August 23, 2005 8:36 AM
Subject: [sqlite] Speed.html



Hi,

I just redid the speedtest.tcl-script in PHP
(http://www.karels0ft.be/tmp/speed_script.html).

Can  anyone check why the SQLite3's are so slow compared to
SQLite2's ?


This script maybe could be used by someone with too much time to
check up the timings of the latest versions, to recreate the
speed.html-document, as it's a little outdated.
[http://www.karels0ft.be/tmp/speed.html]


To be complete, you should include prepared statement execution tests in 
your speed comparisons.  Most modern databases are designed around a 
prepared statement/parameterized execution model, and you're not getting 
ideal performance when you're inserting or updating thousands and thousands 
of rows by manufacturing an INSERT/UPDATE statement for each row.


Even your select tests would benefit from being prepared once and queried 
using parameters.


As an example, using a prepared statement and an integer parameter inside a 
transaction, I can insert about 100,000 rows a second into an indexed 
SQLite3 table using my ADO.NET 2.0 provider in C#.  (Computer is a 3.2ghz HT 
laptop running XP Media Center Ed.).  Times will of course drop when 
inserting strings or adding multiple parameters, but in theory prepared 
statements using parameters should always win over self-generated statements 
in all bulk op tests.


Robert