Dataset size question - 2 million rows

[Dale_Moir]

Hi Guys,

I’m looking into an Appsheet project that will require to connect to a MYSQL database and pull a table of potentially 2 million records. The searches will be different per request so a saved search wouldn’t work.

I have found this article:

help.appsheet.com

Limits on Data Size

How to design your app with consideration of caching and intermittent connectivity.

That seems to suggest a 5-10mb limit as the data is saved onto the device and has performance implications.

Is 2 million rows just completely out of the question? Some of the row data would be extremely long text values (HTML articles)

I’m thinking its just too much data and not a good fit for AppSheet but could do with some advice to be sure.

Thanks

Dale

[LeventK]

Dale_Moir:

That seems to suggest a 5-10mb limit as the data is saved onto the device and has performance implications.

Firstly, I believe those 5-10Mb file size limit is for spreadsheets, not for SQL database.

Dale_Moir:

Is 2 million rows just completely out of the question? Some of the row data would be extremely long text values (HTML articles)

MySQL has both a row character limit and a column character limit. The total row limit cannot exceed 65,536 bytes.
https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.7/en/column-count-limit.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html
Therefore I may advise controlling and ensuring your data limits on this aspect.
And displaying or having 2M rows of data on an AppSheet app is a bit forcing of app syncing and loadin times.

help.appsheet.com

Improving the Speed of Sync

Six different ways to improve sync performance.

[Dale_Moir]

Hi, thank you for your answer. The issue I think is that although one table may have 2 million records, there will also be other tables of varying size. The customers current solution is slow to return data hence the need to look for a replacement.

Also, the searches will be completely random. One minute it might be, “get all records from last 3 months matching x” or “get all records from today” but there isn’t any consistency.

I guess this means that within Appsheet, it would have to request the data from the database, pull it to appsheet and then push to the device and this would be happening a lot.

Additionally also, there is likely to be anything from 300-5k new records being added from the users per day.

Thanks again for your time

Dale

[LeventK]

You’re welcome. But with the given information and usage algorithm, ensure that if AppSheet is the right platform to use.

[Steve · Accepted solution]

Be advised that–by its fundamental design–AppSheet copies all data to the user’s device. When the user interacts with the data,they’re interacting with the copy of the data on the device, not with the data store directly. Consequently, the total size of the data set has a direct impact on the user experience when the app syncs with the data store, and the device’s memory and processing capabilities affect the app’s ability to handle the data set. Again, this is fundamental to AppSheet’s design; there is no option for the app to interact directly with the data store.

[Austin]

It also heavily depends on what other things are gonna be going on, if you’re just viewing data then its possible appsheet is a solution but if you need to perform manipulation and or computation(not filtering, something like counts, ins, selects, etc) on this data and what not then you will likely need another solution or will need to do all of the computation outside of appsheet which is also inherently slow. Appsheet will never beat a raw SQL statement to a database to return a table so if just Select * from table is a 10 second query appsheet will not beat this. You can get around this by pre-filtering your data using in database views and security filters on top of that to reduce the amount of data coming into the app.

I guess the actual biggest question is how much data is needed at 1 moment and what is your current performance levels? I think 10 seconds is a fairly slow load time but others might define 1 minute or even longer as slow.
I don’t think you’ll have to deal with a HARD limit but there is a functional limit on the amount of data appsheet can handle. I mean every solution has a functional limit whether it’s a hard limit, soft limit, or just a hardware limit(something might run on a massive server but you need it to run on a laptop)