Outer apply takes a long time when it contains string compare in it

Question

I have a query:

with cte as
(   
//some select statement
)
    select -
    // also some code here
     from cte a 
     outer apply
(select top 1 position,traffic,tags,url,Domain,Load_Date,trend_date
    from cte b 
    where b.Date_ID<=a.Date_ID and 
        b.Load_Date is not null and 
        a.Domain is null and 
        a.Project_Id=b.Project_Id and
        a.SE_Id=b.SE_Id  and
        a.Keyword=b.keyword 
        order by a.Date_ID desc
        )x

My cte returns almost 3 million rows. This query takes a very long time to finish (every 4 minutes it returns only 500 rows)

But the following query without Keyword compare in outer apply, is very fast:

with cte as
(   
//some select statement
)
    select 
    // also some code here
     from cte a 
     outer apply
(select top 1 position,traffic,tags,url,Domain,Load_Date,trend_date
    from cte b 
    where b.Date_ID<=a.Date_ID and 
        b.Load_Date is not null and 
        a.Domain is null and 
        a.Project_Id=b.Project_Id and
        a.SE_Id=b.SE_Id  and
        order by a.Date_ID desc
        )x

The Problem is, that I need this Keyword comparison in my query. My question is now, How should I change my original query to have a better performance? good to know:

Project_Id is int
SE_Id is int
Keyword is nvarchar(2000)

You would have to examine the execution plans. In all likelihood. the first allows the use of an index. — Gordon Linoff, Feb 27 '19 at 13:17
I would suggest you try to load your data into a temporary table instead of using cte in this case. — Denis Rubashkin, Feb 27 '19 at 13:28
@DenisRubashkin I have test it, but not a big change in performance — Kaja, Feb 27 '19 at 13:30

score 0 · Answer 1 · answered Feb 27 '19 at 14:32

0

Include Keyword in the index(es) being used by the second query.

answered Feb 27 '19 at 14:32

Tab Alleman

31,483
7
36
52

Outer apply takes a long time when it contains string compare in it

1 Answers1