Database syntax

[Ruslan Zasukhin]

On 12/21/11 1:44 AM, "Bernard Devlin" <bdrunrev at gmail.com> wrote:

Hi Bernard,
Hi All,

1) Look guys. DATABASE must be used when

A) it is much bigger of RAM ...
B) you want persistence of data
C) you have complex structures of data (tables, links, )
D) You need kind of client/server
E) You want use different API to acess DATA.
        E.g. From LiveCode and PHP and NET and iOS.

It is clear that if you try stick to REV arrays somehow, say goodbye to PHP
access for example.

 ++ DATABASE gives ANOTHER abstraction LAYER ++


2)  WE all remember, yet 1990-95 years when computers was with
            ONE MEGABYTE of RAM

But dbs even in that years was many megabytes and even Gbs sometimes.

Yes, today RAM can be easy 4-8-16GB ...
Yes for many apps this is more than enough ...
So some people can try drop DB usage ..


3) But as for me ... When I start describe some model of some app, and I see
    Person, Phone, Group, Account, Invoice,  ...
    And yet 50-100 objects/tables ... And x3 - x5 more links between them

You will put that into INI file ???
Into txt file ???
Into Array ???

Never ... Database World already 40-50 years develop first of all
        DATA MODELs

    Navigational, Relational, Object-Relational,  pure OO


4) I think I never have relate speed of SQLite and memory usage ...
Not sure what you mean ...

When we did benches of Valentina against of others RDBMS,
We always give same cache size for example.

Benches clear did show, that wile DB is small, and is much less of RAM and
given cache, even FileMaker could be faster enough ... Really nothing hard
todo here ... But when db grow to some SizeCritical  then happens Oops,
Immediate degradation in speed because HDD dances.

What benches have show yet 15 years ago, is that thanks to vertical storage
format of tables, and other format of indexes, Valentina DB hasthis
SizeCritical significant higher ...

But right, if db is big then all db engines start dances with HDD.
And here becomes important another parameter:
    how speed go down while db grow. x10 more of RAM. x100 more of RAM.

Is it linear or worse or better?

Many issues here. Conditions change dynamically ...


> In my experience _no database_ will outperform arrays as data storage.
>  The simple reason is that a) arrays are stored in memory b)
> 
> Of course, if you run out of memory, you are in dangerous territory
> anyway.  If you read some of the arguments of Ruslan (of Valentina
> fame), even he admits that native in-memory data structures are faster
> than database access.  And he usually does that whilst he's in the
> process of arguing that sqlite is much slower than Valentina.  In
> following those arguments, I've seen Ruslan argue that sqlite's speedy
> performance comes at the cost of high memory usage (he argues
> Valentina performs faster, and is designed to only use small amounds
> of memory).
> 
> Think about it: if you are setting the dgData of a Datagrid to an
> array of 1,000 items, if you are having to get that data from a
> disk-based database before putting it into the dgData, then that is
> going to be slower.  The only way in which things could be faster,
> would be if the Datagrid was only asking for data as it was
> scrolled/sorted.
> 
> If you have a lot of blob data to display, and the aggregated size of
> that data is more than the memory you have available, then you are
> going to have to bring it in from disk.  Whether or not it is faster
> to do that than to store it on the filesystem (with your 1,000 member
> array just containing the path to the binary data), I do not know.
> 
> My experiments some years ago using millions of rows of numeric data,
> demonstrated to me that nothing available to Livecode except its own
> arrays really proved a suitably fast datastore (I compared it with
> in-memory databases, accessing some high-cost commercial databases
> with in-memory tables too).
> 
> One thing that has not been brought out so far in this discussion is
> that when passing data to a SQL statement in Livecode, it is possible
> to have your data stored in an array with numbered keys e.g.
> myArray[1], myArray[2]... and then to pass that array's name to the
> Livecode SQL functions using :1, :2... to pass in the data.
> 
> Search the following page for this line of text: |  revExecuteSQL
> myID,"insert into mytable values(:1,:2,:1)","myArray"  |
> 
> http://docs.runrev.com/Command/revExecuteSQL
> 
> Bernard

-- 
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]