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