SQL Join slower than subquery on View with a Function

Question

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)

Answer 1

When you join on the field in the view which is generated by the function, SQL will need to scan the entire table and execute the function for every row in order to do the join, so this will inevitably be much slower than the equivalent set based operation.

You can improve matters a lot using a computed column. The result of your function will be computed and cached by SQL each time dependent data is changed.

Answer 2

Stop using the function, it is killing your performance.

Scalar functions are executed once for each row, so you have to scan the entire table in your example, then as you are scanning the table execute the function once for each row before the join can be completed.

Answer 3

What your View and Function do is nothing more a View with a simple JOIN would do:

CREATE VIEW dbo.vwTest AS 
  SELECT p.ptyid AS PartyName, i.insaddr
  FROM dbo.Instrument i
    JOIN dbo.Party AS p 
      ON p.ptynbr = i.ptynbr
GO