June 12, 2012 at 12:45 pm
If i add a TOP 4000 to the select statement, it also changes "SELECT Max(RowNumber) FROM CTE " to 4000.
How do i use the Select Top 4000, but keep the original Max(RowNumber).
I have tried a few different ways, but cant get it to work 🙁
Thanks for any help!
USE [JobPortalIANdell2970]
GO
/****** Object: StoredProcedure [dbo].[GetAllResumesSearchedDynamicQuery2FTI] Script Date: 6/12/2012 2:36:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- =============================================
ALTER PROCEDURE [dbo].[GetAllResumesSearchedDynamicQuery2FTI]
-- Add the parameters for the stored procedure here
@Name varchar(250),
@Name1 varchar(250),
@Date int,
@Stateid int,
@CityId int,
@Industry1id int,
@Industry2id int,
@Industry3id int,
@Industry4id int,
@Industry5id int,
@Jobtype1id int,
@Jobtype2id int,
@Jobtype3id int,
@Jobtype4id int,
@Jobtype5id int,
@Experience int,
@Militaryid int,
@Securityid int,
@Degree int,
@Skillsid int,
@FutureSkillsid int,
@ExpectedSalary int,
@ExpectedMaxSalary int,
@Position varchar(250),
@Phrase int,
@fromRec int,
@toRec int
AS
DECLARE @TotalRecord INT
declare @Query as varchar(8000)
declare @QueryForCount as varchar(8000)
DECLARE @QueryCondition VARCHAR(8000)
SET @QueryCondition =''
SET @QueryForCount = ''
set @Query = '
WITH CTE ( Id,regionid,cityid,countryid,Industry1id,Industry2id,Industry3id,Industry4id,Industry5id,Industry1,Industry2,Industry3,Industry4,Industry5,
Desiredempid1,Desiredempid2,Desiredempid3,Desiredempid4,Desiredempid5,Jobtype1,Jobtype2,Jobtype3,Jobtype4,Jobtype5,TotalYears,Militaryid,
Securityid,militaryname,securityname,Degree1id,Degree2id,Degree3id,degree,title,InputDate,salary,
experience,HomePhone,CellPhone,WorkPhone,PrimaryEmailAddress,SecondaryEmailAddress,City,State,newdate,UserName,RowNumber)
AS
(
select top 4000
p.id as Id,
p.regionid,
p.cityid,
p.countryid,
p.Industry1id,
p.Industry2id,
p.Industry3id,
p.Industry4id,
p.Industry5id,
(select i.Name from Industries i where i.Id = p.Industry1id) as Industry1,
(select i.Name from Industries i where i.Id = p.Industry2id) as Industry2,
(select i.Name from Industries i where i.Id = p.Industry3id) as Industry3,
(select i.Name from Industries i where i.Id = p.Industry4id) as Industry4,
(select i.Name from Industries i where i.Id = p.Industry5id) as Industry5,
p.Desiredempid1,
p.Desiredempid2,
p.Desiredempid3,
p.Desiredempid4,
p.Desiredempid5,
(select n.Name from NewJobTypes n where n.Id = p.Desiredempid1) as Jobtype1,
(select n.Name from NewJobTypes n where n.Id = p.Desiredempid2) as Jobtype2,
(select n.Name from NewJobTypes n where n.Id = p.Desiredempid3) as Jobtype3,
(select n.Name from NewJobTypes n where n.Id = p.Desiredempid4) as Jobtype4,
(select n.Name from NewJobTypes n where n.Id = p.Desiredempid5) as Jobtype5,
p.TotalYears,
p.Militaryid,
p.Securityid,
(select m.Name from MilitaryExperienceTypes m where m.Id = p.Militaryid) as militaryname,
(select s.Name from SecurityClearances s where s.Id = p.Securityid) as securityname,
p.Degree1id,
p.Degree2id,
p.Degree3id,
case
when degree3id > degree2id then (select d.Name from AcademicExperienceTypes d where p.Degree3id=d.Id)
when degree3id < degree2id then (select d.Name from AcademicExperienceTypes d where p.Degree2id=d.Id)
when degree2id > degree1id then (select d.Name from AcademicExperienceTypes d where p.Degree2id=d.Id)
else (select d.name from AcademicExperienceTypes d where p.Degree1id=d.id)
end
as degree,
--(select a.RecentJobTitle from AllRecentJobTitles a where a.Uniqueid = p.id) as title,
arjt.RecentJobTitle as title,
--case when len(w.position) < 30 then w.position
--else
--substring(w.position,1,30) + ''...'' end
--as Title,
p.InputDate,
--p.SalaryMinID,
--p.SalaryMaxID,
--(select s.Value from Salaries s where s.Id = p.SalaryMinID) as salary,
--(select s.Value from Salaries s where s.Id = p.SalaryMaxID) as maxsalary,
rw.RecentWage as salary,
--p.TotalYears as experience,
p.TotalYrsExp as experience,
p.HomePhone,
p.CellPhone,
p.WorkPhone,
p.PrimaryEmailAddress,
p.SecondaryEmailAddress,
(select c.name from Cities c where p.cityid = c.Id) as City,
(select r.abbreviatedname from regions r where p.regionid = r.Id) as State,
--c.Name as City,
--r.abbreviatedName as State,
--isnull(c.Name,'''') + '', '' + isnull(r.abbreviatedName,'''') as Location,
substring(cast(InputDate as varchar(20)),1,12) as newdate,
--(select CONVERT(datetime, InputDate, 107)) as newdate,
--CONVERT(datetime, date_string, 121) PERSISTED
isnull(p.FirstName,'''') + '' '' + isnull(p.MiddleName + '' '','''') + isnull(p.LastName,'''') as UserName,
ROW_NUMBER() OVER (ORDER BY (select 1)) AS RowNumber
from profiles p With (nolock)
--inner join workexperiences w With (nolock) on w.ProfileId = p.id
inner loop join alldocuments dc With (nolock) on p.id = dc.uniqueid
--added 5-24-12 next line
inner loop join AllRecentJobTitles arjt With (nolock) on arjt.UniqueId=p.id
inner loop join AllRecentWages rw With (nolock) on rw.UniqueId=p.id
--Left Outer loop join Cities c With (nolock) ON c.id = p.cityid
--inner loop join Cities c With (nolock) ON c.id = p.cityid
--Left OUter Loop join Regions r With (nolock) ON r.id = p.regionid
--inner Loop join Regions r With (nolock) ON r.id = p.regionid
where p.allowrecruiters=1 '
if (@Name <> '')
Begin
set @QueryCondition = @QueryCondition + ' and contains(dc.doccontent,''' + @Name1 + ''') '
End
if (@Position <> '')
Begin
if (@Phrase = 1)
Begin
set @QueryCondition = @QueryCondition + ' and contains(arjt.RecentJobTitle,''' + @Position + ''') '
end
else if (@Phrase = 2)
Begin
set @QueryCondition = @QueryCondition + ' and contains(arjt.RecentJobTitle,''' + @Position + ''') '
end
else if (@Phrase = 3)
Begin
set @QueryCondition = @QueryCondition + ' and contains(arjt.RecentJobTitle,''' + @Position + ''') '
end
else if (@Phrase = 4)
Begin
set @QueryCondition = @QueryCondition + ' and arjt.RecentJobTitle = ''' + cast(@Position as varchar(100)) + ''''
end
End
if (@Stateid <>0)
Begin
set @QueryCondition = @QueryCondition + ' and p.regionid =' + cast(@Stateid as varchar(10))
End
if (@CityId <>0)
Begin
set @QueryCondition = @QueryCondition + ' and p.cityid =' + cast(@CityId as varchar(10))
End
if (@Experience <>0)
Begin
set @QueryCondition = @QueryCondition + ' and p.TotalYears =' + cast(@Experience as varchar(10))
End
if (@Militaryid <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Militaryid =' + cast(@Militaryid as varchar(10))
End
if (@Securityid <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Securityid =' + cast(@Securityid as varchar(10))
End
if (@Industry1id <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Industry1id =' + cast(@Industry1id as varchar(10))
End
if (@Industry2id <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Industry2id =' + cast(@Industry2id as varchar(10))
End
if (@Industry3id <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Industry3id =' + cast(@Industry3id as varchar(10))
End
if (@Industry4id <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Industry4id =' + cast(@Industry4id as varchar(10))
End
if (@Industry5id <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Industry5id =' + cast(@Industry5id as varchar(10))
End
if (@Jobtype1id <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Desiredempid1 =' + cast(@Jobtype1id as varchar(10))
End
if (@Jobtype2id <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Desiredempid2 =' + cast(@Jobtype2id as varchar(10))
End
if (@Jobtype3id <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Desiredempid3 =' + cast(@Jobtype3id as varchar(10))
End
if (@Jobtype4id <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Desiredempid4 =' + cast(@Jobtype4id as varchar(10))
End
if (@Jobtype5id <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Desiredempid5 =' + cast(@Jobtype5id as varchar(10))
End
if (@Degree <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Degree1id =' + cast(@Degree as varchar(10))
End
if (@Degree <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Degree2id =' + cast(@Degree as varchar(10))
End
if (@Degree <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.Degree3id =' + cast(@Degree as varchar(10))
End
if (@Date <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and InputDate >=''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
End
if (@ExpectedSalary <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.SalaryMinID >= ' + cast(@ExpectedSalary as varchar(10))
End
if (@ExpectedMaxSalary <> 0)
Begin
set @QueryCondition = @QueryCondition + ' and p.SalaryMaxID <= ' + cast(@ExpectedMaxSalary as varchar(10))
End
SET @Query = @Query + @QueryCondition +' ) SELECT (SELECT Max(RowNumber) FROM CTE ) AS TotalCount ,*, isnull(City,'''') + '', '' + isnull(State,'''') as Location FROM CTE WHERE RowNumber BETWEEN ' + Convert(varchar(100),@fromRec)+ ' AND ' + Convert(varchar(100),@toRec) + ' OpTION( Maxdop 2) '
exec (@Query);
SET STATISTICS TIME OFF
June 12, 2012 at 12:57 pm
Instead of a CTE, insert into a temp table, and query the count(*) and top (4000) from that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2012 at 1:00 pm
I would also suggest what Gus said but to add to that, your original query is selecting top but there is no order by. This means your max was not the same as the whole result set but it was also random because top with no order by is not going to always produce the same rows. It will produce the first x rows that sql finds easiest to retrieve for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2012 at 1:08 pm
Also Why are you using inner loop joins? Those loop joins can be a major performance issue unless you are 100% certain (and can explain) why you need that join hint.
and the nolock hint?
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
You also might want to read up on sql injection. This procedure you posted is very vulnerable.
Last but not the least of concerns is this appears to be a catch-all query. You should read Gail's blog post about this type of query and how to make them work faster.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2012 at 2:14 pm
I used a Temp Table, but it made the query a lot slower!
I also used the loop joins and nolock because they improved performance.
By combining CTE, loops joins and nolock, i made the query time drop from 9 seconds to 4.5 seconds!
Thanks for your help!
June 12, 2012 at 2:31 pm
I have a suggestion, post the DDL for the tables involved, some sample data (something that without restrictions would return say 10 to 12 rows of data), the expected results based on the sample data with the restriction that the final query should return 5 rows data. Be sure to provide an explaination why those 5 rows are selected.
June 12, 2012 at 8:08 pm
I removed the ‘GUID’ field and used an ‘INT’ ID field instead for the primary key and clustered indexes, and got the query down to:
SQL Server Execution Times:
CPU time = 2449 ms, elapsed time = 2788 ms.
Now i just need to fix the above issues 🙂
June 12, 2012 at 8:30 pm
isuckatsql (6/12/2012)
I removed the ‘GUID’ field and used an ‘INT’ ID field instead for the primary key and clustered indexes, and got the query down to:SQL Server Execution Times:
CPU time = 2449 ms, elapsed time = 2788 ms.
Now i just need to fix the above issues 🙂
If you post what I've asked for, plus (as I think about it), the actual execution plan (saved and uploaded as a .sqlplan file) I am sure we could help you improve the performance even more.
June 14, 2012 at 7:23 am
isuckatsql (6/12/2012)
I used a Temp Table, but it made the query a lot slower!I also used the loop joins and nolock because they improved performance.
By combining CTE, loops joins and nolock, i made the query time drop from 9 seconds to 4.5 seconds!
Thanks for your help!
You sped it up, but it's getting the wrong results, if I'm reading your question correctly. If it gets the wrong results, does it actually matter how fast it is?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 14, 2012 at 7:43 am
isuckatsql (6/12/2012)
I used a Temp Table, but it made the query a lot slower!I also used the loop joins and nolock because they improved performance.
By combining CTE, loops joins and nolock, i made the query time drop from 9 seconds to 4.5 seconds!
Thanks for your help!
You should read the articles I posted. NOLOCK is not a magic go fast pill. It is however a chance to get incorrect data and even potentially corrupt your database. It brings along a host of bugs that can be nearly impossible to debug because you can't reproduce the issues consistently.
Why does the loop join improve the performance on this query? If you can't explain that at a highly technical level discussing the inner workings of the optimizer you should NOT use join hints like that. It might make your query run slightly faster today but will cause you unbelievable amounts of anguish in the future. Your data will get bigger and the loop join will not be correct. Somebody else (or maybe even yourself) will use this as an example (I didn't see a comment explaining why to use that hint) and apply it on a different query and the performance will be hideous.
Again I warn you about sql injection, your code is vulnerable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2012 at 9:45 am
isuckatsql (6/12/2012)
I removed the ‘GUID’ field and used an ‘INT’ ID field instead for the primary key and clustered indexes, and got the query down to:SQL Server Execution Times:
CPU time = 2449 ms, elapsed time = 2788 ms.
Now i just need to fix the above issues 🙂
I apologize in advance but it is time for a short rant.
You have asked for help with this query. You have made a few changes that appear to have improved your query's performance. The problem I am having is that you are totally ignoring our requests for additional information. We have asked for the DDL for the tables (including the indexes currently defined), sample data that represents your problem domain, expected results based on the sample data, the actual execution plan for the current query. If you read my last post asking for this information, I asked for enough sample data that if queried unrestricted would return 10 to 12 rows of data and that your expected resutls should show what you expect if the query was restricted to 5 or 6 rows and for you to explain why those rows were selected.
Have you provided us with the requested information? Short answer, no.
If you would really like our help, you really need to help us by providing us with the information we have requested. Without all we can do is shoot in the dark and that isn't going to be very helpful to you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply