MS SQL Server
  1. MS SQL Server
  3. Les expressions de table communes ralentissent lors de l'utilisation d'une variable de table
Intereting Posts
Comment changer toutes les valeurs NULL de colonne de type de bit à false dans toute la database dans MSSQL? Comment get l'ID de la dernière input créée à partir de la database Sql avec Asp.Net Comment créer une list délimitée par des virgules à partir d'une table avec des colonnes dynamics Ajouter des lignes à la sélection finale si la requête SQL est vide Comment puis-je supprimer plusieurs lignes d'une table contenant 2 keys composées avec plusieurs valeurs dans SQL Server? SQL – combiner des lignes de date consécutives basées sur la colonne MySQL GROUP BY ne fonctionne pas lors de la migration vers SQL Server 2012 Problème de type de file flatfile avec SSIS vers SQL Server SQL Query pour parsingr le champ XML et extraire les valeurs possible d'insert le 1er de n tables d'un proc dans une table temporaire? La date du type de données d'opérande est non valide pour l'opérateur de soustraction comment arrondir ma réponse Format de données de modification SQL pour les valeurs minimales et maximales Comment importer ON / OFF SET ANSI_NULLS pour les procédures stockées dans les projets de database VS2010? Comment afficher des données à partir d'une requête SQL Server dans des tables android

Les expressions de table communes ralentissent lors de l'utilisation d'une variable de table

J'ai expérimenté avec le CTE (simplifié) suivant. Lorsque vous utilisez une variable de table (), la requête s'exécute pendant quelques minutes avant de l'annuler. Toutes les autres methods commentées returnnent en less d'une seconde.

Si je remplace toute la clause WHERE par un INNER JOIN, c'est rapide aussi.

Des idées pour lesquelles utiliser une variable de table fonctionnerait si lentement?

FWIW: La database contient 2,5 millions d'loggings et la requête interne renvoie 2 loggings. 

CREATE TABLE #rootTempTable (RootID int PRIMARY KEY) INSERT INTO #rootTempTable VALUES (1360); DECLARE @rootTableVar TABLE (RootID int PRIMARY KEY); INSERT INTO @rootTableVar VALUES (1360); WITH My_CTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY d.DocumentID) rownum, d.DocumentID, d.Title FROM [Document] d WHERE d.LocationID IN ( SELECT LocationID FROM Location JOIN @rootTableVar rtv ON Location.RootID = rtv.RootID -- VERY SLOW! --JOIN #rootTempTable tt ON Location.RootID = tt.RootID -- Fast --JOIN (SELECT 1360 as RootID) AS rt ON Location.RootID = rt.RootID -- Fast --WHERE RootID = 1360 -- Fast ) ) SELECT * FROM My_CTE WHERE (rownum > 0) AND (rownum <= 100) ORDER BY rownum

C'est à partir de l'utilisation de la variable de table. La requête a pris plus de 17 minutes pour s'exécuter: entrez la description de l'image ici

Plans d'exécution au format XML

Table temporaire: https://docs.google.com/open?id=0B66I-fxlyEtEZEthV3ZaWlNLWXM

Variable de table: https://docs.google.com/open?id=0B66I-fxlyEtEbUFZa3RJejFCTkk

Ce que les plans de requête clarifient, c'est que la version Table Variable souffre du fameux problème d '"estimation de la cardinalité des variables de table". Cela se produit principalement parce que, contrairement aux tables temporaires, les variables de table ne prennent pas en charge les statistics. Cet article de Paul White l'explique en détails exquis: http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx .

Ce qui est également clair, c'est que la solution la plus simple est probablement d'append une OPTION (Recomstack); clause à la fin de votre requête. Bien que cela puisse ne pas fonctionner dans les versions antérieures de SQL Server, dans les versions ultérieures, il doit get une meilleure estimation de la cardinalité et produire ainsi le même plan de requête que pour la version de la table temporaire.

Faites-moi savoir si cela ne fonctionne pas, car il existe d'autres solutions (less souhaitables) possibles.

Solution partielle: Ce qui a beaucoup aidé était de rebuild ou de réorganiser les index sur la table comme recommandé par SQL Server en utilisant le rapport Index physique des statistics. Ce rapport est disponible en cliquant avec le button droit sur la database> Rapports> Rapports standard> Indexer les statistics physiques.