Query tuning SQL Server 2008

Question

I have a view name "vw_AllJobsWithRecruiter".

ALTER VIEW dbo.vw_AllJobsWithRecruiter
AS
SELECT TOP(SELECT COUNT(iJobID_PK) FROM dbo.tbUS_Jobs)
         iJobId_PK AS JobId,
         dbo.ufn_JobStatus(iJobId_PK) AS JobStatus,
         dbo.ufn_RecruiterCompanyName(iJobId_PK) AS CompanyName,
         sOther AS OtherCompanyName
FROM dbo.tbUS_Jobs
WHERE bDraft = 0
ORDER BY dtPostedDate DESC

This view contains only 3278 number of rows.

If I execute the below query :

SELECT * FROM vw_AllJobsWithRecruiter
WHERE  OtherCompanyName LIKE '%Microsoft INC%'

It is taking less than a second to execute.

Now my problem is:

If I use the query below query:

SELECT * FROM vw_AllJobsWithRecruiter
WHERE CompanyName LIKE '%Microsoft INC%' 
      OR OtherCompanyName LIKE '%Microsoft INC%'

It is taking 30 seconds to execute and from the front end it is throwing timeout error. The function is here:

CREATE Function [dbo].[ufn_RecruiterCompanyName] (@JobId bigint)      
RETURNS nvarchar(200)      
AS      
BEGIN      
 DECLARE @ResultVar nvarchar(200)     
 DECLARE @RecruiterId bigint     

 select @RecruiterId = iRecruiterId_FK from dbo.tbUS_Jobs     with (Nolock)   
 where iJobId_PK = @JobId;      

 Select @ResultVar = sCompanyName from dbo.tbUS_RecruiterCompanyInfo     with (Nolock)   
 where iRecruiterId_FK = dbo.ufn_GetParentRecruiterID(@RecruiterId)      

 return isnull(@ResultVar,'')      

END 

The other function

CREATE Function [dbo].[ufn_GetParentRecruiterID](@RecruiterId bigint)  
returns bigint  
as  
begin   
declare @ParentRecruiterId bigint  

SELECT @ParentRecruiterId = iParentId FROM dbo.tbUS_Recruiter with (Nolock)   
WHERE iRecruiterId_PK = @RecruiterId  

IF(@ParentRecruiterId = 0)  
 SET @ParentRecruiterId = @RecruiterId  

RETURN @ParentRecruiterId  
end 

My questions are

1. Why it is taking so much time to execute?
2. How can I reduce the execution time?

Thanks a lot for your attention.

Answer 1

The first query only calls dbo.ufn_RecruiterCompanyName() only for the rows returned, it filters on a stored value. For the second Query, SQL Server needs to call the ufn for all of the rows. Depending on the function, this might cause the delay.

Check this in Query Analyzer, and try to avoid the second Query ^^

After taking a look at the custom function I suggest rewriting that View using joined tables. When doing the lookups in such functions, SQL Server calls them for every Row that it touches or delivers. Using a LEFT JOIN allows the Server to use the Indexes and Key much faster and should deliver the Data in less than a second.

Without all of the custom functions and a Definition of all the tables, I cannot give you an Example of that new View, but it should look a bit like this:

SELECT
    jobs.Jobid,
    jobstatus.Jobstatus,
    recruiter.Company
FROM jobs
LEFT JOIN jobstatus ON jobs.Jobid = jobstatus.Jobid
LEFT JOIN recruiter ON jobs.Recruiterid = recruiter.Recruiterid

Answer 2

The problem is your nested function calls.

You are calling ufn_RecruiterCompanyName in your WHERE clause, albeit indirectly.

What this means is, your WHERE clause is non-Sargable, and must run that function for every row.

That function also calls ufn_GetParentRecruiterID. Since that is in your WHERE clause within the first function, and also non-Sargable, you are basically doing two table scans per row in your table.

Replace the function calls with JOINs and you will see a huge boost in performance.