www.digitalmars.com         C & C++   DMDScript  

digitalmars.D - sqlite3 vs. sqlite-d

reply Stefan Koch <uplink.coder googlemail.com> writes:
Hi guys

I made a small video.
Mature and heavily optimized C library vs. young D upstart.

See for yourself how it turns out.

https://www.youtube.com/watch?v=mOeVftcVsvI

Cheers,
Stefan
Jun 07
next sibling parent reply Ozan <ozan.sueel gmail.com> writes:
On Wednesday, 7 June 2017 at 17:51:30 UTC, Stefan Koch wrote:
 Hi guys

 I made a small video.
 Mature and heavily optimized C library vs. young D upstart.

 See for yourself how it turns out.

 https://www.youtube.com/watch?v=mOeVftcVsvI

 Cheers,
 Stefan
Great. I like it (not the color of the terminal font - green!!! ;-) What is the cause for different benchmark results (from 30us up to 48us)? With your small number lines, do you implement the complete sqlite functionality? Regards, Ozan
Jun 07
parent reply Stefan Koch <uplink.coder googlemail.com> writes:
On Wednesday, 7 June 2017 at 19:10:26 UTC, Ozan wrote:
 On Wednesday, 7 June 2017 at 17:51:30 UTC, Stefan Koch wrote:
 Hi guys

 I made a small video.
 Mature and heavily optimized C library vs. young D upstart.

 See for yourself how it turns out.

 https://www.youtube.com/watch?v=mOeVftcVsvI

 Cheers,
 Stefan
Great. I like it (not the color of the terminal font - green!!! ;-) What is the cause for different benchmark results (from 30us up to 48us)? With your small number lines, do you implement the complete sqlite functionality? Regards, Ozan
It's the Matrix-Movie green ;) I only implement reading the file format. And a few convenience functions for finding a table, iterating by rows, and extracting columns. The cause for the different results it like the scheduling of the OS. Since we do issue quite a large read before iterating. most of our time-slice has been used. which makes it possible for us to get swapped out during processing.
Jun 07
parent reply Ozan (O/N/S) <ozan.sueel gmail.com> writes:
On Wednesday, 7 June 2017 at 19:16:07 UTC, Stefan Koch wrote:
 On Wednesday, 7 June 2017 at 19:10:26 UTC, Ozan wrote:
 On Wednesday, 7 June 2017 at 17:51:30 UTC, Stefan Koch wrote:
 Hi guys

 I made a small video.
 Mature and heavily optimized C library vs. young D upstart.

 See for yourself how it turns out.

 https://www.youtube.com/watch?v=mOeVftcVsvI

 Cheers,
 Stefan
Great. I like it (not the color of the terminal font - green!!! ;-) What is the cause for different benchmark results (from 30us up to 48us)? With your small number lines, do you implement the complete sqlite functionality? Regards, Ozan
It's the Matrix-Movie green ;) I only implement reading the file format. And a few convenience functions for finding a table, iterating by rows, and extracting columns. The cause for the different results it like the scheduling of the OS. Since we do issue quite a large read before iterating. most of our time-slice has been used. which makes it possible for us to get swapped out during processing.
I see, Matrix style. Red or blue pill, that's the question. ;-) Your sqlite-d solution would be complete if writing sqlite files are also possible. Ignore the SQL parsing stuff, it does not fit in a world of fast data processing. Regards Ozan
Jun 08
parent Stefan Koch <uplink.coder googlemail.com> writes:
On Thursday, 8 June 2017 at 13:06:55 UTC, Ozan (O/N/S) wrote:
 Your sqlite-d solution would be complete if writing sqlite 
 files are also possible. Ignore the SQL parsing stuff, it does 
 not fit in a world of fast data processing.
Writing or rather modifying sqlite-dbs is a bit harder then reading them. Since the B-Tree should not get to imbalanced. I started on write support a while back, but at that time I did not understand the structure well enough to guarantee efficient write-support. (which would also require you to keep the index updated and so on ...) As for parsing sql. Unfortunately the column information is stored in form of a create table statement which forces me to parse at-least that much sql. In order to provide a nice interface in which you don't have to look up the position of your columns manually.
Jun 08
prev sibling parent reply cym13 <cpicard openmailbox.org> writes:
On Wednesday, 7 June 2017 at 17:51:30 UTC, Stefan Koch wrote:
 Hi guys

 I made a small video.
 Mature and heavily optimized C library vs. young D upstart.

 See for yourself how it turns out.

 https://www.youtube.com/watch?v=mOeVftcVsvI

 Cheers,
 Stefan
It should be noted that the benchmark isn't fair, it favours the sqlite3 implementation as parsing and preparing the statement isn't measured. And yes, it's still faster which is cool ^^ Also for the lazy ones that like reading code: https://github.com/UplinkCoder/sqlite-d
Jun 07
parent reply Stefan Koch <uplink.coder googlemail.com> writes:
On Wednesday, 7 June 2017 at 20:12:22 UTC, cym13 wrote:

 It should be noted that the benchmark isn't fair, it favours 
 the sqlite3 implementation as parsing and preparing the 
 statement isn't measured. And yes, it's still faster which is 
 cool ^^
Yes the benchmark is biased slightly in favor of sqlite3 C implementation. But sqlite-d does not have the ability to parse sql to the point where it could implement that functionality. Also sqlite-d is inefficient in quite a few places and is slowed down by auto-decoding as I discovered just now.
Jun 07
next sibling parent "H. S. Teoh via Digitalmars-d" <digitalmars-d puremagic.com> writes:
On Wed, Jun 07, 2017 at 08:40:12PM +0000, Stefan Koch via Digitalmars-d wrote:
 [...]  Also sqlite-d is inefficient in quite a few places and is
 slowed down by auto-decoding as I discovered just now.
*dreams of a day when we can finally be free of auto-decoding...* T -- If you think you are too small to make a difference, try sleeping in a closed room with a mosquito. -- Jan van Steenbergen
Jun 07
prev sibling parent reply Russel Winder via Digitalmars-d <digitalmars-d puremagic.com> writes:
On Wed, 2017-06-07 at 20:40 +0000, Stefan Koch via Digitalmars-d wrote:
 On Wednesday, 7 June 2017 at 20:12:22 UTC, cym13 wrote:
=20
 It should be noted that the benchmark isn't fair, it favours=C2=A0
 the sqlite3 implementation as parsing and preparing the=C2=A0
 statement isn't measured. And yes, it's still faster which is=C2=A0
 cool ^^
=20
=20 Yes the benchmark is biased slightly in favor of sqlite3 C=C2=A0 implementation. But sqlite-d does not have the ability to parse sql to the point=C2=A0 where it could implement that functionality. Also sqlite-d is inefficient in quite a few places and is slowed=C2=A0 down by auto-decoding as I discovered just now.
But what is D's equivalent to Python's SQLAlchemy? C++ now has sqlpp11. Anyone doing SQL code manipulation with strings in another language is doing it wrong. Internal DSLs FTW. --=20 Russel. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D Dr Russel Winder t: +44 20 7585 2200 voip: sip:russel.winder ekiga.n= et 41 Buckmaster Road m: +44 7770 465 077 xmpp: russel winder.org.uk London SW11 1EN, UK w: www.russel.org.uk skype: russel_winder
Jun 08
next sibling parent reply Stefan Koch <uplink.coder googlemail.com> writes:
On Thursday, 8 June 2017 at 08:44:56 UTC, Russel Winder wrote:
 But what is D's equivalent to Python's SQLAlchemy? C++ now has 
 sqlpp11.

 Anyone doing SQL code manipulation with strings in another 
 language is doing it wrong. Internal DSLs FTW.
The Alternative is not doing SQL at all. But building the queries inside your code. Which is what sqlite-d allows you to do. and which is the reason I wrote it. I wanted to express my desires in code rather then having to log sql-blocks around.
Jun 08
parent reply Russel Winder via Digitalmars-d <digitalmars-d puremagic.com> writes:
On Thu, 2017-06-08 at 11:36 +0000, Stefan Koch via Digitalmars-d wrote:
=20
[=E2=80=A6]
 The Alternative is not doing SQL at all.
 But building the queries inside your code.
Exactly my point. Using SQLAlchemy made me actually enjoy writing database code. Which I did last year having avoided it since I was born.
 Which is what sqlite-d allows you to do.
 and which is the reason I wrote it.
=20
 I wanted to express my desires in code rather then having to log=C2=A0
 sql-blocks around.
Exactly. sqlpp11 made me think about doing database stuff for Me TV in C++ (currently there is a home grown ORM in the previous versions), but that I thought maybe there is something in D sqlite-d is not mentioned on https://wiki.dlang.org/Libraries_and_Frame works --=20 Russel. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D Dr Russel Winder t: +44 20 7585 2200 voip: sip:russel.winder ekiga.n= et 41 Buckmaster Road m: +44 7770 465 077 xmpp: russel winder.org.uk London SW11 1EN, UK w: www.russel.org.uk skype: russel_winder
Jun 08
parent reply Dejan Lekic <dejan.lekic gmail.com> writes:
On Thursday, 8 June 2017 at 13:37:41 UTC, Russel Winder wrote:

 Exactly my point. Using SQLAlchemy made me actually enjoy 
 writing database code. Which I did last year having avoided it
Using ORM like SQLAlchemy certainly has benefits but like any other ORM, it generates hideous SQL code, sometimes terribly slow...
Jun 08
parent Russel Winder via Digitalmars-d <digitalmars-d puremagic.com> writes:
On Thu, 2017-06-08 at 14:55 +0000, Dejan Lekic via Digitalmars-d wrote:
 On Thursday, 8 June 2017 at 13:37:41 UTC, Russel Winder wrote:
=20
 Exactly my point. Using SQLAlchemy made me actually enjoy=C2=A0
 writing database code. Which I did last year having avoided it
=20 Using ORM like SQLAlchemy certainly has benefits but like any=C2=A0 other ORM, it generates hideous SQL code, sometimes terribly=C2=A0 slow...
SQLAlchemy is not just an ORM, though there is an ORM, there is also the expression language which is an internal DSL for writing SQL. Also the authors of SQLAlchemy do optimise the generated SQL. Though obviously if you are needing the optimal dark corner optimisation of Oracle 11.2.0.4 say, then you are on your own. --=20 Russel. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D Dr Russel Winder t: +44 20 7585 2200 voip: sip:russel.winder ekiga.n= et 41 Buckmaster Road m: +44 7770 465 077 xmpp: russel winder.org.uk London SW11 1EN, UK w: www.russel.org.uk skype: russel_winder
Jun 08
prev sibling parent qznc <qznc web.de> writes:
On Thursday, 8 June 2017 at 08:44:56 UTC, Russel Winder wrote:
 But what is D's equivalent to Python's SQLAlchemy? C++ now has 
 sqlpp11.
There is HibernateD. http://code.dlang.org/packages/hibernated For my own project, I used handwritten SQL. It is still on my todo list to replace that with HibernateD. So, no practical experience, yet.
Jun 09