Speed up a slow loop

[Neville Smythe]

> On 9 Mar 2022, at 4:00 am, Alex Tweedly wrote:
> 
> I guess I'm not convinced about VM space thrashing being the problem, 
> certainly not due to the wordlist. It's 2.5 Mb - i.e. 0.02% of the real 
> RAM in a Pixel 5. There may be something else in the app making the 
> total VM space very large - but the wordlist wouldn't do it on its own.

I have to agree … although the relevant RAM size is the amount allocated to the app, not the total RAM. The OS presumably takes up most of the RAM by itself. But of course just a single copy of the list in memory is not a problem or Jacque wouldn’t be able to do the filtering.  Turning the list into an array will add indexing overhead very similar to what using a database would do (I suspect extremely similar in fact!) but I would have thought we are still only talking 10MB at most. Possibly more, the faster-than-light magic that happens with arrays must be trading off memory for cpu time somehow  The symptoms definitely sound to me like VM thrashing, and reducing the list to around 20K words evidently removes the issue, which suggests multiple copies of the array are being created somewhere in (Jacque’s original ) search algorithm.
> 
> 
>> An alternative is to use an sqlite database, always an option which should be considered when handling large data. This completely removes the issue of having the wordlist in memory, and at the same time provides an extremely fast search engine, so was worth exploring as a solution to both problems. Storing the words in a db single table with an index on initial letter and word length, or as lots of tables, one for each initial and length (!), both return a result for a search for a word in a small fraction of a millisecond, so definitely this would be a viable solution which would handle all letter distributions.
> Sounds cool. I'd like to see an example of how this would be created and 
> used (I'm very much a SQL novice).

I can’t claim to be an expert myself (see below) but it’s not hard and the LC tutorial together with online sqlite cheat sheets make it pretty straightforward. The hardest bit is getting into the db mindset which a bit different from procedural coding. The LC library makes it very easy to use sqlite in stacks. Creating the db from the data is easy,  it only needs some thought on what tables and columns are needed - just like designing a DataGrid in LC. Making simple queries of the db is plain language-like. For example to  find all words matching a crossword pattern - - T - - - -  containing N you execute a query such as

SELECT FROM words WHERE (length=7) AND (word LIKE ‘__T____’) AND (word LIKE ‘%N%’);

[ wildcard underscore _ matches a single character, % matches an arbitrary string … sound familiar? LIKE is used for comparing strings]

This outputs the 89 answers in a few milliseconds [most of which look to me like real words. But LETTERN ?? WITCHEN ?? SOWPODS really is ridiculous]

Making more complex queries is where things get arcane.

>> There is however a downside: both methods produce a db file size of 7.1 MB for the SOWPODS wordlist, which rather bloats the app footprint, even when you discount the text file version of the wordlist which no longer needs to be stored.
> You could do the opposite. Store the wordlist (700Kb compressed), and 
> decompress/populate into the SQLite database on initial run.

But unfortunately populating the database is very slow, taking minutes. [Note to myself: hey wait a minute, me, a database with only 275K rows is not that big in sqlite terms. Why is it taking so long? Because I am using a dumb repeat loop inserting one word at a time, akin to adding one word at a time to a field and then updating the UI. There is a better way. Physician, heal thyself!]

>> But now, speaking of databases, I have a question. I have an update to my nsScriptDatabase stack which I want to upload to the Sample Stacks. Because it really should be compiled to a standalone, I really need to upload a pair of stacks, a launch stack and one to hold data which can one modified by the user. But it would seem a sample stack must be a single item. What to do?
> 
> Yep -this is a big drawback of the Sample Stacks system.
> 
> Maybe you could make a single app, which contains the two desired stacks 
> as custom props.

Ah thank you, and a very interesting approach which I would never have thought of. Prompted me to think of another way: include the data stack as a substack when used in the IDE, but on openstack when not in the development environment clone the data stack to a writeable directory. That should work.

Neville