I support an Internal ERP system and an external Reporting/Dashboard that our customers use that both use the same database.
Internal normal application users can control what customers can access through the external dashboard - this can be sites, jobs, documents etc as who can see what on which accounts/sites gets very complex very quickly.
As such we use an Inline Table Value Function to return who can see what (this can and does change fairly frequently) and join this to the resources they can see through a field that's common across all the data.
This function is called multiple times per page and is difficult to cache; it's not unusual for this result set to consist of 10k+ records (which are then joined to resources and queried against) though is fast.
A typical call would therefore look something like this:
SELECT top 50 * FROM [dbo].[fn_GetJobsForUser] (CustomerUserId) order by JOBIDNumber desc
The code for which looks like:
CREATE FUNCTION [dbo].[fn_GetJobsForUser] ( @contactId int ) RETURNS TABLE AS RETURN ( SELECT -- ... FROM cd_Job (NOLOCK) jobs inner join dbo.fn_GetUserPermissions(@contactId) usr on jobs.CommonField = usr.CommonField )
This has worked well so far, though it's always been slow when run from a cold start when a customer first logs in (we mask it with a loading screen).
The problem is that it will occasionally time out where customers have a large number of records returned from the permissions query.
I'm therefore looking for a better approach, in summary:
- We need to allow internal users to give permissions to external customers and them to apply immediately
- The permissions are complex and the result set can be over 18k records
- There are no indexes missing and 99% of the time everything runs smoothly
- It performs poorly for large customers with a large number of permissions
- I can't see how roles would improve things, most users have the same permission levels but can only see data that pertains to them and this is arbitrarily set by an internal user. New data and resources are added on a daily basis.
If it makes any difference our stack is SQL-Server (2008) & C# MVC5 using Entity Framework
This can't be a new problem, does anyone have any pointers as to a better approach?