Sql-server – SQL Join slower than subquery on View with a Function

sql-server-2008

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)

Best Answer

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.