troubleshooting Question

SQL Query Optimize

Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
6 Comments1 Solution332 ViewsLast Modified:
Hello Experts,

I've got an MS SQL Stored Procedure that I'm trying to optimise. But I'm struggling, here is what I have so far -

CREATE PROCEDURE [dbo].[JobsearchRSS](
@region int,
@location int,
@sector int,
@hours int,
@jobtype int,
@clientid int,
@keywords nvarchar(50))
AS
BEGIN
SET NOCOUNT ON;
BEGIN

IF @keywords IS NOT NULL
BEGIN

DECLARE @MyQuote varchar(10)
DECLARE @MyWildCard varchar(5)

SET @MyQuote = '"'
SET @MyWildCard = '*'
SET @keywords = @MyQuote + @keywords + @MyWildCard + @MyQuote

SELECT 
A.ID ID,         
Lower(A.JBATitle) AS JobTitle,         
A.JBALocation,  
S.JBSRegion,       
A.JBACategory,         
A.JBAPayRate,
[JBAEmplymentType] JobType,
H.HoursType,         
CONVERT(CHAR(11),A.JBADatePosted,106) AS DatePosted,         
Lower(left(A.JBADescription, 1250) + '...') as JobDescription, 
C.Name,       
'/jobs/' + dbo.fixchars(A.JBATitle) + '/' + cast(A.ID as nvarchar(12)) + '/' as URL,
'/jobs/' + dbo.fixchars(Lower(S.JBSRegion)) + '/' + dbo.fixchars(Lower(A.JBALocation)) + '/' as LocationURL,
'/jobs/' + dbo.fixchars(Lower(A.JBACategory)) + '/' as SectorURL,
'/jobs/' + dbo.fixchars(Lower(C.Name)) + '/' as ClientNameURL
FROM [dbo].[JBAdvert] A
inner join dbo.Client C on C.ID = A.ClientID
inner join dbo.sector cat on cat.sector = A.JBACategory
inner join dbo.JBLocation L on L.JBLSiteID = A.JBASiteID AND L.JBLocation = A.JBALocation
inner join dbo.JBSite S on S.JBSSiteID = A.JBASiteID
inner join dbo.EmploymentType E on E.etype = A.JBAEmplymentType
inner join dbo.HoursType H on H.ID = A.[Hours]

WHERE		(S.JBSSiteID = @region or @region IS NULL) 
AND			(L.JBLID = @location or @location is null) 
AND			(cat.ID = @sector or @sector is null) 
AND			(A.[Hours] = @hours or @hours is NULL) 
AND			(E.ID = @jobtype or @jobtype IS NULL) 
AND			(C.ID= @clientid or @clientid is null) 
And		CONTAINS(JBADescription, @keywords)
ORDER BY ID DESC
END
ELSE
BEGIN
SELECT 
A.ID ID,         
Lower(A.JBATitle) AS JobTitle,         
A.JBALocation,  
S.JBSRegion,       
A.JBACategory,         
A.JBAPayRate,
[JBAEmplymentType] JobType,
H.HoursType,         
CONVERT(CHAR(11),A.JBADatePosted,106) AS DatePosted,         
Lower(left(A.JBADescription, 1250) + '...') as JobDescription, 
C.Name,       
'/jobs/' + dbo.fixchars(A.JBATitle) + '/' + cast(A.ID as nvarchar(12)) + '/' as URL,
'/jobs/' + dbo.fixchars(Lower(S.JBSRegion)) + '/' + dbo.fixchars(Lower(A.JBALocation)) + '/' as LocationURL,
'/jobs/' + dbo.fixchars(Lower(A.JBACategory)) + '/' as SectorURL,
'/jobs/' + dbo.fixchars(Lower(C.Name)) + '/' as ClientNameURL
FROM [dbo].[JBAdvert] A
inner join dbo.Client C on C.ID = A.ClientID
inner join dbo.sector cat on cat.sector = A.JBACategory
inner join dbo.JBLocation L on L.JBLSiteID = A.JBASiteID AND L.JBLocation = A.JBALocation
inner join dbo.JBSite S on S.JBSSiteID = A.JBASiteID
inner join dbo.EmploymentType E on E.etype = A.JBAEmplymentType
inner join dbo.HoursType H on H.ID = A.[Hours]

WHERE		(S.JBSSiteID = @region or @region IS NULL) 
AND			(L.JBLID = @location or @location is null) 
AND			(cat.ID = @sector or @sector is null) 
AND			(A.[Hours] = @hours or @hours is NULL) 
AND			(E.ID = @jobtype or @jobtype IS NULL) 
AND			(C.ID= @clientid or @clientid is null) 
ORDER BY ID DESC
END
END
END

GO

The Execution plan XML gives me references -

                            <ColumnReference Column="Expr1014" />
                            <ColumnReference Column="Expr1015" />
                            <ColumnReference Column="Expr1016" />
                            <ColumnReference Column="Expr1017" />
                            <ColumnReference Column="Expr1018" />
                            <ColumnReference Column="Expr1019" />
                            <ColumnReference Column="Expr1020" />

I'm not sure what those are....

92% of the query cost is on a sort?

Any suggestions here?

Are there any indexes in particular that I should be considering?

Appreciate any help you can offer...

thank you
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros