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

AppSheet AppSheet Q&A
1

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??

2

how do you create a MySQL database view ?

3

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.

1 Like
4

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;

5

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

6

@SkrOYC ,

can appsheet convert it to SQL query ?

IF(

AND(
isnotblank(
            LOOKUP(
                LOOKUP(USEREMAIL(),"USERS", "EMAIL", "USER ROLE"),
                  "USER ROLE PERMISSIONS","USER ROLE", "CONSIGNMENTS"
                   )
          ), LOOKUP(USEREMAIL(),"USERS","EMAIL","USER STATUS") = "ENABLED")
         
          , LOOKUP(
                LOOKUP(USEREMAIL(),"USERS", "EMAIL", "USER ROLE"),
                  "USER ROLE PERMISSIONS","USER ROLE", "CONSIGNMENTS"
                   ), "READ_ONLY"
  )
7

Written in this way, no it won’t do that. But you could write it in another way. When it’s mentioned that you can use only few expressions, it’s not the whole trueth. If you need, you can use multiple expressions inside of an IN() because AppSheet is able to convert them into a parameter list before the query is sent. The limit with this list is 999 parameters.

When you are using app with the real database, you can find what is converted in the Performance Analyzer.

3 Likes
8

for learning purpose, can please provide me an IN() expression for above expression.

If the list has more than 999 names , will it be converted as SQL Query ?

9

The first thing I would do.. filter the “Users” and “User role permissions” table with the useremail() so you will have only one row after the security filter, then you would not need to use nested lookups. Though your formula you copied is not meant for the security filter as the result is not TRUE or FALSE.

2 Likes
10

Each LOOKUP() in that expression would be better and more efficiently implemented using slices. As written, you’ve got a LOT of built-in inefficiency.

3 Likes
11

I have applied this expression SF, to reduce the data load during sync and to avoid exposure of data to unwanted users . How to achieve my requirement in slices ?

AND( 

IF(
   LOOKUP(
  LOOKUP(USEREMAIL(),"USERS", "EMAIL", "USER ROLE"),
  "USER ROLE PERMISSIONS","USER ROLE", "CONSIGNMENTS"
    ) = "NO ACCESS",FALSE,TRUE),

   in(Useremail(), USERS[EMAIL])
   )
12 
AND( 

IF(
   LOOKUP(
  LOOKUP(USEREMAIL(),"USERS", "EMAIL", "USER ROLE"),
  "USER ROLE PERMISSIONS","USER ROLE", "CONSIGNMENTS"
    ) = "NO ACCESS",FALSE,TRUE),

   in(Useremail(), USERS[EMAIL])
   )

Sorry , the above one is the right expression used in SF. Kindly guide me how to simplify it better.

previous expression is to decide to show and edit the data.

13

Here is your expression, reformatted to my preferences:

IF(
  AND(
    ISNOTBLANK(
      LOOKUP(
        LOOKUP(
          USEREMAIL(),
          "Users",
          "Emails",
          "User Role"
        ),
        "User Role Permissions",
        "User Role",
        "Consignments"
      )
    ),
    (
      LOOKUP(
        USEREMAIL(),
        "Users",
        "Email",
        "User Status"
      )
      = "Enabled"
    )
  ),
  LOOKUP(
    LOOKUP(
      USEREMAIL(),
      "Users",
      "Email",
      "User Role"
    ),
    "User Role Permissions",
    "User Role",
    "Consignments"
  ),
  "READ_ONLY"
)

Create a slice on the Users table named (e.g.) My User with a row filter expression of:

(USEREMAIL() = [Email])

By doing this, the current (“my”) user’s row is isolated and immediately accessible without scanning the entire Users table each time you need a value from it. Your expression can then be simplified to this:

IF(
  AND(
    ISNOTBLANK(
      LOOKUP(
        ANY(My User[User Role]),
        "User Role Permissions",
        "User Role",
        "Consignments"
      )
    ),
    ("Enabled" = ANY(My User[User Status]))
  ),
  LOOKUP(
    ANY(My User[User Role]),
    "User Role Permissions",
    "User Role",
    "Consignments"
  ),
  "READ_ONLY"
)

Create a slice on the User role permissions table named (e.g.) My Permissions with a row filter expression of:

([User Role] = ANY(My User[User Role]))

This isolates the current user’s permissions for easier and more efficient reference. Your expression can then be simplified to this:

IF(
  AND(
    ISNOTBLANK(ANY(My Permissions[Consignments])),
    ("Enabled" = ANY(My User[User Status]))
  ),
  ANY(My Permissions[Consignments]),
  "READ_ONLY"
)

Another alternative would be:

ANY(
  IFS(
    ("Enabled" = ANY(My User[User Status])),
      My Permissions[Consignments]
  )
  + {"READ_ONLY"}
  - LIST("")
)
1 Like
14

I assume you are trying to write the security filter in table #3. If that’s the case, something like this should work.
#1 - Use a security filter in Users table as USEREMAIL()=[Email]
#2 - In User Role Permission table, use IN([User Role],Users[User Role])
#3 - And in your final table, use IN(ANY(User Role Permission[Consigments]),LIST(“Access”,“all true words”))

15

Yours, reformatted:

AND( 
  IF(
    (
      LOOKUP(
        LOOKUP(
          USEREMAIL(),
          "Users",
          "Email",
          "User Role"
        ),
        "User Role Permissions",
        "User Role",
        "Consignments"
      ) = "No Access"
    ),
    FALSE,
    TRUE
  ),
  IN(USEREMAIL(), Users[Email])
)

With my previous suggestions:

AND( 
  ISNOTBLANK(My User[_RowNumber]),
  ("No Access" <> ANY(My Permissions[Consignments))
)
1 Like
16

#1 will make the user to have a look on his row. incase If the permission grant to a particular user, then it is needed to disclose the data to particular user.

please see this link how I have established the table structure. Dynamic User Control

Literally I have made an group policy based on user roles.

17

USEREMAIL () = [EMAIL] will make the user to have a look only on his row. incase If the permission grant to a particular user, then it is needed to disclose the data to particular user.

please see this link how I have established the table structure. Dynamic User Control

Literally I have made an group policy based on user roles.