SQL Query Optimize

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

Open in new window


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" />

Open in new window


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
garethtnashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
create non-clustered indexes on the non primary keys
0
garethtnashAuthor Commented:
Sorry, excuse my ignorance, on which? and do they need columns included?
Thanks
0
plusone3055Commented:
no worries I didnt clarify my answer :)

in a nutshell creating a non-clustered index will help the searches go faster
Non-clustered indexes are a pre-sorted snapshot of all the rows in a table

CREATE NONCLUSTERED INDEX VIEW TPlayers_strPlayerName_NI
ON TPlayers ( strLastName, strFirstName )
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

garethtnashAuthor Commented:
Thanks Plusone,

There are actually loads of indexes on te various tables already...
is a Clustered Index Scan a bad thing? I'm getting 89% on one
0
PortletPaulfreelancerCommented:
The Execution plan XML gives me references -
..
I'm not sure what those are....

More insight into what they are, and the performance issues might be possible if you attach the explain plan XML as a file. I think you have to give it a .xml extension before uploading (i.e. site doesn't like .sqlplan)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
garethtnashAuthor Commented:
Thanks Both
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.