4
votes

Requête CROSS APPLY très lente lorsqu'une colonne supplémentaire est ajoutée

J'ai une requête CROSS APPLY qui s'exécute très rapidement (1 seconde). Cependant, si j'ajoute certaines colonnes supplémentaires en haut de SELECT , la requête s'exécutera très lentement (plusieurs minutes). Je ne vois pas ce qui en est la cause.

SELECT cs.show_title, im.primaryTitle, im.genre2

J'ai essayé d'ajouter / supprimer plusieurs colonnes et uniquement lors de l'ajout des champs "genre" - par exemple genre2 (varchar (50)) - la lenteur se produit-elle. Par exemple,

SELECT 
    cs.show_title, im.primaryTitle
FROM
    captive_state cs 
CROSS APPLY
    (SELECT TOP 1 
         imdb.tconst, imdb.titleType, imdb.primaryTitle,
         imdb.genres, imdb.genre1, imdb.genre2, imdb.genre3
     FROM 
         imdb_data imdb 
     WHERE 
         (imdb.primaryTitle LIKE cs.show_title+'%') 
         AND (imdb.titleType like 'tv%' OR imdb.titleType = 'movie')
     ORDER BY 
         imdb.titleType, imdb.tconst DESC) AS im 
WHERE 
    cs.genre1 IS NULL

Je m'attendrais à ce que la requête ait fondamentalement les mêmes performances, qu'elle ajoute une colonne supplémentaire ou non.

Voici les plans de requête sans la colonne supplémentaire, et avec.

La première table (cs) a un index de clé primaire et un index sur genre1. La deuxième table (imdb) a un index de clé primaire et un index sur primaryTitle.

Je ne sais pas si cela poserait des problèmes.

Merci pour vos suggestions.

sql sql-server

1 commentaires

Les questions sur les performances doivent inclure EXPLAIN ANALYZE et des informations sur la taille de la table, l'index, les performances de l'heure actuelle, l'heure souhaitée, etc. Slow est un terme relatif et nous avons besoin d'une valeur réelle pour comparer. Inclure également les deux requêtes

4 Réponses :

2
votes

Dans votre deuxième capture d'écran, vous effectuez une analyse d'index sur la clé primaire pour imdb_data . Il s'agit essentiellement d'analyser la table comme s'il n'y avait pas d'index.

Vous avez deux options. Modifiez votre requête pour utiliser les colonnes indexées de imdb_data ou créez un nouvel index pour couvrir cette requête.

2 commentaires

Oui, vous avez raison. Si j'ajoute une indication d'index pour utiliser l'index primaryTitle existant, la requête s'exécute très rapidement. Par exemple, dans ma requête d'origine, si je fais ceci: ** SELECT cs.show_title, im.primaryTitle, im.genre2: FROM imdb_data imdb WITH (INDEX (idx_primaryTitle)) ** la requête s'exécute très rapidement. Ainsi, l'optimiseur rejette en quelque sorte l'index si je souhaite renvoyer une colonne supplémentaire, à moins que je ne le force à l'utiliser. Bizarre.

@BeachBum sans connaître les détails de votre index, il est difficile de dire exactement pourquoi. Mais une raison courante est que les colonnes que vous sélectionnez peuvent ne pas être dans l'index. Vous pouvez toujours INCLURE les colonnes de votre requête dans l'index afin que la requête soit entièrement couverte par l'index. Quoi qu'il en soit, si ma réponse vous a été utile, pourriez-vous la marquer comme réponse? Cela m'aiderait avec cette réputation! :)

2
votes

Peut-être passer à une alternative pour le CROSS APPLY en tête

SELECT TOP 1 WITH TIES 
 cs.show_title, 
 imdb.tconst, imdb.titleType, imdb.primaryTitle,
 imdb.genres, imdb.genre1, imdb.genre2, imdb.genre3
FROM captive_state cs 
JOIN imdb_data imdb 
  ON imdb.primaryTitle LIKE cs.show_title+'%'
 AND (imdb.titleType = 'movie' OR imdb.titleType LIKE 'tv%')
WHERE cs.genre1 IS NULL
ORDER BY ROW_NUMBER() OVER (PARTITION BY cs.show_title ORDER BY imdb.titleType, imdb.tconst DESC)

5 commentaires

Oui, cela fonctionne comme une alternative et c'est une bonne solution. Cependant, je ne sais toujours pas pourquoi l'ajout d'une colonne supplémentaire prendrait la requête de 1 seconde à 10 minutes.

Mattkwish a expliqué pourquoi le deuxième plan est lent. Il scanne la table. Étant donné que ces autres champs sont inclus, l'optimiseur de requêtes a dû penser qu'il était plus rapide de le rechercher et de le récupérer directement à partir de la table. Si la première requête n'avait besoin que du titre, le pourrait l'obtenir à partir de cet index seul.

@LukStorms vous avez volé ma réponse! ;) mais vous avez tout à fait raison, la meilleure approche est d'utiliser l'index existant si vous ne pouvez pas le modifier.

@Mattkwish Ouais, cela m'a surpris aussi que BeachBum ait changé cette approbation pour la mienne. Je n'avais pas expliqué dans ma réponse pourquoi ce plan avait changé, puisque vous l'avez déjà fait de toute façon. Cette question m'a un peu surpris que l'optimiseur fasse quelque chose de stupide comme ça. Je veux dire, cette requête externe dans son SQL ne sélectionne même pas ces champs supplémentaires. Peut-être que l'optimiseur est plus affiné pour trouver le meilleur plan lors de l'utilisation de jointures normales, par rapport à l'utilisation d'une application croisée ou d'une requête imbriquée.

Désolé d'avoir changé les approbations. Je l'ai changé à l'original (je pensais que vous pouviez en approuver plus d'un). Dans tous les cas, oui, l'optimiseur agit très mal ici et ce serait formidable d'obtenir une explication de l'équipe SQL Server (2016) sur la façon dont cela pourrait se produire.

0
votes

Vous pouvez inclure des colonnes supplémentaires pour indexer [imdb_data]. [idx_primary_table]. (le nom n'est pas lisible sur la capture d'écran) :

CREATE INDEX idx_name ON [imdb_data].[idx_primary_table](same cols as in original)
INCLUDE (genre1, genre2, genre3) WITH (DROP_EXISTING=ON)

1 commentaires

Ok, je pourrais faire ça. Mais c'est un peu comme dire que SELECT firstname, lastname FROM mytable prend 1 seconde mais SELECT firstname, lastname, middlename FROM mytable prend 10 minutes, sauf si j'ajoute des colonnes à mon index. Pour moi, cela n'a pas de sens. Dans la première requête, il est montré que les enregistrements sont récupérés en 1 seconde. L'ajout d'une colonne supplémentaire ne devrait pas le ralentir de 10 minutes.

0
votes

Essayez d'utiliser "join" avec "row_number ()" au lieu de "apply"

select
     dat.primaryTitle
    ,dat.show_title
from (
    select
         imdb.primaryTitle
        ,cs.show_title
        ,row_number() over (partition by cs.show_title order by imdb.titleType, imdb.tconst DESC) as rn
    from imdb_data imdb
    inner join captive_state cs on imdb.primaryTitle LIKE cs.show_title+'%'
    where (imdb.titleType like 'tv%' OR imdb.titleType = 'movie')
    and cs.genre1 IS NULL
) dat
where dat.rn = 1

0 commentaires