We have a scenario where admins in our system can see users who belong to their department or any department under them. We, therefore have a few queries that join multiple times onto our Department table and compare the nodepaths for the admin and users to see who can see who...
AND ((SUBSTRING([UserDepartment].[NodePath], 0 + 1, LEN([AdminDepartment].[NodePath]))) =
For clarification, "UserDepartment" and "AdminDepartment" are both aliases for the "Department" table.
Obviously, this is going to perform a table SCAN on the Department table. For most of our clients this is okay since they have less than 30 departments. But we have a few clients that have over 5000 departments and the related queries are taking minutes to run due to this.
Does anyone have ideas on how to better query this? I'm also interested in hearing about better ways to architect this data storage but that would be a pretty fundamental change so I'm hoping for query/index change solutions.