Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
You should provide the code for the view and also the execution plans that you get on those queries –  Lamak Mar 28 '12 at 21:12
add comment

migrated from stackoverflow.com Apr 1 '12 at 4:41

This question came from our site for professional and enthusiast programmers.

3 Answers

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.

share|improve this answer
    
yes, scalar functions are often a very poor chocie for performance as they are the equivalent of a cursor, forcing the system to do row-by-agonizing-row processing. –  HLGEM Mar 28 '12 at 21:20
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.