• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

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  
0
heyday2004
Asked:
heyday2004
1 Solution
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now