Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland asked on

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
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
garethtnash

8/22/2022 - Mon
SOLUTION
plusone3055

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
garethtnash

Sorry, excuse my ignorance, on which? and do they need columns included?
Thanks
plusone3055

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 )
ASKER
garethtnash

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
garethtnash

Thanks Both