Database syntax

[Bernard Devlin]

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

On Tue, Dec 20, 2011 at 10:49 PM, Michael Doub <mike at doub.com> wrote:
> Bob, this is a really interesting question.   I am not sure.    I was researching this but got
> side tracked when I found that the performance of the datagrid when driven by a 1000 entry
> dgData array did not perform well.  I switched to the using a database at that point as
> I felt that I did not have much of a choice as performance trumps memory in my mind.
>
> -= Mike
>
>
>
>>
>> Are arrays on mobile devices more memory intensive than an sqLite database?
>>
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode