The Row-level security (RLS) guidance in Power BI Desktop article mentions the potential negative performance impacts in reports. Is there only the potential for a negative performance impact, though? Could there also be the potential for a positive performance impact?
For example, if there are 10,000 rows in a table, but a user only has access to 1,000, then would Power BI run all of its queries against only those 1,000 rows? Or does it run the queries against the 10,000 rows with additional filters that decrease the performance.
The Avoid using RLS section in the article talks about splitting up the model and using different workspaces instead of RLS. Here it states:
There are several advantages associated with avoiding RLS:
- Improved query performance: It can result in improved performance due to fewer filters.
This makes me think that the all 10,000 rows in my hypothetical example would be evaluated and thus performance would not improve, but I wanted to reach out and verify this.
RLS will impact the query performance, as it will have to do in a typical RLS set up, take the user information, filter the reference table, then will filter the main data table and return the results.
It will have to go though, in your example all 10,000 rows, return the data that matches the filter criteria and then return the results.
The tabular engine will be optimised to some degree (seeking data via indexes, rather than scanning the whole data table), but it will have some level of overhead to filer datasets affected by RLS, rather than just the straight return of the whole data. In your example of 10K rows, it will be in the range of milliseconds, but you can use tools such as DAX Studio or Tabular editor, or the inbuilt tools of Power BI Desktop to see the affect on RLS in terms of performance.
RLS can go through 10's of millions of rows or data quite quickly, but final report performance generally depends on, the cardinality of the dataset (which helps indexing of the data), the number of relationships it has to transverse, the complexity of the DAX used in the visuals, the amount of data returned to the visuals, and the number of visuals on the report page itself.