1

I'm trying to optimize a view which grabs the most recent history record for an item based on some specific criteria. I wrote a query which ran fairly quickly, and then turned it into a view.

The query looks like the following:

with specs as (
            select 't1' report, 'MC' flag, 1 typer union 
            select 't1', 'CBC', 1 union
            select 't1', 'RIC', 1 union
            select 't2', 'SMC', 1 union
            select 't3', 'IRC', 1 union
            select 't3', 'IMC', 1  union
            select 't3', 'IAC', 1  union
            select 't4', 'ISMC', 1 union
            select 't5', 'MC', 1 union
            select 't5', 'CBC', 1 union
            select 't5', 'RIC', 1 union
            select 't5', 'AC', 1 union
            select 't5', 'SMC', 1 union
            select 't5', 'SAC', 1 union
            select 't5', 'IRC', 1 union
            select 't5', 'IMC', 1 union
            select 't5', 'IAC', 1 union
            select 't5', 'ISMC', 1 union
            select 't5', 'ISAC', 1 union 
            select 't6', 'C', 2
            )               ,
    years as (
            select YEAR(getdate()) y
            union all 
            select y -1
            from years 
            where y > YEAR(getdate())-3
            )
                    select  rank() over (partition by report,PC_P.p_id order by mast.year_id desc, mast.HISTORY_ID) ranks, 
                    y.y filterYear,
                    mast.YEAR_ID, 
                    mast.HISTORY_ID,
                    PC_P.P_ID, 
                    PC_P.MAP MAP, 
                    report,
                    INSP_FLAG,
                    REASON_CODE
                    from table1 PC_P WITH(NOLOCK) 
                    Cross Join 
                    years y 
                    inner join table2 mast WITH(NOLOCK)
                    on mast.P_ID = PC_P.P_ID
                    and mast.HISTORY_ID <> -32765
                    and mast.YEAR_ID <=y.y      
                            inner join 
                    table3 ma with(nolock)
                    on mast.YEAR_ID = ma.YEAR_ID
                    and mast.HISTORY_ID =ma.HISTORY_ID
                    and mast.P_ID = ma.P_ID
                    inner join 
                    specs 
                    on (typer=1 and (flag =INSP_FLAG or REASON_CODE='C')) or (typer=2 and REASON_CODE=flag)
                    where MAP between 'town-000000000' and 'town-999999999'
                    and Report = 't1'
                    and y.y =2019

The view looks like the following:

CREATE view [dbo].[TEST_view] as
with specs as (
                select 't1' report, 'MC' flag, 1 typer union 
                select 't1', 'CBC', 1 union
                select 't1', 'RIC', 1 union
                select 't2', 'SMC', 1 union
                select 't3', 'IRC', 1 union
                select 't3', 'IMC', 1  union
                select 't3', 'IAC', 1  union
                select 't4', 'ISMC', 1 union
                select 't5', 'MC', 1 union
                select 't5', 'CBC', 1 union
                select 't5', 'RIC', 1 union
                select 't5', 'AC', 1 union
                select 't5', 'SMC', 1 union
                select 't5', 'SAC', 1 union
                select 't5', 'IRC', 1 union
                select 't5', 'IMC', 1 union
                select 't5', 'IAC', 1 union
                select 't5', 'ISMC', 1 union
                select 't5', 'ISAC', 1 union 
                select 't6', 'C', 2
                )               ,
        years as (
                select YEAR(getdate()) y
                union all 
                select y -1
                from years 
                where y > YEAR(getdate())-3
                )
                        select  rank() over (partition by report,PC_P.p_id order by mast.year_id desc, mast.HISTORY_ID) ranks, 
                        y.y filterYear,
                        mast.YEAR_ID, 
                        mast.HISTORY_ID,
                        PC_P.P_ID, 
                        PC_P.MAP MAP, 
                        report,
                        INSP_FLAG,
                        REASON_CODE
                        from table1 PC_P WITH(NOLOCK) 
                        Cross Join 
                        years y 
                        inner join table2 mast WITH(NOLOCK)
                        on mast.P_ID = PC_P.P_ID
                        and mast.HISTORY_ID <> -32765
                        and mast.YEAR_ID <=y.y      
                                inner join 
                        table3 ma with(nolock)
                        on mast.YEAR_ID = ma.YEAR_ID
                        and mast.HISTORY_ID =ma.HISTORY_ID
                        and mast.P_ID = ma.P_ID
                        inner join 
                        specs 
                        on (typer=1 and (flag =INSP_FLAG or REASON_CODE='C')) or (typer=2 and REASON_CODE=flag)

I then ran a query on the view with the same where clause as the initial query:

select * 
from [TEST_view]
where TAX_MAP between 'town-000000000' and 'town-999999999'
and Report = 't1'
and filterYear = 2019

What I found was that it took ~60 times longer to run the query on the view than the initial query.
I can only guess that it's treating the view as a subquery, and is grabbing all the rows in the db and doing calculations first, and then filtering based on the criteria in the where clause.
I took a quick look at the msdn docs and didn't see a description of how the query optimizer works when filtering a view. Why is the query on the view so much slower than the standalone query?

Stand Alone Query Plan

Query of view

  • 1
    Did you compare execution plans between fast and slow versions of the query? – Aaron Bertrand Aug 14 at 18:27
  • You might have a bad query plan for the view. Are you running different WHERE clauses against the view before comparing? You can clear the views cached plans or just DROP/RECREATE. – Deerup Aug 14 at 18:41
  • I took a look (still fairly new to reading the actual plans) and the query joins table1 to table3 then table2, whereas the query on the view joins table 2 to table 3 and joins that to table1. Considering that table1 has the smallest number of rows and the main constraint on the dataset, it should be the first table joined to or from. – Rolan Aug 14 at 18:47
  • I've edited the question to include links to the plans – Rolan Aug 14 at 19:02
  • @Lukas.Navratil I think that's just how the execution plan renders the query on a view with a where clause attached to it. The query itself doesn't have any "parameters" associated with it. – Rolan Aug 14 at 21:49
4

I will try to explain again why the ranking function doesn't allow for the filters to be applied on the lowest level possible. Here is a query and the same query but as a view and the filter applied to the view:

CREATE VIEW dbo.TEST
AS
SELECT ROW_NUMBER() OVER (ORDER BY number) RN, *
FROM master.dbo.spt_values
GO

SELECT ROW_NUMBER() OVER (ORDER BY number) RN, *
FROM master.dbo.spt_values
WHERE type = 'P'
GO

SELECT *
FROM dbo.TEST
WHERE type = 'P'

And here are the plans: You can see that the filter has been pushed as the last step in the execution plan. SQL Server must calculate the ranking function first for the whole dataset and only then it can apply the filter otherwise the result will be wrong.

Also by comparing the result you can see that the queries only look similar but actually provide different results for RN column.

Result

  • Good answer. The 2 queries are not equivalent and the conditions cannot - and should not - be pushed down. – ypercubeᵀᴹ Aug 15 at 21:05
  • @ypercubeᵀᴹ I see that the conditions aren't pushed down. Why shouldn't they conditions be pushed down? – Rolan Aug 16 at 21:47
  • 2
    Because it would change the query results. Any transformation (pushdown, etc) is only considered if it would result in an equivalent query – ypercubeᵀᴹ Aug 16 at 22:21
2

It looks like the view is slow because certain predicates are being applied later in the query plan compared to earlier in the stand alone plan.

Few examples, in the stand alone query plan, in order to retrieve the fields pc_parcel.p_id and pc_parcel.tax_map_ufmt, the optimizer chooses to do an index seek with a key lookup with a predicate on the index seek of

enter image description here

Compare that with the view plan where there is no predicate and the optimizer does an index scan

enter image description here

Notice the Estimate Rows = 887k but Actual rows = 1.7M.

Another for grabbing the field reason_code.. in the stand alone plan.. chooses to do a clusetered index seek outputting 131k rows

enter image description here

Compare that to the bad plan, where it chooses to do an index scan and outputs 14M rows

enter image description here

And one more observation, this predicate

where TAX_MAP between 'yorkt-000000000' and 'yorkt-999999999'

In the stand alone plan, it is applied to the index seek on tax_map id pc_parcel table mentioned above, but in the view plan that predicate isn't applied until almost the end in a filter action, where it takes in 34M rows and outputs only 108k

enter image description here

Maybe by refactoring your query you can try to generate a better plan for the view. I think there are more details and clues in the execution plans but hopefully these few observations can help reach an understanding of why the view is slower.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.