Link to home
Create AccountLog in
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
SOLUTION
Avatar of plusone3055
plusone3055
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of garethtnash

ASKER

Sorry, excuse my ignorance, on which? and do they need columns included?
Thanks
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 )
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
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks Both