Upgrade to Lion -- why Valentina has columnar format ...

[Ruslan Zasukhin]

On 5/31/12 5:47 PM, "Richard Gaskin" <ambassador at fourthworld.com> wrote:

Hi Richard,

>> The sql statements were not that complex, just a LOT of toing and froing
>> between LC and the db. I soon abandoned SQLite as it was clear that
>> Valentina was getting the answers quicker.
> 
> Ruslan's genius is noteworthy, but perhaps the smartest decision he made
> with Valentina was to design it using a columnar data store.

Very wrong actually :-)

* Valentina is fast not only because of vertical format.
    we use for example not B-tree indexes but others.

* We are proud NOT by vertical format, although we was may be one of the
first in the world, that's right.

We ARE proud by Valentina Database Model - which we position as
      Object Relational Model

* I have start develop Valentina in 1993 NOT because I was going make
super-fast db, or columnar db.  No !!   :)

    REASON was -- because I have learn in that years OO-programming,
    and c++, and I have start work with dbs, and I was shocked by
    stupid state of things with relational model ...
    which still exists, 20 years later ...

    So I have start develop new db engine, which KNOWs and UNDERSTAND
    something more than just tables and fields.

* Idea of columnar format have come to mind about 3-4 years later,
And reason was NOT speed as many think. :-)

REASON of columnar format was idea how **Table Inheritance** must look.
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

* And yes, we still have not implement inheritance :-(((

    btw, all other attempts to make inheritance for DBs,
    which I have see are wrong (from my point of view).


> Columnar stores are radically different from row-based stores, such as
> most SQL-based implementations use.  For the relatively low cost of some
> additional overhead in updates, columnar stores allow optimized searches
> in ways that row-based system can rarely match.
 
> This page provides a good intro to the differences:
> <http://en.wikipedia.org/wiki/Column-oriented_DBMS#Benefits>

This is only may be HALF of truth :-)

Remember my words about  {INHERITANCE + COLUMN-format}
Future you will see why this is so important ...
 

> Additionally, the structure of an SQLite DB, particularly the indexing,
> can radically improve performance.

Not very right. 

Any db have indexing. And relational, and navigational, and columnar.
Nobody searches db without indexing :-)

But if compare to Valentina this point ...
    except indexes each db have one more HARD operation -- joins ...

Here play PK, FK keys,  their indexes, and so on ...
Valentina can win here cool also ...

> While it's unlikely that it could be
> optimized to beat Valentina, there may be opportunities to speed up the
> SQLite DB to be at least closer to it.

No ... 

Of course depends on queries ... But

If table weights e.g. 2 GB ...
For some operations a row-based DB NEED to load that 2GB
  at HDD speed 40Mb you get 50 seconds ...


For column db you may be lucky to load only e.g. 20-100Mb
So speed ONLY because of this can be 50-100 times faster.

But exists other factors also, which multiply each other at last of end ...



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]