I would pick the method that's the easiest to implement and try it, first.
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.
Post by Richard Gaskin Post by Dr. Hawkins
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
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
Please visit this url to subscribe, unsubscribe and manage your