Query fast, but when in a VIEW, it's slow - due to ROW_NUMBER

Question

I have a query that when run, it's result is instant.

However, I paste the exact same query into VIEW, and the results take 6 seconds to reply.

For example,

SELECT ... FROM MyTables WHERE PersonID = x

runs fast.

But create a view with:

SELECT ... FROM MyTables 

And then call the view:

SELECT * FROM MyView WHERE PersonID = x

And it runs slow.

Actual Query:

select ROW_NUMBER() over(partition by h.Id order by h.[SysStartTime]) as VersionNUmber,
      h.Id,
      fac.HIC,
      ... plus 18 other columns from the joined tables.

from   [hist].[A_View] as h
inner join [dbo].[Facilities] as fac
      on fac.Id = h.FacilityId
inner join ref.FormStatus as r_fs
      on r_fs.Id = h.FormStatusId
inner join TableA as data
      on data.Id = h.dataId
inner join Consultants as c
      on c.Id = h.ConsultantId
inner join dbo.Specialties spec
      on spec.Id = h.SpecialtyId
inner join dbo.Users modifieduser
      on modifieduser.Id = h.ModifiedByUserId
left join ref.ARefTable as r_uc
      on r_uc.Id = h.refId
cross apply [dbo].[getPersonUrn](h.PersonId, h.AnotherIdId) as PersonURN

(Note, I am changing some table names and columns as we're in quite a confidential area)

I notice that 97% of the time, it's in a Sort (Top N Sort), when executing the view. In the query, that 34%, but the plans are completely different.

I suspected parameter sniffing, but don't think that's an issue with Views.

I've actually just 'fixed' it, but no idea why.

My first column in my select is a ROW_NUMBER.

SELECT ROW_NUMBER() over(partition by h.Id order by h.[SysStartTime]) as` VersionNumber,

Removing that, and I get instant results. Not sure why, as both the columns I order by and partition by, are already in the result set.

Answer 1

1) Here ROW_NUMBER applies to filtered data only:

SELECT ROW_NUMBER(), ... FROM MyTables WHERE PersonID = x

At first it filters by PersonID, then it computes ROW_NUMBER

2) Here ROW_NUMBER applies to all of the data:

CREATE VIEW MyView as
  select ROW_NUMBER(), ... FROM MyTables

SELECT * FROM MyView WHERE PersonID = x

and only after proceeding full data the filter by PersonID is applied

it's the same as

SELECT * FROM
(SELECT ROW_NUMBER(), ... FROM MyTables
) t
WHERE t.PersonID = x

check out the example:

GO
CREATE VIEW dbo.test_view
AS
    SELECT ROW_NUMBER() OVER (ORDER BY NAME) rn, o.name, o.[object_id]
    FROM sys.objects o
GO
SET SHOWPLAN_XML ON
GO
SELECT rn, o.name, o.[object_id] FROM dbo.test_view o
WHERE OBJECT_ID < 100
GO
SELECT ROW_NUMBER() OVER (ORDER BY NAME) rn, o.name, o.[object_id] FROM sys.objects o
WHERE OBJECT_ID < 100
GO
SET SHOWPLAN_XML OFF
GO
DROP VIEW dbo.test_view
GO

With the view filter operation is in the very end. So plans are different actually.

Answer 2

I found that running it in a view, it counts every record with ROW_NUMBER() then selects. When you run it in a query it just counts the records returning with ROW_NUMBER()

Answer 3

I found moving all my OVER clauses outside of the primary SELECT made a huge difference in performance when calling my view with a filter. If I ran my view's code by itself with a filter, it would return instant results, but when in a view with the same filter, it would take over almost two minutes to return the same results. After moving the OVERS out like below, the filtered view runs instantly now.

Old way:

CREATE VIEW as dbo.vw_SalesTotals 
AS
SELECT 
    OrderDetails.Item
    , OrderDetails.Sales
    , OrderDetails.DocID
    , SUM(OrderDetails.Sales) OVER (PARTITION BY OrderDetails.DocID) as TotalDocSales
FROM dbo.OrderDetails

New way:

CREATE VIEW as dbo.vw_SalesTotals 
AS
SELECT 
    Ord.Item
    , Ord.Sales
    , Ord.DocID
    , SUM(Ord.Sales) OVER (PARTITION BY Ord.DocID) as TotalDocSales
FROM (
    SELECT 
        OrderDetails.Item
        , OrderDetails.Sales
        , OrderDetails.DocID
    FROM dbo.OrderDetails
) Ord