Query optimization.

I have a query, the performance is very bad:
select
...
cfGetEmployeeList(officeID),
...
from Table A
join Table B
join Table C

Basically, for every row, it's funning the user function and return a employee name list for this office.(there are thousands of offices in the database). How to improve the performance for this kind of user functions involved in the queries? Also, there is cursor operation is this function (not listed here), is it also a reason it runs slow too? Thanks a lot.

The user function details is listed below and if I comment out this user function: cfGetEmployeeList(officeID)
The the query is running veru fast.
 CREATE FUNCTION [dbo].cfGetEmployeeList  
(  
 @officeID int  
)  
RETURNS nvarchar(4000)  
AS  
BEGIN  
 
  -- set up my in-memory table  
  DECLARE @contact_list table  
     (rowId int identity(1,1),  
   full_name nvarchar(1000)  
   )  
 
  -- set up row counters and cursor variables  
  declare @rowId int,  
  @maxRowId int,  
  @full_name nvarchar(1000),  
  @full_name_list nvarchar(4000)  
 
  set @full_name_list = ''  
 
  -- insert the rows into the work table  
 
  insert into @contact_list  
     (full_name)  
 
  select distinct employeenames from company
  where @officeID  = @officeID  
  return @full_name_list  
end  
heyday2004Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
can you post here the entire function including the cursor?
0
jogosCommented:
select ... function(),
from ...
=> this means that for every row your function will get executed. When that function is slow the whole query will suffer.  For each row you create worktable, a distinct select ,  cursor to concatinate.....
Can't expect it to get very fast if you have to repeat that a couple of thousend times

It would be a hell off a benifit if you don't do this a thousend times but do it in one single movement.  Basicly the function will  have to change :
- no înputparameter of officeId
- your function returns a table with officeId and concatinated string
- your distinct-select  + filter (WHERE) on officeId  replace by group by officeId /name
-- in the cursor where you concatinate you will have to create a row in a temp-table each time you get on a diffrent officeid.

And finaly your select your function will have to be called as if it is a table, so it will be another  JOIN
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.