LinqToSql Query time de return trop de performance trop longtime

Il y a plusieurs choses que vous pourriez faire:

1. Index filtrés : Selon la répartition des loggings dans le tableau Opportunités autour de la valeur 'open', vous pouvez créer un index filtré sur 'open'. Si vous avez des quantités à peu près égales de 'open' et 'closed' (ou d'autres valeurs), alors un index filtré laissera votre TSQL regarder uniquement les loggings qui ont 'open'. Un index filtré stocke uniquement datatables qui correspondent au prédicat; Dans ce cas, tout ce à quoi vous vous joindriez aura la valeur 'ouvert'. De cette façon, il n'a pas besoin d'parsingr d'autres index pour les loggings qui peuvent avoir «ouvert» en eux.

2. Table récapitulative / rollup : créez une table de rollup contenant les valeurs que vous searchz; Dans ce cas, vous cherchez des sums et des counts – pourquoi ne pas créer une table qui a simplement une ligne qui a ces counts? Vous pouvez utiliser un travail Stored Procedure / Agent pour le maintenir à jour. Si votre requête le permet, vous pouvez également essayer de créer une vue indexée; Je vais aller dans cela ci-dessous. Pour le tableau récapitulatif vous exécuteriez essentiellement une procédure stockée qui calcule ces champs et les met à jour périodiquement (disons une fois toutes les quelques minutes ou une fois par minute, en fonction de la charge) et écrit ces résultats dans une nouvelle table; ce serait votre table de rollup. Vos résultats sont alors aussi faciles qu'une déclaration choisie. Ce serait très rapide, au prix de la charge pour calculer ces sums toutes les quelques minutes. Selon le nombre d'loggings, cela pourrait être problématique.

3. Vue indexée : Probablement la bonne façon de résoudre un problème comme celui-ci, en fonction de vos contraintes et du nombre de lignes dont nous parlons (dans mon cas, je l'ai poursuivi pour un cas où il y avait des centaines de milliers de lignes) .

Index filtrés

Vous pouvez également créer un index filtré (c'est un peu abusif, mais cela fonctionnerait) pour chacun de ces états, et ensuite simplement quand il sum / count, il doit seulement countr sur l'index qui correspond à l'état qu'il search .

Pour créer un index filtré:

 CREATE NONCLUSTERED INDEX FI_OpenStatus_Opportunities ON dbo.Opportunities (AccountManagerId, Status, ActualAmount) WHERE status = 'OPEN'; GO 

De même pour vos sums et vos counts (un par colonne):

 CREATE NONCLUSTERED INDEX FI_SalesCycleEnquiry_Status_Opportunities ON dbo.Opportunities (AccountManagerId, Status, SalesCycle, ActualAmount) WHERE status = 'OPEN' and SalesCycle = 'Enquiry' 

(et ainsi de suite pour le rest).

Je ne dis pas que c'est ta meilleure idée; mais c'est une idée. Que ce soit bon ou mauvais dépend de la façon dont il fonctionne dans votre environnement sur votre charge de travail (quelque chose que je ne peux pas répondre).

Vue indexée

Vous pouvez également créer une vue indexée qui contient ces informations de cumul; C'est un peu plus avancé et ça dépend de vous.

Pour faire ça:

  CREATE VIEW [SalesCycle_Summary] WITH SCHEMABINDING AS SELECT AccountManagerID, Status, SUM(ActualAmount) AS MONETARY ,COUNT_BIG(Status) as Counts FROM [DBO].Opportunities GROUP BY AccountManagerID, Status GO -- Create clustered index on the view; making it an indexed view CREATE UNIQUE CLUSTERED INDEX IDX_SalesCycle_Summary ON [SalesCycle_Summary] (AccountManagerId); 

Et puis (en fonction de votre configuration), vous pouvez soit join directement cette vue indexée, ou l'inclure via un indice (essayez pour l'ancien).

Enfin, si rien de tout cela ne fonctionne (il y a quelques pièges autour des vues indexées – je ne les ai pas utilisées depuis environ 6 mois, donc je ne me souviens pas très bien de la question spécifique), vous pouvez toujours créer un CTE abandonner entièrement Linq-To-SQL.

Cette réponse est un peu hors de scope (parce que j'ai déjà donné deux approches et elles exigent beaucoup d'investigation de votre part).

Pour étudier comment ils font:

1. Obtenez le SQL généré à partir de votre instruction Linq-To-SQL ( voici comment vous faites cela ).

2. Ouvrez SSMS et activez les éléments suivants dans une window de requête:

   • SET STATISTICS IO ON
   • SET STATISTICS TIME ON
   • Cochez la case "afficher le plan de requête réel" et "afficher le plan de requête estimé"
   • Copiez le SQL généré dans celui-ci; exécuter.

3. Résolvez tous les problèmes avec les index avant de continuer. Si vous obtenez des avertissements d'index manquants; étudiez-les et résolvez-les, puis relancez les benchmarks.

Ces numéros de départ sont vos repères.

• Statistiques IO vous indique le nombre de lectures logiques et physiques effectuées par votre requête (plus faible est mieux, concentrez-vous sur les zones où il y a un nombre élevé de lectures en premier)
• Statistics TIME vous indique le time nécessaire à l'exécution de la requête et l'affichage de ses résultats dans SSMS (veillez à activer SET NOCOUNT ON afin de ne pas affecter les résultats).
• Le plan Actual Query vous indique exactement ce qu'il utilise, les index que SQL Server pense vous manquer et d'autres problèmes tels que les conversions implicites ou les statistics incorrectes qui affecteraient vos résultats. Brent Ozar Unlimited a une super video sur le sujet , donc je ne vais pas reproduire la réponse ici.
• Le plan de requête estimé vous indique ce que SQL Server pense qu'il va se passer – ce ne sont pas toujours les mêmes que le plan de requête réel – et vous voulez être sûr de prendre en count la différence dans votre enquête.

Il n'y a pas de réponses 'faciles' ici; la réponse dépend de vos données, de l'utilisation de vos données, ainsi que des modifications que vous pouvez apporter au schéma sous-jacent. Une fois que vous l'avez exécuté dans SSMS, vous verrez à quel point il s'agit de l'en-tête Linq-To-SQL, et quelle est la quantité de la requête elle-même.

J'ai fait ma requête linq plus tôt dans ma requête, en faisant un groupe par et ensuite en créant mes objects. J'ai été seulement capable de faire ceci en raison de la petite quantité d'articles returnnés ainsi le server peut facilement les manipuler. Toute personne ayant un problème similaire serait mieux conseillé à l'user George Stocker's Answer

J'ai mis à jour ma requête à ce qui suit:

  var allOpps = ctx.slx_Opportunities.Where(opp => opp.STATUS == "open").GroupBy(opp => opp.SALESCYCLE).ToList(); var users = ctx.tbl_Bios.Where(bio => bio.SLXUID != null).ToList().Select(bio => new UserStats { LeadNum = allOpps.Single(group => group.Key == "Lead").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(), LeadMoney = allOpps.Single(group => group.Key == "Lead").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp => opp.SALESPOTENTIAL.GetValueOrDefault(_default)), GoingAheadNum = allOpps.Single(group => group.Key == "Going Ahead").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(), GoingAheadMoney = allOpps.Single(group => group.Key == "Going Ahead").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp => opp.SALESPOTENTIAL.GetValueOrDefault(_default)), EnquiryNum = allOpps.Single(group => group.Key == "Enquiry").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(), EnquiryMoney = allOpps.Single(group => group.Key == "Enquiry").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp => opp.SALESPOTENTIAL.GetValueOrDefault(_default)), GoodPotentialNum = allOpps.Single(group => group.Key == "Good Potential").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(), GoodPotentialMoney = allOpps.Single(group => group.Key == "Good Potential").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp => opp.SALESPOTENTIAL.GetValueOrDefault(_default)), PriceOnlyNum = allOpps.Single(group => group.Key == "Price Only").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(), PriceOnlyMoney = allOpps.Single(group => group.Key == "Price Only").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp => opp.SALESPOTENTIAL.GetValueOrDefault(_default)), ProvisionalNum = allOpps.Single(group => group.Key == "Provisional Booking").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Count(), ProvisionalMoney = allOpps.Single(group => group.Key == "Provisional Booking").Where(opp => opp.ACCOUNTMANAGERID == bio.SLXUID).Sum(opp => opp.SALESPOTENTIAL.GetValueOrDefault(_default)), Name = bio.FirstName + " " + bio.SurName }).ToList();