Query optimization.

Posted on 2008-11-03
Last Modified: 2010-03-19
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  
 @officeID int  
RETURNS nvarchar(4000)  
  -- 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  
  select distinct employeenames from company
  where @officeID  = @officeID  
  return @full_name_list  
Question by:heyday2004
    LVL 37

    Expert Comment

    can you post here the entire function including the cursor?
    LVL 25

    Accepted Solution

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    Modifying SQL 2008/2012 PARTITIONS 3 39
    SQL Query 2 45
    Help with SQL joins 9 32
    Order by but want it in specific order 2 14
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now