Performance issues associated with IMPORTRANGE() use (?)

[Kirk_Masden]

For several years now, I've been working on a flashcard app for language learners.  One feature is access to several databases of pre-existing flashcards that can be copied into the app.   On the spreadsheet side, I use IMPORTRANGE() to pull in the flashcard data.  It seems to work pretty well but today I'd like to ask about some performance concerns I have.  Depending on the selection made by the user, the IMPORTRANGE() expression will pull in anywhere from a few dozen records to many thousands of records.  I assume that pulling in many thousands of records has the potential to cause some performance issues because the app is already quite big.  I'm not worried about newer, more powerful devices but older, less powerful ones may wind up having problems.

My idea is to suggest that users who experience performance issues try setting their apps to the smallest database.  This will not change the number of cells in the sheet but it will change the number of rows with valid keys.

Here are my questions:

1. Am I correct to assume that setting the IMPORTRANGE() expression to pull in a smaller set of data would lighten the load and improve performance, even with the same number of cells?
2. Do I need to use a security filter or anything to get the most out of switching to a smaller database?

Thanks!

P.S.  I just noticed that we don't have a "Performance" label.  Shouldn't we?

[1minManager]

---

@Kirk_Masden wrote:

Am I correct to assume that setting the IMPORTRANGE() expression to pull in a smaller set of data would lighten the load and improve performance, even with the same number of cells?

---

So am I correct you're using importrange() such that you change a value in the app, ImportRange() updates, then you either sync the App or run a bot on this updated data?

If not, then ImportRange() should have no effect on the performace.  As it related purely to Google  Sheets.

---

@Kirk_Masden wrote:

Do I need to use a security filter or anything to get the most out of switching to a smaller database?

---

Yes.  Securty filters restrict the table size that is synced and virtual columns are calculated from.  A slice sort of just hides data.

One to consider is that if your using the Enterprice Plan and you set a table to read only you can then also set "Server caching interval" to 1day.  This stops that table getting synced every 5mins.

Simon, 1minManager.com

[Kirk_Masden]

Thanks for this excellent reply.  

---

@1minManager wrote:

So am I correct you're using importrange() such that you change a value in the app, ImportRange() updates, then you either sync the App or run a bot on this updated data?

---

Yes.   For most users, the data pulled in by the IMPORTRANGE() formula would not change frequently -- they would merely have the potential to change it.  My concern is the impact of holding thousands of records in the memory, not so much the time required to read that in to the memory.  Is it the case that holding many, many records in the apps memory might make it more likely to crash (restart) on smaller, older devices?  If so, can this be mitigated but selecting a smaller database?

---

@1minManager wrote:

Securty filters restrict the table size that is synced and virtual columns are calculated from.  A slice sort of just hides data.

---

I should clarify my question about security filters.  If, after using a very large database, the user chooses a smaller one, there will still be thousands of empty rows in the spreadsheet.  As far as the performance of the app is concerned, do I need to use security filters or anything to make sure that the app is ignoring the blank rows?

[1minManager · Accepted solution]

---

@Kirk_Masden wrote:

Is it the case that holding many, many records in the apps memory might make it more likely to crash (restart) on smaller, older devices?  If so, can this be mitigated but selecting a smaller database?

---

As the App gets bigger, firstly you see sync times increase.  Then you'll get reports of syncs failing on mobile deivces.  Because their data is slower and it times out before it completes.  Then you gets a few times where doing a Save & Verify fails because there are so many things for it to check.  Then if you really try hard you can even get it to fail to save.  I only know because I've been through all those stages 🤣

To speed up an App, in order of action, my quick 20sec summary would be:

• Make sure all performance settings are turned on
• Use the bar graph in Performance Analyzer to find the slowest part of the app and try to make it more efficent or get rid of it
• Remove all virtual columns.  If needed have it run an action when you save a form that mirrors what the virtual column did
• Sometimes slices seem to be more efficent at generating tables of data than virtual columns
• Move any calculations into Bot template files if they aren't needed in the app
• Use security filters to limit the data if your on a suitable licence.  Otherwise create a bot that copies data to another spreadsheet
• Delete rows and columns you don't need

Blank rows do take time for the app to read.  But I think it probably adds a couple hundreds of secs to the sync time.  Having said that I always delete any extra columns and the same with rows where i know the App won't be adding any more.

Hope this helps 🙂

[Kirk_Masden]

Thanks!  I think this gives me what I need to know to proceed.  Much appreciated!!

P.S. The number of rows in the table that accepts the IMPORTRANGE() data will wind up being as large as the biggest set of data that gets imported.  I'll experiment with setting big and small sets of data via the IMPORTRANGE() line (but without changing the number of rows manually) and report back if I can detect a difference in performance.  🙂