Could someone help in understanding the issue which creates a performance dip in the query execution time as an Admin user and a normal business user. For example, the business user is none but an operator assigned with a business unit id. This setup is included to make sure the operators assigned with a business unit cannot view the data of other business units and scenario is covered using sysfilter in the data schemas i.e., in Recipient, Card etc.
And say suppose if a basic query like recipients who has a VIP card is run against the recipient schema is taking 1 min to return the results for admin user, whereas the same query is taking 5 min for a business user. The results of the query differs due to the business unit link with the operators and the sysfilter in the respective schemas. The same query when run by a business user returns only the result relevant to that particular business unit he is tied up with.
We are trying to understand the reason of this time difference, the query takes to return the results. Could someone explain the reason behind this behavior?
One of the cons of data partitioning using sys filters is database performances (1..n SQL join)
Using partitioning is only going to help your query performance if the partitioning scheme is built serve your specific queries. Your going to have to review your query patterns and see how they are accessing the table in order to identify the best approach.
Thanks for your quick response. Could you please elaborate this point as am not able to understand the deeper insights of this setup? Also, it would be helpful if you could share info on how this data partitioning supports sysfilters and when it would cause issues for the database performance i.e., the scenarios which would cause troubles for these query executions?