Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query optimization.

Posted on 2008-11-03
2
Medium Priority
?
173 Views
Last Modified: 2010-03-19
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
Comment
Question by:heyday2004
2 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22874001
can you post here the entire function including the cursor?
0
 
LVL 25

Accepted Solution

by:
jogos earned 2000 total points
ID: 22874804
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question