Click here to Skip to main content
15,881,689 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
Hi everyone,

I have a view name "vw_AllJobsWithRecruiter".

SQL
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 :
SQL
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:
SQL
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 functions are:
SQL
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


And

SQL
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.
Posted
Updated 6-Apr-11 18:33pm
v2

You both functions can be converted into one nested query which will be much faster than the logic distributed into 2 funtions....

Thanks
 
Share this answer
 
Few explanations:

- Why it's taking so much time: One reason is because you're using a condition that most likely forces a full table scan. Since you are searching for a string anywhere from a varchar field, even if you have indexes on those fields, they are not usable. The situation could be very different if you had an index on the company names AND you would search for a string starting with something, like: CompanyName LIKE 'SomeCompany%'.

- Ensure that you have a (good) primary (or unique) key on the table.

- Check what the user defined functions do. Part of the cost may be coming from those.

- The amount of rows returned from the view (3278) doesn't matter. The key thing is: how many rows you have in the base table. This is where the data is fetched from and if a full table scan is performed, it's done on the base table.

- What else to do: if you need to use wildcards on both ends, full text index is a good option. Also check the statistical distribution of the bDraft field. If 0 is rare, index it. This way the view can more easily fetch a smaller subset from the base table.
 
Share this answer
 
Comments
arindamrudra 7-Apr-11 0:13am    
In the base table there are almost 200000 data.
Wendelius 7-Apr-11 0:18am    
This explains whe slow behaviour. Did you go through the steps I proposed?
arindamrudra 7-Apr-11 0:25am    
Yes, I gone through your answer, but the main problem is "dbo.ufn_RecruiterCompanyName(iJobId_PK)" here. For its execution it is scanning two tables for each row. Thats where the problem lies and I checked the indexing of the table.
Wendelius 7-Apr-11 0:29am    
Yes, those ufn's were one thing I suspected as I wrote.

Can you post the SQL from the function?
arindamrudra 7-Apr-11 0:34am    
I updated my question and I wrote the functions that are responsible for delay.
Looks like you need some primary keys and indexes on tbUS_Jobs, this will speed up your query.
 
Share this answer
 
Have a look at your query execution plan, see where the cost is

* Put your SQL in a new query window
* Choose Query -> display estimated execution plan

You can also use Tools -> Database Engine Tuning to help you with this

Full Text Search[^] is something you might want to look into as well
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900