sql - CROSS APPLY query very slow when additional column added

Question

I have a CROSS APPLY query which executes very quickly (1 second). However, if I add certain additional columns to the top SELECT, the query will run very slow (many minutes). I'm not seeing what is causing this.

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

I've tried adding/removing various columns and only when adding the 'genre' fields - e.g. genre2 (varchar(50)) - does the slowness occur. For example,

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

I would expect the query to basically have the same performance whether adding one additional column or not.

Here are the query plans without the extra column, and with.

The first table (cs) has a primary key index and an index on genre1. The second table (imdb) has a primary key index and an index on primaryTitle.

I'm not sure if those would cause any problems though.

Thanks for any suggestions.

标签： sqlsql-server

Answer 1

在您的第二个屏幕截图中，您正在对imdb_data. 这本质上是在扫描表，就好像没有索引一样。

你有两个选择。更改您的查询以使用的索引列imdb_data或创建一个新索引来覆盖此查询。