sql - Slow Query when Joined View contains PIVOT -

i have following view containing pivot:

create view [vwphonenumberbypref] select * from(     select objectkey, objecttypename,r_id,maskednumber (         select * ,row_number() on (partition objectkey, objecttypename order preference) r_id         vwphonenumbermasked  ) r_id<=3     )as p pivot(     max(maskednumber) r_id in ([1],[2],[3]) )as pvt 

which when run using where objectkey = 1234 returns result instantly. when use query in join though:

select *  tblperson p  inner join      vwphonenumberbypref ph on p.personid = ph.objectkey 

it takes 10 seconds , in execution plan, doing sort on over 200,000 rows.

when not used in join, doing "sort" on single row. tried doing join using cross apply makes marginally faster, still takes long , still sorting 200,000 odd rows.

how can improve performance?

---

additional info:

create view [vwphonenumbermasked] select ph.objecttypename, ph.objectkey, dbo.applymask(ph.phonenumber, pt.mask), ph.preference tblphonenumber ph inner join tblphonetype pt on ph.phonetypename = pt.name ph.isdeleted = 0 go  create table tblphonetype name varchar(20) not null, mask varchar(20) not null go 

there index on tblphonenumber on objecttypename, objectkey, preference, isdeleted

the function dbo.applymask applies mask phonetype phonenumber, mask (##) #### ####

i not running select * tblperson, running select personid tblperson inner join .... causes query take ages. example.

after provided more information, suggest try following. on table tblphonenumber add index:

create index ix_tblphonenumber_1     on tblphonenumber (isdeleted, objectkey, objecttypename, preference)         include (phonenumber, phonetypename) 

on table tblphonetype add (if there no) either

create unique clustered index ix_tblphonetype     on tblphonetype (phonetypename) 

or

create unique index ix_tblphonetype     on tblphonetype (phonetypename) include (mask) 

change vwphonenumbermasked view as:

alter view [vwphonenumbermasked] select ph.objecttypename, ph.objectkey, mp.value maskednumber, ph.preference tblphonenumber ph     inner /*loop*/ join tblphonetype pt on ph.phonetypename = pt.name     cross apply ftmaskphone(ph.phonenumber, pt.mask) mp ph.isdeleted = 0 

where ftmaskphone 1 this answer (please use approach 2 version).