June 20, 2012 at 6:27 am
How do i get select within the contains clause to work with the dynamic sql ?
if (@Name <> '')
Begin
set @QueryCondition = @QueryCondition + ' and contains((select doccontent from alldocuments dc where p.idnew = dc.Id),''' + @Name1 + ''') '
End
The code below works fine!
if (@Name <> '')
Begin
set @QueryCondition = @QueryCondition + ' and contains(dc.doccontent,''' + @Name1 + ''') '
End
Thanks
June 20, 2012 at 7:48 am
So is this a question or just explaining how you fixed it?
_______________________________________________________________
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 20, 2012 at 7:54 am
Its a question, i am trying to use the select within the contains statement and cant get it to work!
June 20, 2012 at 7:58 am
isuckatsql (6/20/2012)
and cant get it to work!
What does this mean? Not getting the correct results or giving an error? My first guess is that your subquery is returning more than 1 result (this coming from someone who does not use contains). Also, kind of hard to tell without the full DML what the full query will be and therefore not possible to really help you.
Jared
CE - Microsoft
June 20, 2012 at 7:59 am
Well you can't use a subquery in CONTAINS like that. What exactly are you trying to do here? This looks like part of where clause in dynamic sql but your second code snippet looks like it is correct.
_______________________________________________________________
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 20, 2012 at 8:06 am
At the moment i am using the following:
inner loop join alldocuments dc With (nolock) on p.idnew = dc.id
set @QueryCondition = @QueryCondition + ' and contains(dc.doccontent,''' + @Name1 + ''') '
To increase performance, i tried a HASH join but it was slower, so i thought i would try to remove the 'inner join' statement.
(9 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AllDocuments'. Scan count 0, logical reads 5476521, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Profiles'. Scan count 2, logical reads 194724, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Regions'. Scan count 0, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Cities'. Scan count 0, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AcademicExperienceTypes'. Scan count 0, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SecurityClearances'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MilitaryExperienceTypes'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NewJobTypes'. Scan count 5, logical reads 95, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Industries'. Scan count 0, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
June 20, 2012 at 8:07 am
USE [JobPortalIANdell297061412]
GO
/****** Object: StoredProcedure [dbo].[GetAllResumesSearchedDynamicQuery2FTI_6-19-12part2] Script Date: 6/20/2012 10:01:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- =============================================
ALTER PROCEDURE [dbo].[GetAllResumesSearchedDynamicQuery2FTI_6-19-12part2]
-- 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,recentjobtitle,recentwage,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,InputDate,
experience,HomePhone,CellPhone,WorkPhone,PrimaryEmailAddress,SecondaryEmailAddress,City,State,newdate,UserName,RowNumber)
AS
(
select
p.idnew as Id,
p.recentjobtitle,
p.recentwage,
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.id = p.idnew) 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
--ROW_NUMBER() OVER (ORDER BY p.id DESC) AS RowNumber TWO SECONDS SLOWER
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.idnew = dc.id
--added 5-24-12 next line
--inner loop join AllRecentJobTitles arjt With (nolock) on arjt.Id=p.idnew
--inner loop join AllRecentWages rw With (nolock) on rw.Id=p.idnew
--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(p.RecentJobTitle,''' + @Position + ''') '
end
else if (@Phrase = 2)
Begin
set @QueryCondition = @QueryCondition + ' and contains(p.RecentJobTitle,''' + @Position + ''') '
end
else if (@Phrase = 3)
Begin
set @QueryCondition = @QueryCondition + ' and contains(p.RecentJobTitle,''' + @Position + ''') '
end
else if (@Phrase = 4)
Begin
set @QueryCondition = @QueryCondition + ' and p.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 top 4000 (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 20, 2012 at 8:10 am
Here is the plan.
June 20, 2012 at 8:12 am
Why all the join hints? Do you know what they do? Can you explain and justify very clearly why you need them? (to make it faster does not count, you need details). And why nolocks all over the place?? Consider using isolation instead if you dirty reads are acceptable.
_______________________________________________________________
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 20, 2012 at 8:24 am
I dont know what they do, other than make the query faster!
I have had a few DBA's and Microsoft Enterprise support work with me on this query, and was told it was optimal.
Microsoft got their Full Text guys involved, as well as their query performance tuning guys and spend a fair bit of time reviewing numerous reports.
I dont know enough SQL to disagree.
June 20, 2012 at 8:28 am
WOW not sure where to begin with this. You should start by reading Gail's blog about catch all queries. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
It actually looks like you have probably read parts of this. You should pay close attention to the last part as your procedure here is vulnerable to sql injection.
I am guessing since you posted an execution plan that performance of this is less than optimal? One possible element of this is parameter sniffing. Again Gail has a great series of posts about how to handle this. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
Then there are the 20 or so subqueries, this seems to be the direct result of your profiles table not being normalized (Industry1id, etc).
A number of these subqueries should be changed to joins instead (Cities, regions).
Then drop the join hints, they will cause you far more grief than any possible performance gains.
Use isolation instead of NOLOCK.
The last line before you execute your dynamic sql has a top 4000 but there is no order by. This means the top 4000 will change. SQL will just give you the 4000 it finds the easiest to retrieve each run.
_______________________________________________________________
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 20, 2012 at 8:30 am
isuckatsql (6/20/2012)
I dont know what they do, other than make the query faster!I have had a few DBA's and Microsoft Enterprise support work with me on this query, and was told it was optimal.
Microsoft got their Full Text guys involved, as well as their query performance tuning guys and spend a fair bit of time reviewing numerous reports.
I dont know enough SQL to disagree.
They may make your query faster today but in the long run it will have a huge swing the other way when the amount of data in the table changes.
You should hire a consultant to help with this. There is just way too much going on here for an online forum.
_______________________________________________________________
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 20, 2012 at 8:30 am
isuckatsql (6/20/2012)
I dont know what they do, other than make the query faster!I have had a few DBA's and Microsoft Enterprise support work with me on this query, and was told it was optimal.
Microsoft got their Full Text guys involved, as well as their query performance tuning guys and spend a fair bit of time reviewing numerous reports.
I dont know enough SQL to disagree.
"Optimal for what?" is the question you have to ask yourself. My boss loves when queries run fast, but if I gave him wrong financial data (which can happen as a result of NOLOCK) at the expense of speed I would be fired. Not saying that the DBAs looking at your code are not good DBAs, but the title of DBA certainly doesn't make you an expert. Sorry, I know you came here asking specifically about the CONTAINS thing, but I want to always try to help in more ways than 1.
Jared
CE - Microsoft
June 20, 2012 at 8:44 am
Thanks for the feedback guys!
BTW I have hired consultants, and Microsoft Enterprise support was not free either, but it seems to be a case of "the more cooks spoil the broth".
Everyone seems to have a slightly different opinion of what will work, and what will not!
June 20, 2012 at 8:50 am
Sean,
The Joins were slower than the subqueries, and actually adding the recentwages and recentjobtitle fields back to the profiles table, gained 30% in performance! That is just something i did not expect!
Thanks
Ian
BTW the more normalization i do, the slower the query gets ! E.F. Codd would not be happy.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply