search

tehhowch / apps-script

A collection of files used by Google Apps Script to develop and serve data via Google Sheets
0 stars 0 forks source link

Make maintenance functions resumable #4

Closed tehhowch closed 6 years ago

tehhowch commented 7 years ago

As the database grows in size, eventually it will take 6 minutes to identify which record pairs should be kept, 6 minutes to retrieve those record pairs' ROWIDs, and 6 minutes to upload the new data to FusionTables. Right now, everything fails with uncertain outcome if the total duration for all 3 operations is 6 minutes.

tehhowch commented 6 years ago

This is resolved by using a smarter duplicate check. Best would be to use a builtin which guarantees uniqueness (e.g. Set()) but Google Apps Script is currently JS 1.6 with no stated plans to upgrade. Previous check used indexOf on the entire array (which is technically correct), but gets really expensive as the array grows (and grow it does... to > 100k elements). 4 calls to bigArray.indexOf are replaced with 4 calls to tinyArray, where tinyArray is at most 3 elements - the unique rowid values for each individual's particular crown change date - min seen, max seen, min rank, max rank.

Now the only plague for the maintenance functions is #7