2
votes

T-SQL Sélectionnez où "IN" très mauvais impact sur les performances

lorsque vous utilisez le code suivant:

SELECT * INTO #StoreIdsPermision FROM (SELECT StoreId FROM dbo.FN_Inv_GetListOfStoreIdCheckPermission(15,3019)) StoreIdsPermision;

Le temps d'exécution est de 4 minutes.

et lorsque vous utilisez le même code avec la syntaxe de virgule dans l'instruction "WHERE IN", le le temps d'exécution est de 3 secondes, pourquoi? et comment résoudre ce problème?

SELECT distinct

        Id,
        StoreName,
        ......,
        ......,

    from INV_Transactions
        inner join ....
        left outer join ....

    WHERE StoreId in (4,7,9,15,22,........)

REMARQUE: le temps d'exécution de l'instruction suivante est inférieur à 1 seconde et #StoreIdsPermision d'environ 140 lignes uniquement

SELECT * INTO #StoreIdsPermision FROM (SELECT StoreId FROM dbo.FN_Inv_GetListOfStoreIdCheckPermission(15,3019)) StoreIdsPermision;

SELECT distinct

        Id,
        StoreName,
        ......,
        ......,

    from INV_Transactions
        inner join ....
        left outer join ....

    WHERE StoreId in (select Id from #StoreIdsPermision)

sql sql-server tsql

11 commentaires

Combien de temps prend SELECT Id FROM #StoreIdsPermision lorsque vous l'exécutez seul?

moins d'une seconde

Veuillez ajouter 2 plans d'exécution réels à votre question

@MohamedElsayedAli IN n'affecte pas les performances. Les index manquants le font. Vous n'avez pas fourni les schémas de table, les index ou les plans d'exécution, il n'est donc pas possible de dire ce qui ne va pas. Je parie que # StoreIdsPermision.Id n'est pas couvert par un index, forçant le serveur à analyser toute la table #StoreIdsPermision pour chaque ligne de la requête externe

Je vais joindre les plans d'exécution réels.

Le codage en dur des valeurs génère très probablement une recherche d'index sur le plan d'exécution, tandis que l'utilisation de SELECT fait probablement une semi-jointure gauche sur les enregistrements après avoir été extraites des autres jointures de votre requête. Vous verrez cette différence assez clairement en analysant les plans d'exécution sur les deux sélections. Veuillez utiliser brentozar.com/pastetheplan pour partager les plans.

#StoreIdsPermision environ 140 lignes uniquement

Le tri distinct semble être la partie la plus chère. Voulez-vous vraiment distinct?

Trier distinct en cher en deux requêtes

Lorsque vous utilisez des valeurs littérales, les estimations de cardinalité seront probablement plus précises que lorsque vous utilisez une table temporaire. Avec les littéraux, il est capable d'utiliser les statistiques pour estimer chaque valeur spécifique de StoreId codée en dur et les combiner. Avec le tableau, il utilisera une approche différente de l'alignement de l'histogramme qui peut être moins précise. Des estimations différentes peuvent entraîner des plans différents.

Résolu par 1. FORCEPLAN ON, 2. remplacer toute jointure interne par une jointure externe gauche

3 Réponses :

2
votes

Vous avez confirmé que la première partie de la première requête n'est pas à l'origine du problème?

WHERE EXISTS (SELECT 1 from #StoreIdsPermision SIP WHERE INV_Transactions.StoreId  = SIP.StoreId)

Si le code ci-dessus n'est pas à l'origine du problème de performances, vous pouvez ajouter un index à votre table temporaire #StoreIdsPermision

CREATE INDEX sip1 ON #StoreIdsPermision (StoreId);

J'essaierais deux autres choses (en gardant toujours l'index créé sur la table temporaire)

1 - en remplaçant votre clause where avec celui-ci

SELECT * 
INTO #StoreIdsPermision 
FROM 
    (SELECT StoreId 
     FROM dbo.FN_Inv_GetListOfStoreIdCheckPermission(15, 3019)) StoreIdsPermision;

2 - mettre un plan de force au début de la deuxième requête et un plan de force désactivé à la fin. 99,9% du temps, l'optimiseur fait les choses correctement ou du moins assez bien, mais parfois ce n'est pas le cas. Dans votre cas, je ne m'attendrais pas à une telle différence de performance des deux approches.

10 commentaires

le temps d'exécution de l'instruction select into inférieur à 1 seconde, et lors de l'ajout d'un index pour #StoreIdsPermision rien de nouveau

@MohamedElsayedAli cela ne veut pas dire grand-chose. Affichez les plans d'exécution. À l'heure actuelle, vous n'avez pas fourni suffisamment d'informations pour diagnostiquer, ni prouvé qu'il y avait quelque chose de mal avec IN

je joindrai les plans d'exécution réels

#StoreIdsPermision environ 140 lignes uniquement

@MohamedElsayedAli J'ai ajouté deux autres suggestions, à essayer. Je ne crois pas que vous ayez fait quelque chose de mal.

Pourquoi suggérez-vous de créer un index sur la table temporaire? Vouliez-vous dire créer un index sur INV_Transactions?

TRES BON Suggestion numéro deux, lorsque vous ajoutez SET FORCEPLAN ON dans le premier et SET FORCEPLAN OFF dans la dernière requête, le temps d'exécution est normal, que se passe-t-il?

@SalmanA La table temporaire est utilisée dans la requête suivante, donc l'ajout d'un index aurait pu aider à obtenir un plan plus optimal. C'est une astuce courante, mais elle fait généralement une différence sur les grandes tables temporaires.

@MohamedElsayedAli Je ne sais pas avec certitude. Lorsque des tables temporaires sont impliquées, le problème pour l'optimiseur est de ne pas savoir à l'avance la taille des tables temporaires et donc de ne pas toujours sélectionner le plan optimal, il se peut que les statistiques de la table ne soient pas à jour également. Mais utiliser des tables temporaires dans t-sql est parfaitement valide (je trouve le code plus facile à lire). Un mot d'avertissement sur l'utilisation de set forceplan, certaines personnes ont une réaction allergique, personnellement je crois que cela existe parce que ce sont des cas où il est nécessaire que l'optimiseur soit très bon mais pas parfait.

@EponymeWeb j'ai remplacé toute la jointure interne par la jointure externe gauche et a bien fonctionné également sans plan de force

0
votes

Normalement, ce ne serait pas si grave, il semble donc que la requête durera 3 secondes car elle a déjà été mise en cache.

Vous pouvez utiliser le script sql ci-dessous pour obtenir une requête de vérification du cache:

SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan
FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
where text like '%IN (4,7,9,15,22,........)%'
   or text like '% in (select Id from #StoreIdsPermision)%'
ORDER BY cplan.usecounts DESC

Si la requête de la table temporaire n'est pas dans le cache et la requête normale dans le résultat du cache, cela explique pourquoi cela prend quatre minutes et les trois autres secondes.

0 commentaires

0
votes

où .... dans ... (1,2,3) exécute essentiellement ou contre chaque élément de cette liste. Les 3 minutes correspondent au (sélectionnez Id à partir de ...) sans index. @EponymeWeb dit construire un index; oui, mais vérifiez ces performances par rapport à #StoreIdsPermission en tant que table permanente avec un index permanent. Lorsque vous videz puis remplissez cette table, assurez-vous de reconstruire les statistiques.

0 commentaires