September 26, 2011 at 1:23 pm
Thanks, i will keep playing with it, until i figure it out!
September 27, 2011 at 3:33 pm
Is this what you meant?
The times went down from 12 seconds to 6-7 seconds 🙂
Can you see anything i did wrong, or any other improvements?
Thanks!
USE [JobPortal]
GO
/****** Object: StoredProcedure [dbo].[GetAllResumesSearchedDynamicQuery2FTIBUP92711works ] Script Date: 09/27/2011 17:25:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- =============================================
ALTER PROCEDURE [dbo].[GetAllResumesSearchedDynamicQuery2FTIBUP92711works ]
-- Add the parameters for the stored procedure here
@Name varchar(250),
@Name1 varchar(250),
@Date int,
@Stateid int,
@CityId int,
@Industryid int,
@Degree int,
@Skillsid int,
@FutureSkillsid int,
@ExpectedSalary int,
@ExpectedMaxSalary int,
@Position varchar(250),
@Phrase int,
@fromRec int,
@toRec int
AS
DROP TABLE ##Ian_Temp
create TABLE ##Ian_Temp
(
RowId int IDENTITY(1,1),
id uniqueidentifier,
regionid int,
cityid int,
countryid int,
IndustryID int,
AcademicExperienceTypeID int,
Title nvarchar(100),
InputDate datetime,
SalaryMinID int,
SalaryMaxID int,
Homephone nvarchar(20),
CellPhone nvarchar(20),
WorkPhone nvarchar(20),
PrimaryEmailAddress nvarchar(100),
SecondaryEmailAddress nvarchar(100),
UserName nvarchar(100)
)
declare @Query as varchar(8000)
set @Query = '
select p.id as Id,p.regionid,p.cityid,p.countryid,
p.IndustryID,
p.AcademicExperienceTypeID,
case when len(w.position) < 30 then w.position
else
substring(w.position,1,30) + ''...'' end
as Position,
p.InputDate,
p.SalaryMinID,
p.SalaryMaxID,
p.HomePhone,p.CellPhone,p.WorkPhone,p.PrimaryEmailAddress,p.SecondaryEmailAddress,
isnull((select [Name] + '' '' from salutationtypes st
where p.SalutationId=st.id),'''') + isnull(p.FirstName,'''') + '' '' + isnull(p.MiddleName + '' '','''') + isnull(p.LastName,'''') as UserName
from profiles p inner join workexperiences w on w.ProfileId = p.id
inner join documents dc on p.id = dc.profileid
where p.allowrecruiters=1 and dc.documentstatusid=1
and p.InputDate >=''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
if (@Date <> 0)
Begin
set @Query = @Query + ' and p.InputDate >=''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
End
if (@Name <> '')
Begin
set @Query = @Query + ' and contains(dc.doccontent,''' + @Name1 + ''') '
End
if (@Position <> '')
Begin
if (@Phrase = 1)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 2)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 3)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 4)
Begin
set @Query = @Query + ' and w.position = ''' + cast(@Position as varchar(250)) + ''''
end
End
if (@Stateid <>0)
Begin
set @Query = @Query + ' and p.regionid =' + cast(@Stateid as varchar(10))
End
if (@CityId <>0)
Begin
set @Query = @Query + ' and p.cityid =' + cast(@CityId as varchar(10))
End
if (@Industryid <>0)
Begin
set @Query = @Query + ' and p.IndustryId =' + cast(@Industryid as varchar(10))
End
if (@Degree <> 0)
Begin
set @Query = @Query + ' and p.AcademicExperienceTypeID =' + cast(@Degree as varchar(10))
End
if (@Date <> 0)
Begin
set @Query = @Query + ' and InputDate >=''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
End
if (@ExpectedSalary <> 0)
Begin
set @Query = @Query + ' and p.SalaryMinID >= ' + cast(@ExpectedSalary as varchar(10))
End
if (@ExpectedMaxSalary <> 0)
Begin
set @Query = @Query + ' and p.SalaryMaxID <= ' + cast(@ExpectedMaxSalary as varchar(10))
End
Insert into ##Ian_Temp
exec (@Query);
CREATE CLUSTERED INDEX IDX_C_Users_UserID ON ##Ian_Temp(id)
select
id ,
--regionid ,
--cityid ,
--countryid ,
(select top 1 c.Name from cities c where c.id=cityid) as City,
(select top 1 r.abbreviatedName from regions r where r.id=regionid) as State,
(select top 1 sum(endyear - startyear) from workexperiences w where w.ProfileId=##Ian_Temp.id) as Experience,
(select top 1 i.name from industries i where i.id = IndustryID) as Industry,
(select top 1 w.Name from AcademicExperienceTypes w where w.Id = AcademicExperienceTypeID) as Degree,
Title,
InputDate ,
(select top 1 s.value from salaries s where s.id = SalaryMinID) as Salary,
(select top 1 s.value from salaries s where s.id = SalaryMaxID) as MaxSalary,
Homephone ,
CellPhone ,
WorkPhone ,
PrimaryEmailAddress ,
SecondaryEmailAddress ,
UserName ,
(select max(RowId) from ##Ian_Temp) as TotalCount from ##Ian_Temp
Where RowId between 0 and 5000
DROP TABLE ##Ian_Temp
--exec (@Query)
September 27, 2011 at 3:59 pm
From permanent tables to global temporary tables. That's going to give you even more fun race-conditions and incorrect results if multiple of those queries are run concurrently.
I know you're new to SQL, but there is very good documentation available.
Start with this: http://msdn.microsoft.com/en-us/library/ms177399.aspx
Homephone nvarchar(20),
CellPhone nvarchar(20),
WorkPhone nvarchar(20),
I don't know about your part of the world, but down here phone numbers have numbers and maybe + and -, they don't typically have Arabic, Japanese, Korean and the like characters in them.
if (@Phrase = 1)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 2)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 3)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
Why 3 IF statements with exactly the same result? If that's not a mistake, then this is at least cleaner
IF (@Phrase IN (1,2,3))
SET @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
select
id ,
--regionid ,
--cityid ,
--countryid ,
(select top 1 c.Name from cities c where c.id=cityid) as City,
(select top 1 r.abbreviatedName from regions r where r.id=regionid) as State,
(select top 1 sum(endyear - startyear) from workexperiences w where w.ProfileId=##Ian_Temp.id) as Experience,
(select top 1 i.name from industries i where i.id = IndustryID) as Industry,
(select top 1 w.Name from AcademicExperienceTypes w where w.Id = AcademicExperienceTypeID) as Degree,
Title,
InputDate ,
(select top 1 s.value from salaries s where s.id = SalaryMinID) as Salary,
(select top 1 s.value from salaries s where s.id = SalaryMaxID) as MaxSalary,
...
Subqueries with TOP (1) can be incredibly inefficient. Join the tables, don't use subqueries.
Widen the index [JobPortalIAN].[dbo].[WorkExperiences].[Profileid_IX], include the columns StartYear and EndYear
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 27, 2011 at 4:29 pm
Thanks Gail 🙂
ps I'm not new to SQL, i have not coded in 20+ years.....old COBOL, CICS, IMS guy.
September 27, 2011 at 5:35 pm
Rather than a global temp table (e.g. ##temptable), use a single # for your temp tables.
Also, you might want to figure out a method to rank your results. You can still use a CTE if you employ some ranking and limit results in that fashion.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2011 at 5:36 pm
GilaMonster (9/27/2011)
From permanent tables to global temporary tables. That's going to give you even more fun race-conditions and incorrect results if multiple of those queries are run concurrently.
Dang - Gail beat me to it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2011 at 5:41 pm
hmmm...Could you possibly provide DDL and some sample data?
It would be handy in trying to recreate and provide a better solution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2011 at 6:16 pm
What do you suggest for phone numbers?
Most of what i have found has been char, varchar or nvarchar.
Thanks!
September 27, 2011 at 6:18 pm
SQLRNNR (9/27/2011)
hmmm...Could you possibly provide DDL and some sample data?It would be handy in trying to recreate and provide a better solution.
The problem only occurs with large volumes of data, 4 Million recs and 155 GB DB are a bit too big for the attachments 🙂
Would the DDL's help without the data in any way?
Thanks
September 27, 2011 at 8:40 pm
isuckatsql (9/27/2011)
SQLRNNR (9/27/2011)
hmmm...Could you possibly provide DDL and some sample data?It would be handy in trying to recreate and provide a better solution.
The problem only occurs with large volumes of data, 4 Million recs and 155 GB DB are a bit too big for the attachments 🙂
Would the DDL's help without the data in any way?
Thanks
Yes they would. Combine that with the exec plan and we have a much better idea.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2011 at 1:12 am
isuckatsql (9/27/2011)
What do you suggest for phone numbers?Most of what i have found has been char, varchar or nvarchar.
char or varchar. nvarchar is Unicode, so 2 bytes per character in order to store all sorts of other characters (inc the alphabets that I mentioned). Since phone numbers are typically 0-9, + and -, that's a waste of space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2011 at 5:27 am
When i try to run my app with the updated SP that uses the temp table, i get the following error:
"The return types for the following SP count not be detected, set the return type"
It seems that this is a common error on using a temp table within an sp, what is the workaround?
Thanks
October 3, 2011 at 11:48 am
Is there any way to make this query faster?
It seems that each search term i add, makes the query run 6 seconds slower. With five search terms, it takes approx 30 secs the first time, then 12 seconds each subsequent time. I assume that this is due to the temp index already being built?
I have tried moving the tempDB files to various RAID1's and even a twelve disk RAID10 and nothing seems to make the first search much faster!
I even tried using the thesaurus option, but this did not help the performance.
Thanks
USE [JobPortalIAN]
GO
/****** Object: StoredProcedure [dbo].[GetAllResumesSearchedDynamicQuery2FTI] Script Date: 10/03/2011 13:36:57 ******/
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,
@Industryid int,
@Degree int,
@Skillsid int,
@FutureSkillsid int,
@ExpectedSalary int,
@ExpectedMaxSalary int,
@Position varchar(250),
@Phrase int,
@fromRec int,
@toRec int
AS
--DROP TABLE #Ian_Temp
create TABLE #Ian_Temp
(
RowId int IDENTITY(1,1),
id uniqueidentifier,
regionid int,
cityid int,
countryid int,
IndustryID int,
AcademicExperienceTypeID int,
Title nvarchar(100),
InputDate datetime,
SalaryMinID int,
SalaryMaxID int,
Homephone varchar(12),
CellPhone varchar(12),
WorkPhone varchar(12),
PrimaryEmailAddress nvarchar(100),
SecondaryEmailAddress nvarchar(100),
UserName nvarchar(100),
RowNumber int
)
declare @Query as varchar(8000)
set @Query = '
select p.id as Id,p.regionid,p.cityid,p.countryid,
p.IndustryID,
p.AcademicExperienceTypeID,
case when len(w.position) < 30 then w.position
else
substring(w.position,1,30) + ''...'' end
as Position,
p.InputDate,
p.SalaryMinID,
p.SalaryMaxID,
p.HomePhone,p.CellPhone,p.WorkPhone,p.PrimaryEmailAddress,p.SecondaryEmailAddress,
isnull((select [Name] + '' '' from salutationtypes st
where p.SalutationId=st.id),'''') + isnull(p.FirstName,'''') + '' '' + isnull(p.MiddleName + '' '','''') + isnull(p.LastName,'''') as UserName,
ROW_NUMBER() OVER (ORDER BY p.id DESC) AS RowNumber
from profiles p inner join workexperiences w on w.ProfileId = p.id
inner join documents dc on p.id = dc.profileid
where p.allowrecruiters=1 and dc.documentstatusid=1'
--and p.InputDate >=''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
if (@Date <> 0)
Begin
set @Query = @Query + ' and p.InputDate >=''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
End
if (@Name <> '')
Begin
set @Query = @Query + ' and contains(dc.doccontent,''' + @Name1 + ''') '
--set @Query = @Query + ' and contains(dc.doccontent,''FORMSOF(Thesaurus, java)'') '
End
if (@Position <> '')
Begin
if (@Phrase = 1)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 2)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 3)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 4)
Begin
set @Query = @Query + ' and w.position = ''' + cast(@Position as varchar(250)) + ''''
end
End
if (@Stateid <>0)
Begin
set @Query = @Query + ' and p.regionid =' + cast(@Stateid as varchar(10))
End
if (@CityId <>0)
Begin
set @Query = @Query + ' and p.cityid =' + cast(@CityId as varchar(10))
End
if (@Industryid <>0)
Begin
set @Query = @Query + ' and p.IndustryId =' + cast(@Industryid as varchar(10))
End
if (@Degree <> 0)
Begin
set @Query = @Query + ' and p.AcademicExperienceTypeID =' + cast(@Degree as varchar(10))
End
if (@Date <> 0)
Begin
set @Query = @Query + ' and InputDate >=''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
End
if (@ExpectedSalary <> 0)
Begin
set @Query = @Query + ' and p.SalaryMinID >= ' + cast(@ExpectedSalary as varchar(10))
End
if (@ExpectedMaxSalary <> 0)
Begin
set @Query = @Query + ' and p.SalaryMaxID <= ' + cast(@ExpectedMaxSalary as varchar(10))
End
Insert into #Ian_Temp
exec (@Query);
CREATE CLUSTERED INDEX IDX_C_Users_UserID ON #Ian_Temp(Id)
select
#Ian_Temp.id,
c.Name as City,
r.abbreviatedName as State,
sum(endyear - startyear) as Experience,
i.name as Industry,
ae.Name as Degree,
smin.value as Salary,
smax.value as MaxSalary,
cast((c.Name + '' + ', ' + r.abbreviatedName) as nvarchar(100)) as Location,
Title,
Position,
InputDate,
substring(cast(InputDate as varchar(20)),1,12) as newdate,
Homephone,
CellPhone,
WorkPhone,
PrimaryEmailAddress,
SecondaryEmailAddress,
UserName,
RowNumber,
(select max(RowId) from #Ian_Temp) as TotalCount from #Ian_Temp
inner join Cities c on c.id = #Ian_Temp.cityid
inner join Regions r on r.id = #Ian_Temp.regionid
inner join WorkExperiences we on we.ProfileId = #Ian_Temp.id
inner join Industries i on i.id = #Ian_Temp.IndustryID
inner join AcademicExperienceTypes ae on ae.Id = #Ian_Temp.AcademicExperienceTypeID
inner join Salaries smin on smin.Id = #Ian_Temp.SalaryMinID
inner join Salaries smax on smax.Id = #Ian_Temp.SalaryMaxID
--Where RowId between 0 and 5000
--where RowNumber between 0 and 5000
WHERE RowNumber BETWEEN @fromRec AND @toRec
group by RowNumber, #Ian_Temp.id, c.Name, r.abbreviatedName, Position, i.Name, ae.Name, smin.value, smax.value, Title, InputDate, Homephone,CellPhone,WorkPhone,PrimaryEmailAddress,SecondaryEmailAddress,UserName
--order by InputDate DESC
order by RowNumber asc
--DROP TABLE #Ian_Temp
--exec (@Query)
October 3, 2011 at 12:32 pm
What field type for the FTI field, would provide the best performance?
Thanks
Viewing 14 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply