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
            -- ...
        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?

  • 1
    Table value functions can perform poorly as the optimiser has no advance view of the statistics. Also are you sure the statistics in general are up to date on your database? – Robbie Dee May 10 '16 at 11:05
  • Thanks @RobbieDee, so do you think maintaining a table rather than the TVF (that is updated each time something changes) would offer significant performance increase? - Since this is an Inline TVF (not multi-statement) wouldn't the statistics be taken from the base tables? – SWa May 10 '16 at 11:10
  • I would have thought so but you're just going to have to try it and see. – Robbie Dee May 10 '16 at 11:13
  • @RobbieDee - so far the code looks like an inline table function which doesn't have the problems you mention like a multi-valued table function. – JeffO May 10 '16 at 12:34
  • Are you able to post the code for fn_GetUserPermissions? – Robbie Dee May 10 '16 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.