CODEWITHSUNDEEP
sqlsql-serverviewsql-server-2008-r2
Cavyn VonDeylen
Cavyn VonDeylen

Reputation: 4239

View takes longer than query

I have a view that takes 20 seconds to run, where the exact same SQL run as it's own query takes less than a second. Anyone know a reason for this or what I could do to speed up the view?

It's a pretty straightforward view, with about 7 LEFT JOINS and a few "greatest n per group" nested queries. eg

select t.id, t.rev, t.contents
from MyTable t
inner join(
    select id, max(rev) rev
    from MyTable
    group by id
) in on t.id = in.id and t.rev = in.rev

Upvotes: 1

Views: 3239

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can speed up both by replacing the aggregation and join with a window function:

select t.id, t.rev, t.contents
from (select t.id, t.rev, t.contents, MAX(rev) over (partition by id) as maxrev
      from MyTable t
     ) t
where t.rev = t.maxrev;

The reason for the difference in performance between the view and the query is probably due to compilation. The view is compiled (and optimized) the first time it is run. Since then, the data may have changed or the way you are using it may have changed. You can recompile the view if the data has changed for a better execution plan.

Upvotes: 4

Related Questions