Hello,
 
Je me permet de solliciter votre aide car je rencontre un comportement étrange sur un rapport SSRS (SQL Server Reporting Services).
Nous avons réalisé toutes les optimisations possibles au niveau des requêtes SQL.
 
La requête est une très grosse matrice susceptible de retourner plusieurs milliers voir millions de lignes.  
En principe, il s'agit d'un rapport pour Microsoft Dynamics CRM 2011. Ce rapport passe par un connecteur SSRS. Malheureusement, en fonction de certains paramètres d'entrées, le rapport génère un timeout.
 
Nous avons modifié la Datasource du rapport pour pointer directement sur la base de données du CRM 2011 sans passer par le connecteur SRS pour CRM. Par contre, il a fallu fournir les credentials d'un utilisateur.
Et là, le rendu du rapport s'affiche correctement sans générer de timeout et dans un temps raisonnable (de l'ordre de 15 sec).
 
Par contre, truc très étrange, si on modifie les credentials pour le pointer vers un autre utilisateur (ici, le compte admin du CRM), les performances s'écroulent. Le rapport est affiché au bout de 30 min. Et pourtant, le rapport n'a pas bougé d'un iota, ni la base de données. Le seul paramétre ayant changé vient des credentials.
 
Auriez-vous une idée de pourquoi selon les credentials, les performances d'un rapport est complètement chamboulé d'un utilisateur à un autre. Sachant que la requête SQL n'effectue aucune requête lié au profil de l'utilisateur.

Ben si c'est un compte admin, on peut supposer qu'il a tout. Suivant la façon dont sont stocker les droits d'accès, ça peut tout changer.
Soit le sgbd voit que c'est un compte admin et dans ce cas, il ignore les droits d'accès puisqu'il a droit à tout -> là, ça doit être rapide. Soit, il récupère tous ces droits d'accès (comme il le ferait pour n'importe quel user) et effectue la vérif les droits d'accès pour les appliquer afin de récupérer que ce que le user a droit de récupérer. Ce genre de vérif peut se faire via une requête SQL comportant un IN, voire pire, des OR pour chaque droit d'accès. Si le nb de droits d'accès possible est grand, l'admin les ayant tous, ben c'est le drame car un IN avec beaucoup de valeurs ou un OR, ça te flingue un SGBD.
 
Au boulot, je connais une appli qui gère les droits d'accès par entité et y'a environ 1000 entités. Or, la requête qui fait la vérif des droits d'accès passe justement par des OR : je te dis pas la cata niveau perfs
Donc, si ton user a peu de droits d'accès, ça sera rapide, sinon, ben, galère !

J'oubliais de préciser, que les autres comptes auquel on a testé son admin du CRM. Indirectement, la CRM va automatiquement ajouter les privilèges nécessaires pour accéder aux tables et vues de la base de données SQL Server.
 
Sans toutefois fournir le rôle admin sys côté SQL à ces utilisateurs.
 
On a retesté avec plusieurs autres comptes utilisateurs. Sur certain, le rapport s'affiche dans les 15 sec. Pour d'autres, ça mouline pendant 30 min voir génère un timeout.
 
Côté CRM et SQL, ces utilisateurs ont les mêmes privilèges.

Ca serait pas une histoire de requête mise en cache ?
 
Et quand tu listes les droits d'accès pour chaque user direct dans la BD, y'a le même nb d'enregistrements ? (je pensais à des droits qui traineraient en base mais seraient désactivés ou redondant, ce qui augmenterait le nb d'enregistrements mais fournirait les mes droits au final)

On a le même nombre d'enregistrements quelque soit l'utilisateur utilisé.
Le cache n'intervient pas, même si ça a son importance.

Bon, j'ai en partie résolu mon problème (cas 2). Dans le cas où on ne passe pas par le connecteur SRS pour CRM, si au niveau du dossier Sécurité dans SQL Server, cette utilisateur existe dans les connexions et est sysadmin. Le rapport foire et prend 30 min.
 
Si je supprime cet utilisateur, le rapport s'affiche en 5 sec. Je ne comprends pas pourquoi.
 
Mais ça ne résoud pas le problème avec le connecteur SRS pour CRM. Mais c'est déjà mieux.

Pour ce genre d'analyse il y a le SQL Profiler

CRM Dynamics c'est une horreur point de vue DB.
Si tu utilises les vues il y a de grande chance que des paramètres changent en fonction de l'utilisateur qui fait le select avec un join du style: cross join dbo.fn_GetMaxPrivilegeDepthMask(xxx).
 
Pour ne pas avoir de problèmes de perf sur CRM dynamics, la première solution est simplement de l’éviter comme la peste.
La deuxième solution est d’éviter les vue et leur 50 joins et cross join et de faire des select sur les tables en direct.
 
Les select en direct c'est pas supporté par msft mais au moins les rapports auront une durée prévisible.

Impossible de l'éviter surtout pour du reporting et vu la complexité et le besoin de certaines fonctionnalités de SQL (Group By, Union, Aggregat, etc...) pour le rapport, on ne peut pas faire autrement comme passer par du FetchXML (qui semble être bien plus performant).