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.