SQL Server – Are Views Treated Like Subqueries?

sql serversql-server-2017

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

Best Answer

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'

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.

Related Solutions

SQL Server Linked Server – Query Performance Tips

Your problem begins and ends with statistics and estimates. I have reproduced your situation on my servers and found some interesting hints, and a workaround solution.

First things first, let's take a look at your execution plan:
When a view is used we can see that a filter is applied after the Remote Query is executed, while without the view there was no filter applied at all. The truth is that the filter was applied inside the Remote Query, at the remote server, before retrieving the data over the network.
Well, obviously applying the filter at the remote server and thus retrieving less data is a better option, and obviously that only happens when not using a view.

So... what is so intersting...?

Surprisingly, when I changed the filter from cognome = 'test' to cognome = N'test' (unicode representation of the string) the view used the same execution plan as the first query did.
I guess the reason is that somehow when using the view SQL Server estimated that there will be a small number of rows returning from the (remote) query, and that a local filtering will be cheaper, but when SQL Server had to implicit convert NVARCHAR to VARCHAR, statistics could no longer be used and the decision to filter locally was not taken.
I have looked for the statistics locally, but the view had no statistics, so my guess is that the view uses the remote statistics in a way that ad-hoc query does not, and than takes the wrong decision.

OK, so what solves the problem?

I stated earlier that there is a workaround (at least until someone comes up with a better solution), and no, I don't mean using unicode for your strings.
I wanted to give an answer first, I still have to find why, but when using an Inline Function SQL Server behaves exactly the same as with the query (without view), so replacing the view with the function will give the same result, in a simple query, and with good peformance (at least in my environment).

My code suggestion for you is:

CREATE FUNCTION fn_anagrafiche2()
RETURNS table
AS
RETURN 
(
    SELECT * 
    FROM dolph2.agendasdn.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM dolph2.acampanet.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM municipio2.dbnet.dbo.vistaanagraficagrp
)
GO

The query will then be:

SELECT * 
FROM fn_anagrafiche2()
WHERE cognome = 'prova'

This works on my servers, but of course test it first.
Note: I do not recommend using SELECT * at all, as it is prone to future errors, I simply used it because it was in your question and there was no need for me to change that when I can add this remark instead :)

Does Detach/Attach or Offline/Online Clear Buffer Cache for Database?

I initially thought you were on to something here. Working assumption was along the lines that perhaps the buffer pool wasn't immediately flushed as it requires "some work" to do so and why bother until the memory was required. But...

Your test is flawed.

What you're seeing in the buffer pool is the pages read as a result of re-attaching the database, not the remains of the previous instance of the database.

And we can see that the buffer pool was not totally blown away by the detach/attach. Seems like my buddy was wrong. Does anyone disagree or have a better argument?

Yes. You're interpreting physical reads 0 as meaning there were not any physical reads

Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads 0, read-ahead reads 768, lob logical reads 94, lob physical reads 4, lob read-ahead reads 24.

As described on Craig Freedman's blog the sequential read ahead mechanism tries to ensure that pages are in memory before they're requested by the query processor, which is why you see zero or a lower than expected physical read count reported.

When SQL Server performs a sequential scan of a large table, the storage engine initiates the read ahead mechanism to ensure that pages are in memory and ready to scan before they are needed by the query processor. The read ahead mechanism tries to stay 500 pages ahead of the scan.

None of the pages required to satisfy your query were in memory until read-ahead put them there.

As to why online/offline results in a different buffer pool profile warrants a little more idle investigation. @MarkSRasmussen might be able to help us out with that next time he visits.

Best Answer

Related Solutions

SQL Server Linked Server – Query Performance Tips

Does Detach/Attach or Offline/Online Clear Buffer Cache for Database?

Related Question