Posted on 2008-11-03
I have a query, the performance is very bad:
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
-- set up my in-memory table
DECLARE @contact_list table
(rowId int identity(1,1),
-- set up row counters and cursor variables
declare @rowId int,
set @full_name_list = ''
-- insert the rows into the work table
insert into @contact_list
select distinct employeenames from company
where @officeID = @officeID