Performances de database pour déterminer les entités affectées aux users

Je suis intéressé par un aperçu de l'amélioration des performances dans l'exemple suivant:

Je travaille actuellement sur une application qui a une structure d'entité hiérarchique où, selon le niveau d'un user au sein de l'organisation, ils auraient une affectation à une entité spécifique à un niveau spécifique – et ont ainsi access à tous leurs enfants (dans ce cas dans les dissortingcts au sein des régions à travers les États-Unis). La plupart des super-users auront plus de 20 000 entités individuelles dans leur portefeuille, certaines avec jusqu'à 40 000.

Au lieu d'entrer dans les détails, disons qu'un peu de logique est nécessaire pour déterminer toutes les entités auxquelles un user a access. Cette logique est actuellement gérée à l'aide d'une fonction de table utilisée dans 95% des procédures stockées. La procédure stockée moyenne ne prend pas plus de 1 à 2 secondes pour s'exécuter. MAIS, dans une page ASP.Net qui fait des appels à 10+ différentes procédures stockées, la performance frappe rapidement des boules de neige en 20 + secondes.

En guise d'alternative, nous ne prévoyions d'appeler cette fonction de table qu'une seule fois (lors de la connection) et de stocker les résultats dans une table (après avoir effacé toutes les valeurs précédentes pour le même user). Nous aurions alors toutes les procédures stockées reference cette nouvelle table au lieu de la fonction de table. Un test a révélé qu'une page qui prenait 15 secondes à charger pouvait s'afficher en less de 3 secondes lors de la sélection de la nouvelle table.

Par exemple:

L'user se connecte
système supprime toutes les entités de l'user dans la table
le système insère toutes les entités de l'user puis
le système les envoie sur leur voie joyeuse et n'exécute plus la fonction de table pour cette session en cours

Notre préoccupation est que, avec la possibilité pour des centaines d'users de se connecter et de se déconnecter de l'application, la suppression et l'insertion si souvent dans cette table peut entraîner une performance importante en raison du locking des lignes. Est-ce que quelqu'un d'autre a utilisé une table SQL de cette manière? Si c'est le cas, devrions-nous nous préoccuper de la faible performance en raison de l'insertion constante et de la suppression d'une seule table.

J'ai fait quelque chose de similaire et c'était bien, mais c'était pour une application avec au plus quelques centaines d'users simultanés, donc cela dépend du type d'échelle que vous avez. Si vous obtenez des interblocages, vous pouvez aller plus loin en créant et en supprimant une table pour chaque user, mais vous devrez avoir quelque chose qui a échappé à la table lorsque la session d'un user a expiré.

Les deux ci-dessus sont un peu un hack ce que vous devriez vraiment faire est de maintenir une table de permissions user et de le mettre à jour lorsque datatables associées changent. Peut-être un peu de factorisation mais en fonction de votre application.

Je pense que ce serait une mauvaise design, et vous allez rencontrer des problèmes de performance à un moment donné.

Je peux penser à deux façons d'inverser ce problème, d'abord à quelle fréquence avez-vous vraiment besoin d'une list de toutes les entités auxquelles un user peut accéder, il serait beaucoup plus rapide de recréer l'tree avec un CTE et de vérifier la permission pour l'logging qu'ils veulent accéder, mais c'est un grand changement dans votre model.

La seconde ne consiste pas à mettre à jour le cache lorsque l'user se connecte, mais lorsque datatables sont modifiées. Avoir un triggersur sur la table d'affectation, chaque fois que ces modifications de données appliquent le résultat de cette modification à la table des éléments autorisés, en cascadant effectivement le changement tout le long de l'tree. Parce que datatables ne changeraient que lorsque cela serait nécessaire, elles seraient plus performantes.