Slow Query when Joined View contains PIVOT

Question

I have the following view containing a PIVOT:

CREATE VIEW [vwPhoneNumberByPref]
AS
SELECT * FROM(
    SELECT ObjectKey, ObjectTypeName,r_id,MaskedNumber FROM (
        SELECT * ,ROW_NUMBER() OVER (PARTITION BY ObjectKey, ObjectTypeName ORDER BY preference) as r_id
        FROM vwPhoneNumberMasked  ) AS a WHERE r_id<=3
    )AS P
PIVOT(
    MAX(MaskedNumber) FOR r_id in ([1],[2],[3])
)AS Pvt

Which when I run it using WHERE ObjectKey = 1234 returns a result instantly. When I use this query in a join though:

Select * 
from tblPerson p 
inner join 
    vwPhoneNumberByPref ph on p.PersonID = ph.ObjectKey

it takes about 10 seconds and in the execution plan, is doing a sort on over 200,000 rows.

When not used in a join, it is doing the "sort" on a single row. I tried doing the join using CROSS APPLY which makes it marginally faster, but still takes too long and is still sorting 200,000 odd rows.

How can I improve the performance?

Additional Info:

CREATE VIEW [vwPhoneNumberMasked] AS
SELECT ph.ObjectTypeName, ph.ObjectKey, dbo.ApplyMask(ph.PhoneNumber, pt.Mask), ph.Preference
FROM tblPhoneNumber ph inner join tblPhoneType pt on
ph.PhoneTypeName = pt.Name
WHERE ph.isDeleted = 0
GO

CREATE TABLE tblPhoneType
Name varchar(20) NOT NULL,
Mask varchar(20) NOT NULL
GO

There is an index on tblPhoneNumber on ObjectTypeName, ObjectKey, Preference, isDeleted

The function dbo.ApplyMask applies the Mask from PhoneType to PhoneNumber, the mask is (##) #### ####

I am not actually running Select * from tblPerson, But even running SELECT PersonID from tblPerson INNER JOIN .... causes the query to take ages. This was just for example.

Why are you using ROW_NUMBER function , Sorry but i do not see any need of assigning row numbers here , Correct me if i am wrong you can increase efficiency by removing assigning rownumbers. — Suraj Singh, Aug 13 '13 at 12:08
@surajsingh The row number is being used to return only three values, these 3 values are then being converted into the columns in the pivot. — Taryn, Aug 13 '13 at 12:14
Can you post your table structure, the view definition of `vwPhoneNumberMasked` and show any indexes that you have? — Taryn, Aug 13 '13 at 12:15
I have aded dependant tables and indexes, sorry for omitting these. — Molloch, Aug 14 '13 at 11:28
@Molloch Can you post the create statement for the function `dbo.ApplyMask`? — Taryn, Aug 14 '13 at 15:25

score 0 · Answer 1 · edited May 23 '17 at 11:49

After you provided more information, I suggest you to try following. On the table tblPhoneNumber add index:

create index ix_tblPhoneNumber_1
    on tblPhoneNumber (isDeleted, ObjectKey, ObjectTypeName, Preference)
        include (PhoneNumber, PhoneTypeName)

On the table tblPhoneType add (if there are no) either

create unique clustered index ix_tblPhoneType
    on tblPhoneType (PhoneTypeName)

or

create unique index ix_tblPhoneType
    on tblPhoneType (PhoneTypeName) include (Mask)

Change your vwPhoneNumberMasked view as:

ALTER VIEW [vwPhoneNumberMasked] AS
SELECT ph.ObjectTypeName, ph.ObjectKey, mp.Value as MaskedNumber, ph.Preference
FROM tblPhoneNumber ph
    inner /*loop*/ join tblPhoneType pt on ph.PhoneTypeName = pt.Name
    cross apply ftMaskPhone(ph.PhoneNumber, pt.Mask) mp
WHERE ph.isDeleted = 0

Where ftMaskPhone is the one from this answer (please use Approach 2 version).

Thanks for your suggestions, I am reviewing them, in the meantime I have added more info to the original question. — Molloch, Aug 14 '13 at 11:29

Slow Query when Joined View contains PIVOT

1 Answers1