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

[Richard Gaskin]

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