Permessi utente dinamici migliori

Supporto un sistema ERP interno e un Reporting / Dashboard esterno utilizzati dai nostri clienti che utilizzano entrambi lo stesso database.

Gli utenti normali dell'applicazione interna possono controllare ciò che i clienti possono accedere tramite il cruscotto esterno: possono essere siti, lavori, documenti, ecc. chi può vedere cosa su quali account / siti diventa molto complesso molto rapidamente.

Pertanto utilizziamo una funzione Valore tabella inline per restituire chi può vedere cosa (questo può e cosa cambia abbastanza frequentemente) e unirlo a risorse che possono vedere attraverso un campo comune a tutti i dati.

Questa funzione è chiamata più volte per pagina ed è difficile da memorizzare nella cache; non è insolito che questo set di risultati sia composto da 10k + record (che vengono poi uniti alle risorse e interrogati) sebbene sia veloce.

Una tipica chiamata sarebbe quindi simile a questa:

SELECT top 50 * FROM [dbo].[fn_GetJobsForUser] (CustomerUserId) order by JOBIDNumber desc

Il codice per il quale appare:

    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 

) 

Questo ha funzionato bene finora, anche se è sempre stato lento quando si avvia da un avvio a freddo quando un cliente effettua il login (lo mascheriamo con una schermata di caricamento).

Il problema è che occasionalmente scade il tempo in cui i clienti hanno un numero elevato di record restituiti dalla query dei permessi.

Sto quindi cercando un approccio migliore, in sintesi:

• Dobbiamo consentire agli utenti interni di fornire autorizzazioni ai clienti esterni e di applicarli immediatamente
• Le autorizzazioni sono complesse e il set di risultati può contenere oltre 18k di record
• Non ci sono indici mancanti e il 99% delle volte tutto scorre liscio
• Funziona male per i grandi clienti con un gran numero di permessi
• Non riesco a vedere come i ruoli migliorerebbero le cose, la maggior parte degli utenti ha gli stessi livelli di autorizzazione ma può vedere solo i dati che li riguardano e questo è arbitrariamente impostato da un utente interno. Nuovi dati e risorse vengono aggiunti su base giornaliera.

Se fa differenza, il nostro stack è SQL Server (2008) & C # MVC5 utilizzando Entity Framework

Questo non può essere un nuovo problema, qualcuno ha qualche indicazione su un approccio migliore?