May 21, 2013 at 12:05 pm
USE [JobPortal]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[GetAllJobsSearched]
-- Add the parameters for the stored procedure here
@Title varchar(250),
@Industry int,
@Industry2 int,
@Industry3 int,
@Date int,
@JobTitle int,
@JobType int,
@Experience int,
@education int,
@State int,
@City int,
@Salary int,
@MaxSalary int
WITH RECOMPILE
AS
DECLARE @ActualDate DateTime = cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20));
IF (@Title = '')
SET @Title = NULL;
WITH EMPLOYMENT_OPPORTUNITIES_CTE(rownumber, id,title,contactperson,lastmodified,description,workexperiencetypeid,
workexperience,jobtypeid,AcademicExperienceTypeId,
industryid,industryid2,industryid3,salaryminid,salarymaxid,
jobTitle,city,state,PostalCode,positions,deadline)
AS(
SELECT top 4000
ROW_NUMBER() OVER (ORDER BY lastmodified DESC) RowNumber,
e.id,title,contactperson,lastmodified,e.description,workexperiencetypeid,
isnull((select we.[Name] from workexperiencetypes we where we.id=e.workexperiencetypeid),'') as workexperience,
isnull((select jot.[Name] from jobtypes jot where jot.id=e.jobtypeid),'') as jobtype,
isnull((select edu.[Name] from Degree edu where edu.Id=e.AcademicExperienceTypeId),'') as education,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid),'') as industryname,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid2),'') as industryname2,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid3),'') as industryname3,
salaryminid,salarymaxid,
isnull((select jt.[Name] from jobTitles jt where jt.id=e.jobtypeid),'') as jobTitle,
isnull((select ci.[Name] from cities ci where ci.id=c.cityid),'') as city,
isnull((select r.[AbbreviatedName] from regions r where r.id=c.regionid),'') as state,
c.PostalCode,
positions,
substring(cast(e.lastmodified as varchar(20)),1,12) as deadline
--ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
from EmploymentOpportunities e With (nolock)
inner join companys c on e.officeid=c.id
where (@Title IS NULL or title = @Title)
and (@Industry = 0 OR e.industryid = @Industry)
and (@Industry2 = 0 OR Industryid2 = @Industry2)
and (@Industry3 = 0 OR Industryid3 = @Industry3)
and (@Date = 0 OR lastmodified >= @Date)
and lastmodified is not null and lastmodified > @ActualDate
and (@JobTitle = 0 OR title = @JobTitle)
and (@JobType = 0 OR jobtypeid = @JobType)
and (@Experience = 0 OR workexperiencetypeid = @Experience)
and (@Education = 0 OR academicexperiencetypeid = @education)
and (@State = 0 OR c.RegionId = @State)
and (@City = 0 OR c.CityId = @City)
and (@Salary = 0 OR SalaryMinID >= @Salary)
and (@MaxSalary = 0 OR SalaryMaxID <= @MaxSalary)
)
SELECT
id,
P1.lastmodified as deaddate,
P1.Title,
P1.city + ', ' + state + ' ' + PostalCode as Location,
contactperson as ContactPerson,description as Description,workexperience,
jobtypeid as jobtype,academicexperiencetypeid as education,industryid as industryname,industryid2 as industryname2,industryid3 as industryname3,
salaryminid as salary,salarymaxid as maxsalary,jobtitle as jobTitle,
positions as Positions,deadline, RowNumber,
(select max(RowNumber) from EMPLOYMENT_OPPORTUNITIES_CTE) as TotalCount FROM (
SELECT
RowNumber,
id,lastmodified,title,city,state,PostalCode,contactperson,description,workexperience,
jobtypeid,academicexperiencetypeid,industryid,industryid2,industryid3,salaryminid,salarymaxid,jobtitle,positions,deadline
FROM EMPLOYMENT_OPPORTUNITIES_CTE ) p1
The above query in its current state searches 600k records in approx. 300 ms.
I have tried numerous ways to do dynamic sorting on LastModified, which is a date field, or Title, which is a Job Title field, or City, which is a City Name Field.
Regardless of which method I have tried, the dynamically sorted query always takes approx. 8 seconds !
At the moment I am doing the sorting in C#, but would like to be able to do the sorting in SQL, if I can get the time under one second, and the data sorted in the correct order.
Any suggestions would be greatly appreciated.
Thanks
The above fields are also sorted ascending or descending.
May 21, 2013 at 12:23 pm
You should convert your stored procedure to use dynamic SQL.
The article below will explain the many good reasons for doing this.
SQL in the Wild: Catch-all queries
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
May 21, 2013 at 12:26 pm
well, because your query is a catch-all-query, it's going to be a table scan, which will not be efficient at all; read a little bit more hear:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
you could probably address some performance points with that.
i don't see an order by in your query you pasted, but i assume there's another version you are using that does feature it?
Lowell
May 21, 2013 at 12:49 pm
well, because your query is a catch-all-query, it's going to be a table scan, which will not be efficient at all; read a little bit more hear:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
It doesn't have to be a table scan, particularly if the rows have a unique key in them. You can create an inline view that returns only the keys that meet your criteria (I use analytics for paging), then join against those IDs on ID to SELECT *. I have a catch all query that runs probably 10-20 times a second over fairly large (50 million) tables with no problems. Of course, the columns being searched against are indexed.
May 21, 2013 at 12:57 pm
Break up your cte/derived table chain:
WITH EMPLOYMENT_OPPORTUNITIES_CTE(
rownumber, id,title,contactperson,lastmodified,description,workexperiencetypeid,
workexperience,jobtypeid,AcademicExperienceTypeId,
industryid,industryid2,industryid3,salaryminid,salarymaxid,
jobTitle,city,state,PostalCode,positions,deadline)
AS
(
SELECT top 4000 -- top 4000 what? Hair colour? Use ORDER BY or risk random and unpredictable results!
ROW_NUMBER() OVER (ORDER BY lastmodified DESC) RowNumber,
e.id,title,contactperson,lastmodified,e.description,workexperiencetypeid,
isnull((select we.[Name] from workexperiencetypes we where we.id=e.workexperiencetypeid),'') as workexperience,
isnull((select jot.[Name] from jobtypes jot where jot.id=e.jobtypeid),'') as jobtype,
isnull((select edu.[Name] from Degree edu where edu.Id=e.AcademicExperienceTypeId),'') as education,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid),'') as industryname,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid2),'') as industryname2,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid3),'') as industryname3,
salaryminid,salarymaxid,
isnull((select jt.[Name] from jobTitles jt where jt.id=e.jobtypeid),'') as jobTitle,
isnull((select ci.[Name] from cities ci where ci.id=c.cityid),'') as city,
isnull((select r.[AbbreviatedName] from regions r where r.id=c.regionid),'') as state,
c.PostalCode,
positions,
substring(cast(e.lastmodified as varchar(20)),1,12) as deadline
--ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
from EmploymentOpportunities e With (nolock)
inner join companys c on e.officeid=c.id
where (@Title IS NULL or title = @Title)
and (@Industry = 0 OR e.industryid = @Industry)
and (@Industry2 = 0 OR Industryid2 = @Industry2)
and (@Industry3 = 0 OR Industryid3 = @Industry3)
and (@Date = 0 OR lastmodified >= @Date)
and lastmodified is not null and lastmodified > @ActualDate
and (@JobTitle = 0 OR title = @JobTitle)
and (@JobType = 0 OR jobtypeid = @JobType)
and (@Experience = 0 OR workexperiencetypeid = @Experience)
and (@Education = 0 OR academicexperiencetypeid = @education)
and (@State = 0 OR c.RegionId = @State)
and (@City = 0 OR c.CityId = @City)
and (@Salary = 0 OR SalaryMinID >= @Salary)
and (@MaxSalary = 0 OR SalaryMaxID <= @MaxSalary)
)
SELECT *
INTO #Temp
FROM EMPLOYMENT_OPPORTUNITIES_CTE
SELECT
id,
P1.lastmodified as deaddate,
P1.Title,
P1.city + ', ' + state + ' ' + PostalCode as Location,
contactperson as ContactPerson,
description as Description,
workexperience,
jobtypeid as jobtype,
academicexperiencetypeid as education,industryid as industryname,industryid2 as industryname2,industryid3 as industryname3,
salaryminid as salary,salarymaxid as maxsalary,jobtitle as jobTitle,
positions as Positions,
deadline,
RowNumber,
TotalCount = COUNT(*) --(select max(RowNumber) from EMPLOYMENT_OPPORTUNITIES_CTE)
FROM #Temp
ORDER BY Whatever
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 21, 2013 at 1:12 pm
Thanks for all the feedback!
Michael,
I just changed from dynamic sql to structured sql, due to concerns about sql injection !
May 21, 2013 at 1:16 pm
isuckatsql (5/21/2013)
Thanks for all the feedback!Michael,
I just changed from dynamic sql to structured sql, due to concerns about sql injection !
If you do dynamic SQL correctly, there is no danger of SQL Injection.
May 21, 2013 at 2:37 pm
Not only do you have the dreaded IS NULL OR construct, you also have a bunch of correlated SELECT..(SELECT..) calls too.
I have had success in examples such as this simply getting the KEY value(s) from the MINIMUM number of table(s) required to get the set of keys you want data out for. Put JUST THE KEY(s) into a temp table (NOT table variable), and then go back and get the actual output data required. If those secondary hits are index seeks for a few hundreds or perhaps even few thousand rows, it could be WAY more efficient than some big scans/hashes/sorts.
Also, I agree with Dynamic SQL being the right tool for this job (with SQL Injection protection of course). You can do it other ways (see Gail's blog post), but dynamic SQL can have an added benefit of easily NOT touching a table that you pass in a NULL parameter against and you only join in case you need to filter on it.
I will add that I believe that SQL 2012 (perhaps with SP1+) has fixed the issues with OPTION (RECOMPILE) not handling the IS NULL OR construct, so that might be something to try out (without requiring dynamic sql).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply