Ada vs SQLite3 benchmark

Ada vs SQLite3 benchmark

[Dmitry A. Kazakov]

I posted benchmark of Ada persistent B-tree vs. SQLite3 at Ada Programming
blog:

http://ada-programming.blogspot.de/2014/09

The implementation of B-tree is based on Ada.Direct_IO with a transaction
layer, e.g. for safety against system failure.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Ada vs SQLite3 benchmark

[gdotone]

On Wednesday, September 17, 2014 12:52:51 PM UTC-4, Dmitry A. Kazakov wrote:
> I posted benchmark of Ada persistent B-tree vs. SQLite3 at Ada Programming

> blog: 
> http://ada-programming.blogspot.de/2014/09
> The implementation of B-tree is based on Ada.Direct_IO with a transaction
> layer, e.g. for safety against system failure.


amazing!

Re: Ada vs SQLite3 benchmark

[Jeffrey Carter]

On 09/17/2014 09:51 AM, Dmitry A. Kazakov wrote:
> I posted benchmark of Ada persistent B-tree vs. SQLite3 at Ada Programming
> blog:
> 
> http://ada-programming.blogspot.de/2014/09
> 
> The implementation of B-tree is based on Ada.Direct_IO with a transaction
> layer, e.g. for safety against system failure.

Is your B-tree/Direct_IO version task safe? Is your version of SQLite compiled
to be thread safe?

-- 
Jeff Carter
"Perfidious English mouse-dropping hoarders."
Monty Python & the Holy Grail
10

Re: Ada vs SQLite3 benchmark

[Georg Bauhaus]

On 17.09.14 18:51, Dmitry A. Kazakov wrote:
> http://ada-programming.blogspot.de/2014/09
>
> The implementation of B-tree is based on Ada.Direct_IO with a transaction
> layer, e.g. for safety against system failure.

What will the comparison look like if you let SQLite3 open the file
":memory:", thus an in-memory data base? Which will require to then
occasionally .dump its contents into another, persistent SQLite3 database.

http://www.sqlite.org/inmemorydb.html

Re: Ada vs SQLite3 benchmark

[Dmitry A. Kazakov]

On Thu, 18 Sep 2014 07:50:09 +0200, Georg Bauhaus wrote:

> On 17.09.14 18:51, Dmitry A. Kazakov wrote:
>> http://ada-programming.blogspot.de/2014/09
>>
>> The implementation of B-tree is based on Ada.Direct_IO with a transaction
>> layer, e.g. for safety against system failure.
> 
> What will the comparison look like if you let SQLite3 open the file
> ":memory:", thus an in-memory data base? Which will require to then
> occasionally .dump its contents into another, persistent SQLite3 database.
> 
> http://www.sqlite.org/inmemorydb.html

In would make no sense, IMO.

Let us consider the use case when everything fits into the memory. Why not
to use plain containers then?

Even if some sort of persistency is needed in this scenario, a persistency
layer can be added to a plain Ada container at *zero* performance cost.
E.g.

http://www.dmitry-kazakov.de/ada/components.htm#persistent_objects

I can tell without any benchmarks that Ada's standard containers or any
other would easily beat memory-mapped SQLite.

P.S. I had serious problems with the Kyoto Cabinet in the past. KC is a
single-file B-tree implementation written in C. It is not fail-safe, but
that was irrelevant to me. KC did what you described by OS means, it opened
the file as memory mapped. I was unaware of that until the DB grew 2GB,
then ... I am a bit allergic to memory-mapped files since.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Ada vs SQLite3 benchmark

[Dmitry A. Kazakov]

On Wed, 17 Sep 2014 17:16:35 -0700, Jeffrey Carter wrote:

> On 09/17/2014 09:51 AM, Dmitry A. Kazakov wrote:
>> I posted benchmark of Ada persistent B-tree vs. SQLite3 at Ada Programming
>> blog:
>> 
>> http://ada-programming.blogspot.de/2014/09
>> 
>> The implementation of B-tree is based on Ada.Direct_IO with a transaction
>> layer, e.g. for safety against system failure.
> 
> Is your B-tree/Direct_IO version task safe?

Yes. It uses a persistent memory pool on top of the file. The pool has a
protected object implementing a mutex and tree operations run with the
mutex taken.

> Is your version of SQLite compiled
> to be thread safe?

Should be as the SQLite DB is opened with the FULL_MUTEX option. The
documentation is somehow fuzzy about the effects of FULL_MUTEX, but I
suppose it should make it task-safe.

As far as I can tell the most time-consuming part of SQLite operations was
commit. Immutable operations are way quicker.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Ada vs SQLite3 benchmark

[Georg Bauhaus]

On 18.09.14 09:27, Dmitry A. Kazakov wrote:
> Let us consider the use case when everything fits into the memory. Why not
> to use plain containers then?

The operations needed by the application program are what can
set STL style containers apart from relational databases.
Whenever the operations do not call for the latter, I, too,
don't see a use case for an RDBMS in a small embedded system.

So, do people use SQLite3 as a hash table of records?

Re: Ada vs SQLite3 benchmark

[Dmitry A. Kazakov]

On Thu, 18 Sep 2014 09:56:15 +0200, Georg Bauhaus wrote:

> So, do people use SQLite3 as a hash table of records?

In my case it was. I never used relational queries with SQLite. All I
needed always was a persistent ordered map.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Ada vs SQLite3 benchmark

[briot.emmanuel]

Why did you run the benchmarks without optimization ? That seems inconsistent. If you are measuring performance, you should run with full optimization on I think.

Also, it would be interesting to use the following pragmas (combined or not) in sqlite, since they can impact performance significantly:
     pragma journal_mode=WAL;
     pragma synchronous=OFF;    (unless the Ada code is also running fsync() regularly)

I think the latter in particular will significantly change the time measured for sqlite.

But I agree with your conclusion that Ada is a viable alternative here, thanks for the experiment !

Re: Ada vs SQLite3 benchmark

[Marius Amado-Alves]

Outstanding!

Now we can make 100% Ada web systems.

But let's test more than 1000 records. At least 100 million.

Re: Ada vs SQLite3 benchmark

[Jacob Sparre Andersen]

Dmitry A. Kazakov wrote:

> I posted benchmark of Ada persistent B-tree vs. SQLite3 at Ada
> Programming blog:
>
> http://ada-programming.blogspot.de/2014/09

Nice write-up.

One thing which I miss is some sort of error-bars on your measurements.

One argument for using SQLite, which you don't cover, is the command
line tool for inspecting a SQLite database.  Have you considered how one
could generate something like that for your persisten B-tree library?

Thanks for the fine work!

Jacob
-- 
"if a person can't communicate,
 the very least he can do is to shut up!"

Re: Ada vs SQLite3 benchmark

[Pascal Obry]

Le mercredi 17 septembre 2014 à 18:51 +0200, Dmitry A. Kazakov a
écrit : 
> I posted benchmark of Ada persistent B-tree vs. SQLite3 at Ada Programming
> blog:
> 
> http://ada-programming.blogspot.de/2014/09
> 
> The implementation of B-tree is based on Ada.Direct_IO with a transaction
> layer, e.g. for safety against system failure.

Ok, but you don't have the full SQL language. For my v2p application I
do have some complex SQL transaction that would be difficult to
translate in plain Direct_IO call.

But if SQL is not needed your B-tree looks quite nice.

-- 
  Pascal Obry /  Magny Les Hameaux (78)

  The best way to travel is by means of imagination

  http://v2p.fr.eu.org
  http://www.obry.net

  gpg --keyserver keys.gnupg.net --recv-key F949BD3B

Re: Ada vs SQLite3 benchmark

[Dmitry A. Kazakov]

On Thu, 18 Sep 2014 15:41:25 +0200, Jacob Sparre Andersen wrote:

> Dmitry A. Kazakov wrote:
> 
>> I posted benchmark of Ada persistent B-tree vs. SQLite3 at Ada
>> Programming blog:
>>
>> http://ada-programming.blogspot.de/2014/09
> 
> Nice write-up.
> 
> One thing which I miss is some sort of error-bars on your measurements.

Yes, but I could not provide them. In the sense that of course I could
measure single operation duration within the loop, and accumulate minimum
and maximum times. But the maximum time would depend on the OS process
switching and the time slice etc. To remove these would be too complicated,
so I used only averaged time.

> One argument for using SQLite, which you don't cover, is the command
> line tool for inspecting a SQLite database.  Have you considered how one
> could generate something like that for your persisten B-tree library?

No, because it is not meant to be relational. You could have as many
B-trees allocated there as you wished plus allocated objects out of any
tree, as you would do by Ada "new T". So basically it lacks any predefined
structure. It is just a memory pool.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Ada vs SQLite3 benchmark

[Dmitry A. Kazakov]

On Thu, 18 Sep 2014 01:08:46 -0700 (PDT), briot.emmanuel@gmail.com wrote:

> Why did you run the benchmarks without optimization ? That seems
> inconsistent. If you are measuring performance, you should run with full
> optimization on I think.

Optimization could remove or rearrange parts of code which would not happen
in a real-life case. For example doing something like

   for I in 1..1000 loop
      N := I;
   end loop;

could be optimized to N := 1000.

IMO, not optimized code is a better measure for algorithmic complexity.
 
> Also, it would be interesting to use the following pragmas (combined or
> not) in sqlite, since they can impact performance significantly:
>      pragma journal_mode=WAL;
>      pragma synchronous=OFF;    (unless the Ada code is also running fsync() regularly)
>
> I think the latter in particular will significantly change the time measured for sqlite.

Thanks for pointing this.

Regarding Ada, it was strictly Ada.Direct_IO, nothing else. Ada.Direct_IO
does not have Flush [*]. As far as I can tell GNAT's implementation of
Ada.Direct_IO.Write is fwrite not followed by fsync. So forcing SQLite to
sync might be unfair. However, the intended use surely must sync upon
commit.

-----------
* Maybe it is worth an AI to add Flush to Direct_IO.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Ada vs SQLite3 benchmark

[Gour]

On Thu, 18 Sep 2014 19:09:31 +0200
Pascal Obry <pascal@obry.net> wrote:

> But if SQL is not needed your B-tree looks quite nice.

When we speak about it, what would be the sqlite3 bindings recommended
for use in Ada? Gnatcoll? Something else?


Sincerely,
Gour

-- 
In the material world, one who is unaffected by whatever good 
or evil he may obtain, neither praising it nor despising it, 
is firmly fixed in perfect knowledge.

Re: Ada vs SQLite3 benchmark

[Pascal Obry]

Le lundi 22 septembre 2014 à 17:43 +0200, Gour a écrit : 
> When we speak about it, what would be the sqlite3 bindings recommended
> for use in Ada? Gnatcoll? Something else?

GNATColl works fine with SQLite3. I'm using it in a project.

-- 
  Pascal Obry /  Magny Les Hameaux (78)

  The best way to travel is by means of imagination

  http://v2p.fr.eu.org
  http://www.obry.net

  gpg --keyserver keys.gnupg.net --recv-key F949BD3B

Re: Ada vs SQLite3 benchmark

[Dmitry A. Kazakov]

On Mon, 22 Sep 2014 17:43:13 +0200, Gour wrote:

> On Thu, 18 Sep 2014 19:09:31 +0200
> Pascal Obry <pascal@obry.net> wrote:
> 
>> But if SQL is not needed your B-tree looks quite nice.
> 
> When we speak about it, what would be the sqlite3 bindings recommended
> for use in Ada? Gnatcoll? Something else?

The benchmark uses SQLite3 bindings, obviously.

There exist many others. See:

http://en.wikibooks.org/wiki/Ada_Programming/Libraries/Database

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Ada vs SQLite3 benchmark

[Gour]

On Mon, 22 Sep 2014 18:08:11 +0200
Pascal Obry <pascal@obry.net> wrote:

> GNATColl works fine with SQLite3. I'm using it in a project.

Nice to hear. Thanks.


Sincerely,
Gour

-- 
He is a perfect yogī who, by comparison to his own self, 
sees the true equality of all beings, in both their 
happiness and their distress, O Arjuna!

Re: Ada vs SQLite3 benchmark

[Gour]

On Mon, 22 Sep 2014 18:29:23 +0200
"Dmitry A. Kazakov" <mailbox@dmitry-kazakov.de> wrote:

> The benchmark uses SQLite3 bindings, obviously.

Please excuse my (Ada) ignorance, but do you think about the ones from
Gnatcoll lib?

Btw, benchmark numbers are thought-provoking to consider pure Ada
option.  ;)


Sincerely,
Gour

-- 
One who restrains his senses, keeping them under full control, 
and fixes his consciousness upon Me, is known as a man of 
steady intelligence.

Re: Ada vs SQLite3 benchmark

[Dmitry A. Kazakov]

On Mon, 22 Sep 2014 19:02:46 +0200, Gour wrote:

> On Mon, 22 Sep 2014 18:29:23 +0200
> "Dmitry A. Kazakov" <mailbox@dmitry-kazakov.de> wrote:
> 
>> The benchmark uses SQLite3 bindings, obviously.
> 
> Please excuse my (Ada) ignorance, but do you think about the ones from
> Gnatcoll lib?

No. I used SQLite3 bindings from Simple Components:

http://www.dmitry-kazakov.de/ada/components.htm#SQLite

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: Ada vs SQLite3 benchmark

[Gour]

On Mon, 22 Sep 2014 21:09:57 +0200
"Dmitry A. Kazakov" <mailbox@dmitry-kazakov.de> wrote:

> No. I used SQLite3 bindings from Simple Components:
> 
> http://www.dmitry-kazakov.de/ada/components.htm#SQLite

Thank you. Many nice gems there, besides SQLite3 bindings. ;)


Sincerely,
Gour

-- 
He who is regulated in his habits of eating, sleeping, recreation 
and work can mitigate all material pains by practicing the yoga system.