Speed.html

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Speed.html

Steven Van Ingelgem
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]



Reactions would be nice too :)


 
Greetings,
Steven
Reply | Threaded
Open this post in threaded view
|

Re: Speed.html

Robert Simpson
----- Original Message -----
From: "Steven Van Ingelgem" <[hidden email]>
To: <[hidden email]>
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


Reply | Threaded
Open this post in threaded view
|

Re: Speed.html

D. Richard Hipp
In reply to this post by Steven Van Ingelgem
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 <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Speed.html

D. Richard Hipp
In reply to this post by Steven Van Ingelgem
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 <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Speed.html

D. Richard Hipp
In reply to this post by Steven Van Ingelgem
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 <[hidden email]>