Requête plus rapide avec l'atsortingbut supérieur

Je vois que vous avez maintenant posté les plans . Juste de la chance du tirage au sort.

Votre requête actuelle est une jointure de 16 tables.

 SELECT max(atDate1) AS AtDate1, min(atDate2) AS AtDate2, max(vtDate1) AS vtDate1, min(vtDate2) AS vtDate2, max(bgtDate1) AS bgtDate1, min(bgtDate2) AS bgtDate2, max(lftDate1) AS lftDate1, min(lftDate2) AS lftDate2, max(lgtDate1) AS lgtDate1, min(lgtDate2) AS lgtDate2, max(bltDate1) AS bltDate1, min(bltDate2) AS bltDate2 FROM (SELECT TOP 100000 at.Date1 AS atDate1, at.Date2 AS atDate2, vt.Date1 AS vtDate1, vt.Date2 AS vtDate2, bgt.Date1 AS bgtDate1, bgt.Date2 AS bgtDate2, lft.Date1 AS lftDate1, lft.Date2 AS lftDate2, lgt.Date1 AS lgtDate1, lgt.Date2 AS lgtDate2, blt.Date1 AS bltDate1, blt.Date2 AS bltDate2 FROM dbo.Tab1 a INNER JOIN dbo.Tab2 at ON a.id = at.Tab1Id AND cast(Getdate() AS DATE) BETWEEN at.Date1 AND at.Date2 INNER JOIN dbo.Tab5 v ON v.Tab1Id = a.Id INNER JOIN dbo.Tab16 g ON g.Tab5Id = v.Id INNER JOIN dbo.Tab3 vt ON v.id = vt.Tab5Id AND cast(Getdate() AS DATE) BETWEEN vt.Date1 AND vt.Date2 LEFT OUTER JOIN dbo.Tab4 vk ON v.id = vk.Tab5Id LEFT OUTER JOIN dbo.VerkaufsTab3 vkt ON vk.id = vkt.Tab4Id LEFT OUTER JOIN dbo.Plu p ON p.Tab4Id = vk.Id LEFT OUTER JOIN dbo.Tab15 bg ON bg.Tab5Id = v.Id LEFT OUTER JOIN dbo.Tab7 bgt ON bgt.Tab15Id = bg.Id AND cast(Getdate() AS DATE) BETWEEN bgt.Date1 AND bgt.Date2 LEFT OUTER JOIN dbo.Tab11 b ON b.Tab15Id = bg.Id LEFT OUTER JOIN dbo.Tab14 lf ON lf.Id = b.Id LEFT OUTER JOIN dbo.Tab8 lft ON lft.Tab14Id = lf.Id AND cast(Getdate() AS DATE) BETWEEN lft.Date1 AND lft.Date2 LEFT OUTER JOIN dbo.Tab13 lg ON lg.Id = b.Id LEFT OUTER JOIN dbo.Tab9 lgt ON lgt.Tab13Id = lg.Id AND cast(Getdate() AS DATE) BETWEEN lgt.Date1 AND lgt.Date2 LEFT OUTER JOIN dbo.Tab10 bl ON bl.Tab11Id = b.Id LEFT OUTER JOIN dbo.Tab6 blt ON blt.Tab10Id = bl.Id AND cast(Getdate() AS DATE) BETWEEN blt.Date1 AND blt.Date2 WHERE a.Nummer = 223889) B 

Tant sur les bons que sur les mauvais plans, le plan d'exécution indique «raison de la résiliation anticipée de l'optimization des relevés» comme «timeout d'expiration».

Les deux plans ont des ordres de jointure légèrement différents.

La seule jointure dans les plans non satisfaits par une search d'index est celle sur Tab9 . Cela a 63 926 lignes.

Les détails d'index manquants dans le plan d'exécution suggèrent que vous créez l'index suivant.

 CREATE NONCLUSTERED INDEX [miising_index] ON [dbo].[Tab9] ([Date1],[Date2]) INCLUDE ([Tab13Id]) 

La partie problématique du mauvais plan peut être clairement vu dans SQL Sentry Plan Explorer

SQL Server estime que les lignes 1.349174 seront renvoyées à partir des jointures précédentes entrant dans la jointure sur Tab9 . Et par conséquent, les loops nestedes se rejoignent comme si elle devait exécuter l'parsing sur la table interne 1.349174 fois.

En fait, 2 600 lignes alimentent ce joint ce qui signifie qu'il fait 2 600 balayages complets de Tab9 (2 600 * 63 926 = 164 569 600 lignes).

Il se trouve que sur le bon plan, le nombre estimé de lignes arrivant à la jointure est 2.74319. Cela est toujours faux de trois ordres de grandeur, mais l'estimation légèrement augmentée signifie que SQL Server privilégie une jointure de hachage à la place. Une jointure de hachage ne fait qu'un passage à travers Tab9

Je voudrais d'abord essayer d'append l'index manquant sur Tab9 .

Aussi, vous pouvez essayer de mettre à jour les statistics sur toutes les tables impliquées (en particulier celles avec un prédicat de date comme Tab2 Tab3 Tab7 Tab6 ) et voir si cela corrige l'énorme différence entre les lignes estimées et réelles à gauche du plan.

Diviser également la requête en parties plus petites et les matérialiser en tables temporaires avec des index appropriés pourrait aider. SQL Server peut ensuite utiliser les statistics sur ces résultats partiels pour prendre de meilleures décisions pour les jointures plus tard dans le plan.

Ce n'est qu'en dernier recours que j'utiliserais des astuces de requête pour essayer de forcer le plan avec une jointure de hachage. Les options pour ce faire sont soit l'indicateur USE PLAN auquel cas vous dictez exactement le plan que vous voulez, y compris tous les types de jointure et les ordres, ou en indiquant l' LEFT OUTER HASH JOIN tab9 ... Cette deuxième option a également pour effet secondaire de corriger tous les ordres de jointure dans le plan. Les deux signifient que SQL Server sera sévèrement limité est sa capacité à ajuster le plan avec des changements dans la dissortingbution de données.

Il est difficile de répondre sans connaître la taille et la structure de vos tables et de ne pas pouvoir voir le plan d'exécution complet. Mais la différence entre les deux plans est la jointure Hash Match pour la requête "top n" vs la jointure Nested Loop pour l'autre. Hash Match est une jointure très gourmande en ressources, car le server doit préparer des buckets de hachage pour pouvoir l'utiliser. Mais cela devient beaucoup plus efficace pour les grandes tables, alors que les loops nestedes, comparant chaque rangée d'une table à chaque rangée d'une autre table fonctionne très bien pour les petites tables, car il n'y a pas besoin d'une telle préparation. Ce que je pense est qu'en sélectionnant TOP 1000000000000 lignes dans la sous-requête, vous donnez à l'optimiseur un indice que vous êtes sous-requête produira une grande quantité de données, de sorte qu'il utilise Hash Match. Mais en fait, la sortie est petite, donc Nested Loops fonctionne mieux. Ce que je viens de dire est basé sur des lambeaux d'informations, alors s'il vous plaît ayez coeur critiquant ma réponse;).