CROSS APPLY query very slow when additional column added

user_1496743 0
Viewed 352

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.

Est Execution Plan1

Est Execution Plan2

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.

sql-server sql
4 Answers

In your second screenshot, you're performing an Index Scan on the primary key for imdb_data. This is essentially scanning the table as if there is no index.

You have two options. Either change your query to use the indexed columns of imdb_data or create a new index to cover this query.

user_64199090

Maybe switch to an alternative for the topped CROSS APPLY

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)
user_40034190

You could include additional columns to index [imdb_data].[idx_primary_table]. (name is not readable from screenshot):

enter image description here

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

Try to use "join" with "row_number()" instead of "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
user_50755180
Related