Sync time reduced by 50% by using database view vs security filter

[Ali_Sabir]

I had a table with 350K rows and 60 columns (none virtual). However, due to security filter the actual rows in AppSheet data is around 50K. I was getting 18-22 seconds sync time in spite of trying lots of options within AppSheet. I then asked by best friend "ChatGBT" and it suggested to remove security filter and create a MySQL database view. To my surprised it has reduced sync time to under 10 seconds and improved UI performance as well. 

Previously I created ticket with AppSheet support and tried all their suggestions with no noticitable improvements. AI can do better than humans, it appears :))

Now, I wonder if I move the slices also in database views to further improve as ChatGBT suggests???

Now, I wonder if I move the slices also in database views to further improve??

[Benson_Realty]

how do you create a MySQL database view  ?

[Ali_Sabir]

Using MySQL Workbench, you can enter a query like this, for example:

CREATE VIEW filtered_comments_last_year AS
SELECT *
FROM Comment
WHERE Created >= CURDATE() - INTERVAL 1 YEAR
ORDER BY Created;

[AleksiAlkio]

The reason why the sync time has decreased is the security filter yes, but probably because they were simple enough so AppSheet was able to convert them to a SQL query. And when that happens, the filtering is happening in your database before the data is fetched to AppSheet server. When using spreadsheet as a data source, all data is first fetched to AppSheet server, and then security filter filters the data. You should check your security filter is it totally converted to a query or is part of the data still filtered in AppSheet server.

[SkrOYC]

If your query is complex, AppSheet may not be able to translate the security filter expression to a SQL query.
Simple ones are converted into SQL queries so the performance shouldn't be much different.

The way slices work differs from what a security filter or SQL query offers since slices work with the already filtered data.

If you are for some reason using slices to filter data instead of security filters, of course you will see a big improvement