Subquery Performance - Non Unique Column in Where

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Subquery Performance - Non Unique Column in Where

Author

Topic

webcosmo
Starting Member

2 Posts

Posted - 2011-08-24 : 19:35:24

I have two tables

Table Jobs

[ID] [int] IDENTITY(1,1) NOT NULL,
[title] [varchar](150) NULL,
[description] [text] NULL

Table JobSkills

[id] [int] IDENTITY(1,1) NOT NULL,
[jobId] [int] NULL,
[skill] [varchar](150) NULL

Shown above partial list of columns.

For table JobSkills I have indexed jobId column, column skill is full text indexed.

I have a stored procedure to get the list of jobs. sort of like this.

Select totalItems
,Id,title
from
(
Select Row_Number() over(Order By
CASE WHEN @sortBy Is Not Null AND @sortBy='relevance'
THEN
SkillMatchRank
END DESC
,CASE WHEN @sortBy Is Not Null AND @sortBy='date' THEN CreateDate END DESC
) As rowNumber
,COUNT(*) OVER() as totalItems
,ID,createDate,title
from Jobs J
OUTER APPLY dbo.GetJobSkillMatchRank(J.ID,@searchKey) As SkillMatchRank
Where
--where conditions here
) tempData
where
rowNumber>=CASE WHEN @startIndex>0 AND @endIndex>0 THEN @startIndex ELSE rowNumber END
AND rowNumber<=CASE WHEN @startIndex>0 AND @endIndex>0 THEN @endIndex ELSE rowNumber END

I have created a inline table valued function to get the skill matching rank.

CREATE FUNCTION [dbo].[GetJobSkillMatchRank]
(
@jobId int,
@searchKey varchar(150)
)
RETURNS TABLE
AS
RETURN
(

select SUM(ISNULL(JS2.[Rank],0)) as rank
from FREETEXTTABLE(JobSkills,skill,@searchKey) JS2
Where JS2.[Key] in (Select ID from JobSkills Where jobId=@jobId)

)

GO

Problem

Query runs super slow, more then a minute.
My observation

For the table valued function if I set jobId=1 (I do have a job with id=1) then it performs super fast as desired. I understand that jobId is not unique column on JobSkills table.

In this case how could I improve the performance???

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-08-24 : 19:57:24

You should provide example data and expected output.

webcosmo
Starting Member

2 Posts

Posted - 2011-08-24 : 21:43:27

I just did.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources