Hi , I had a look at the performance profil...

[Tejaswini_Yarra]

Hi ,

I had a look at the performance profile of the app after the sync action . I have attached the screenshot and highlighted two rows . I am not able to understand what internal API Based Copy Rows are . But they are eating up a lot of sync time . Is there a way I can improve my app to exclude this ?

Regards,

Tejaswini

[Harry2]

@Tejaswini_Yarramanen Hi Tejaswini, I assume you’re working with a SQL table. The first highlighted row in the screenshot is the process of reading raw data directly from the SQL table. This took almost 6 seconds. The second highlighted row from the screenshot is the process of converting the raw data fetched from the database to a format that can be used by AppSheet. This took 11.5 seconds.

The second step took such a long time is because AppSheet is not a dedicated SQL platform. Since AppSheet has to work with not just data from SQL databases but also Google sheet, excel files, etc., our server cannot be optimized to process raw SQL data as fast as dedicated SQL databases do.

The best way to reduce the time taken is to reduce the number of rows that will be fetched directly from the database. This will reduce both the time spent reading the rows as well as the time spent processing them. To reduce the number rows fetched, you either use a security filter in AppSheet, or hook your AppSheet table to a SQL view instead of the whole SQL table.

[Tejaswini_Yarra]

@Harry So this means for same number of rows Google sheet will take less sync time than the sql database sync . I tried with the same number of rows on google sheet and sql db and google sheets is pretty efficient over sql .

[Tejaswini_Yarra]

@Harry or([usere5]=USEREMAIL(),[usere6]=USEREMAIL())

[Harry2]

@Tejaswini_Yarramanen In general, even with the processing overhead SQL databases should be much faster than Google Sheet. As the number of rows in your table increases, the performance difference will become more pronounced. So if you have a large data set, or if you expect your data set to grow very large, I still recommend using a SQL database.

Also, using SQL databases will allow you to take advantage of security filters and SQL views to significantly reduce the amount of data fetched.

[Tejaswini_Yarra]

@Harry

By security filters do you mean the filters on appsheet while fetching the data ?

[Harry2]

@Tejaswini_Yarramanen Yes, you can find them in the Security section of the app editor. Right now, security filter expressions that use equal comparisons (for example, [Email]=USEREMAIL()) will be translated into a where clause in the SQL query. As a result, less rows will be read directly from the database.

For security filter expressions that are not equal comparisons, the best option right now is to convert them into a SQL view, and then hook this view instead of the source SQL table to the table in AppSheet.

[Tejaswini_Yarra]

@Harry Thanks.

[Tejaswini_Yarra]

@Harry Is there a way to dynamically send data to backend data source .

Example - I want to create a view on sql and filter it using the person who is using the app . So he/she can only see the rows which they have access too . I have used security filters but sql views seem to act quick .

[Harry2]

@Tejaswini_Yarramanen I’m afraid this is not possible. If you want to filter users based on their emails, you will need to do so using a security filter. However, as I have mentioned, if the security filter uses an equal comparison - [Email]=USEREMAIL() for example - the filter will still be translated into a SQL query, which will then reduce the number of rows fetched directly from the database. Because the user’s email will be included in the translated query, this is in effect equivalent to what you are asking for.

[Tejaswini_Yarra]

@Harry Yeah I checked the performance data when I added a security filter on appsheet - sync time 20 secs approx . Then I removed the security filter and added the where clause in the view . sync time - 5-6 seconds . same number of rows where loaded.

[Harry2]

@Tejaswini_Yarramanen Could you give me the exact security filter expression that you are using?