relative performance of two dimensional array and in-memory sqlite database

[Mike Kerner]

I would pick the method that's the easiest to implement and try it, first.
I would rather just access the DB instead of trying to load everything into
containers, so I indexed the relevant fields, and have found that even for
tens-of-thousands of records, doing a compound LIKE (with wildcards) query
on multiple fields, it's giggle-fast.  If you're using mobile, everything
is technically in memory anyway, since your storage is on an SSD.


On Thu, Jul 25, 2013 at 11:32 AM, Richard Gaskin <ambassador at fourthworld.com
> wrote:

> Dr. Hawkins wrote:
>
>> Searching through the archives, I found what would have been my next
>> question.  The answer being that using ":memory:" as the filename
>> opens an sqlite database in memory rather than disk.
>>
>> That said, has anyone ever looked into the relative performance of
>> caching data into two-dimensional arrays, and into in-memory sqlite
>> when it comes time to search?
>>
>> If I want to do something, say, for all the rows in which "squidget >
>> 5", I could loop through ary[row][squidget] and check every one, or I
>> could "SELECT FROM table WHERE squidget>5 ORDER BY sqName".
>>
>> It would seem to make sense that sqlite would be better optimized for
>> such things than a loop.  (in fact, much of my data manipulation would
>> be easier in SQL with WHERE than in memory).
>>
>
> I've benchmarked various LC-based disk stores (simple indexed files) to
> SQLite on disk, but not in memory.
>
> Benchmark fetishist that I am, I would of course be interested in any
> definitive findings related to this.
>
> My hunch is that you'll find a lot of "depends", in which certain types of
> searches in certain sizes of colunns across certain ranges of record
> numbers are faster in SQLite, others faster with LC arrays, and some faster
> with simple chunk expressions.
>
> I'm frequently amazed at how well "repeat for each..." with collected
> results collated with "put...after" performs relative to alternatives.
>
> When we consider the various hash table jumps and offset moves that SQLite
> and arrays need to make to access data, this is perhaps less surprising.
>
> For single-access calls, arrays are hard to beat.  But for aggregate
> operations across an entire data set, like a search, "repeat for each..."
> performs quite admirably.
>
> That said, SQLite offers indexing options that can greatly reduce the
> search space, so for columns with high carindality I'd be surprised if any
> scripted solution could beat it.
>
> But if you need free text searches, I'd wager chunk expressions would
> perform roughly on par with SQLite, possibly besting it, in at least some
> cases (where lines aren't particularly long, or have more than say a couple
> dozen columns).
>
> --
>  Richard Gaskin
>  Fourth World
>  LiveCode training and consulting: http://www.fourthworld.com
>  Webzine for LiveCode developers: http://www.LiveCodeJournal.com
>  Follow me on Twitter:  http://twitter.com/**FourthWorldSys<http://twitter.com/FourthWorldSys>
>
>
> ______________________________**_________________
> 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<http://lists.runrev.com/mailman/listinfo/use-livecode>
>



-- 
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."