Re: AppSheet and SQL Server

ranorga · 06-11-2022 04:16 PM

Hello Community:
I'd appreciate your help with a case. We want to use AppSheet to view data in a SQL Server database that has 2 million records. There are year, month, and day fields on the records that we could use to filter from the AppSheet. Do you have any idea how to view data through AppSheet efficiently?

Marc_Dillon

https://help.appsheet.com/en/articles/954497-security-filters-the-essentials

ranorga

Thanks Marc, but security filters are very useful to restrict access but not to improve performance when there are high volumes of data. I continue with the doubt.

Marc_Dillon

>>"security filters are very useful to restrict access but not to improve performance when there are high volumes of data"

They do both. Security Filters inform the app which records to load. Loading less records means more performance.

ranorga

Thank you Marc. Unfortunately, in the apps I have created, I have verified that security filters do not significantly help performance. Partitions do improve performance dramatically, but the idea of moving to relational databases is not to use partitions. Thanks anyway Marc.

Swoopy

Couldn't agree more (*to the question*).

It's impossible to present AppSheet to management level for making decision of investing in AppSheet and setup a dedicated cloud infrastructure for it.

1. Can not try. My understanding is that we need to setup a pricy database server and choose an AppSheet's plan in order to test it. If it's not working well, is this worth the risk?

2. No demonstration/comparison videos, even a review of AppSheet performance over different cloud data services.

It's AppSheet's vague features.

Please correct me if I'm wrong.

Marc_Dillon

I'm not sure what you're agreeing with, or how your post has any relation to the OP, besides the topic of cloud databases in general.

Re #1: You can spin up a database instance on your own hardware to test with. Or you can use Google Cloud's free trial period. Or you can spend *at most* a few hundred dollars (more likely a lot less) for a cloud instance for a month. You can test cloud database connections on an undeployed app on a free Appsheet plan. What exactly is the risk?

What feature are vague?

Swoopy

Why in the world there's no demo showing its performance? At least ppl can see, no need to spend time testing by themselves, and turn this vague to clearer picture.

Is that too hard to make demo vdo of performance test over hundreds of thousands rows on cloud data services? If it's not big deal for AppSheet, that's fine!

Swoopy

@ranorga Unfortunately, in the apps I have created, I have verified that security filters do not significantly help performance. Partitions do improve performance dramatically, but the idea of moving to relational databases is not to use partitions.

Thank you so much for this Information. I feel it answers what I need to know so far.

Jonathon

A SQL database should perform better than a spreadsheet when security filters are in use, as DB indexes allow the data to queried more efficiently. Otherwise, 2m records is 2m records... that's going to be slow to load regardless of the database. Even excel workbooks start to suffer at this point.

Other platforms might lazy load the data to help with performance, but appsheet doesn't (currently) do that.

If you are familiar with DB partitions, then you can make partitions based on views in your SQL server. This should speed things up pretty dramatically.

ranorga

Thanks a lot @Jonathon !