Efficient approach for filtering, paging and sorting across multiple tables with multiple key columns

I have a query that retrieves data from a complex data structure involving multiple tables. The data can be filtered using a tree component that allows selection of different keys from different tables, sometimes filtering across two or three tables at once (for example filtering on OrganisationID and the LocationID within this organisation). In addition to filtering, the data must be paged and sorted. I'm using a tableview in the ui that allows sorting by a single selected column (only one ORDER BY clause at a time). The filtering targets specific columns from different tables. For example: - OrganisationID (from the Organisation table) - LocationID (from the Location table) - FolderID (from the Folder table) Sometimes the filter must apply across multiple of these keys simultaneously, for example filtering all related OrganisationIDs and LocationIDs and retrieving a paged result set. **Current approach:** I considered creating multiple indexed views based on these key columns, each with many indices to support different sorting options. This would lead to about 6 views, each with around 26 indices. Since indices are pre-sorted this does provide with the ability to quickly filter and sort. But having a very large amount of indices like this on single tables let alone on an indexed view spanning multiple tables seems very contrary to index best practice. **Question:** What are efficient strategies to implement filtering, paging, and sorting across multiple fixed keys from different tables at once? Here is a dbfiddle which illustrates what I am trying to describe. It is a bit simplified, but it catches enough of the gist of what I am trying to accomplish. **Additional Information:** Filtering, sorting, and paging are all currently implemented. However, due to the nature of filtering and sorting across different tables, any sort operation that lacks an appropriate index severely impacts paging. Without an index, SQL Server must scan the entire filtered set to apply sorting and paging. For some filter combinations, this results in scanning over 500,000 rows. Although most individual queries run reasonably fast, the system executes them frequently and concurrently for many users. This leads to significant memory grants, resulting in high memory pressure and a drastic drop in the Page Life Expectancy (PLE), affecting overall performance. For the worst case scenario I can find a query costing 166233968 logical reads.