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
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
