My database has a view that calls a function. Joining on that view is surprisingly slow. Is there a way to speed up joining to a view like that?
I've included 2 queries which illustrate the issue. They give identical results. The first query takes 6 seconds to run. According to the estimated execution plan, the first query spends 82% of its time in "Nested Loops (Inner Join)". The second query runs much faster, but for more complicated queries I would have to use a join instead of a sub query.
I'm happy to provide more information about my particular system, but I suspect this is a generally applicable issue.
--ptynbr is int --ptyid is varchar --vwTest has 8307 rows --map has 126 rows --Instrument and Party are plain old tables. Create function [dbo].[fnTest](@ptynbr int) returns varchar(40) as begin declare @result varchar(40); select @result = ptyid from dbo.[Party] where ptynbr = @ptynbr; return (@result); end; GO CREATE view [dbo].[vwTest] as select dbo.[fnTest](i.ptynbr) PartyName, i.insaddr from Instrument i GO select distinct i.PartyName into #map from [vwTest] i --Takes 6 seconds select i.insaddr from [vwTest] i join #map m on m.PartyName = i.PartyName --Takes < 1 second. select i.insaddr from [vwTest] i where i.PartyName in (select PartyName from #map)