February 14, 2012 at 2:22 pm
I have the following SP and it seems that my Actual Execution Plan(attached) wants a lot of different indexes, depending on my input data.
How many indexes is too many?
Is it due to a poorly written query?
Thanks
USE [JobPortalIAN]
GO
/****** Object: StoredProcedure [dbo].[GetAllResumesSearchedDynamicQuery2FTI] Script Date: 02/14/2012 16:09: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,
@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
--DROP TABLE #Ian_Temp
create TABLE #Ian_Temp
(
RowId int IDENTITY(1,1),
id uniqueidentifier,
regionid int,
cityid int,
countryid int,
IndustryID1 int,
IndustryID2 int,
IndustryID3 int,
IndustryID4 int,
IndustryID5 int,
Industry1 nvarchar(50),
Industry2 nvarchar(50),
Industry3 nvarchar(50),
Industry4 nvarchar(50),
Industry5 nvarchar(50),
Desiredempid1 int,
Desiredempid2 int,
Desiredempid3 int,
Desiredempid4 int,
Desiredempid5 int,
Jobtype1 nvarchar(50),
Jobtype2 nvarchar(50),
Jobtype3 nvarchar(50),
Jobtype4 nvarchar(50),
Jobtype5 nvarchar(50),
TotalYears int,
Militaryid int,
Securityid int,
militaryname nvarchar(50),
securityname nvarchar(50),
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.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.AcademicExperienceTypeID,
--case when len(w.position) < 30 then w.position
--else
--substring(w.position,1,30) + ''...'' end
--as Title,
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 alldocuments dc on p.id = dc.uniqueid
where p.allowrecruiters=1'
--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,''' + 'java' + ''') '
--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 (@Experience <>0)
Begin
set @Query = @Query + ' and p.TotalYears =' + cast(@Experience as varchar(10))
End
if (@Militaryid <> 0)
Begin
set @Query = @Query + ' and p.Militaryid =' + cast(@Militaryid as varchar(10))
End
if (@Securityid <> 0)
Begin
set @Query = @Query + ' and p.Securityid =' + cast(@Securityid as varchar(10))
End
if (@Industry1id <> 0)
Begin
set @Query = @Query + ' and p.Industry1id =' + cast(@Industry1id as varchar(10))
End
if (@Industry2id <> 0)
Begin
set @Query = @Query + ' and p.Industry2id =' + cast(@Industry2id as varchar(10))
End
if (@Industry3id <> 0)
Begin
set @Query = @Query + ' and p.Industry3id =' + cast(@Industry3id as varchar(10))
End
if (@Industry4id <> 0)
Begin
set @Query = @Query + ' and p.Industry4id =' + cast(@Industry4id as varchar(10))
End
if (@Industry5id <> 0)
Begin
set @Query = @Query + ' and p.Industry5id =' + cast(@Industry5id as varchar(10))
End
if (@Jobtype1id <> 0)
Begin
set @Query = @Query + ' and p.Desiredempid1 =' + cast(@Jobtype1id as varchar(10))
End
if (@Jobtype2id <> 0)
Begin
set @Query = @Query + ' and p.Desiredempid2 =' + cast(@Jobtype2id as varchar(10))
End
if (@Jobtype3id <> 0)
Begin
set @Query = @Query + ' and p.Desiredempid3 =' + cast(@Jobtype3id as varchar(10))
End
if (@Jobtype4id <> 0)
Begin
set @Query = @Query + ' and p.Desiredempid4 =' + cast(@Jobtype4id as varchar(10))
End
if (@Jobtype5id <> 0)
Begin
set @Query = @Query + ' and p.Desiredempid5 =' + cast(@Jobtype5id 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,
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,
IndustryID1,
IndustryID2,
IndustryID3,
IndustryID4,
IndustryID5,
Industry1,
Industry2,
Industry3,
Industry4,
Industry5,
Desiredempid1,
Desiredempid2,
Desiredempid3,
Desiredempid4,
Desiredempid5,
Jobtype1,
Jobtype2,
Jobtype3,
Jobtype4,
Jobtype5,
TotalYears,
Militaryid,
Securityid,
militaryname,
securityname,
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 i1 on i1.id = #Ian_Temp.IndustryID1
inner join Industries i2 on i2.id = #Ian_Temp.IndustryID2
inner join Industries i3 on i3.id = #Ian_Temp.IndustryID3
inner join Industries i4 on i4.id = #Ian_Temp.IndustryID4
inner join Industries i5 on i5.id = #Ian_Temp.IndustryID5
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,
InputDate,
Desiredempid1,
Desiredempid2,
Desiredempid3,
Desiredempid4,
Desiredempid5,
Jobtype1,
Jobtype2,
Jobtype3,
Jobtype4,
Jobtype5,
Militaryid,
Securityid, militaryname, securityname, IndustryID1, IndustryID2, IndustryID3, IndustryID4, IndustryID5, Industry1, Industry2, Industry3, Industry4, Industry5, TotalYears, ae.Name, smin.value, smax.value, Homephone,CellPhone,WorkPhone,PrimaryEmailAddress,SecondaryEmailAddress,UserName
--group by RowNumber, #Ian_Temp.id, c.Name, r.abbreviatedName, ae.Name, smin.value, smax.value, Homephone,CellPhone,WorkPhone,PrimaryEmailAddress,SecondaryEmailAddress,UserName
--order by InputDate DESC
order by RowNumber asc
--DROP TABLE #Ian_Temp
exec (@Query)
February 14, 2012 at 4:48 pm
Never use dynamic sql like that. Use sp_executesql and correctly parameterize all variable input. Right now, the way you have it, you have an open invitation to perform sql injection and you have zero plan re-use unless the exact same parameter values are given.
Index flexibility is great, but it comes at the cost of disk space and a small amount of overhead when inserting/updating/deleting records. If those issues aren't problems for you, then you want as many indexes as you need to support the queries run on the system.
February 15, 2012 at 6:22 am
Thanks for your advice!
I'm still learning all this dynamic SQL stuff 🙂
February 15, 2012 at 6:57 am
Try change you query to not use dynamic SQL.
You can, for example, use
where (@param is null or table.column = @param)
to avoid dynamic setup of the where clause.
But first lear the basics and the basics is about indexes and there a lot to learn about it.
A index is "too many" if it is useless or is not improving performance.
How do you ill find if its usefull or improving performance?
Well, learn a bit more about it. Sometimes its tricky and needs deep investigation.
I suggest reading the basics in in BOL and make some search about the subject here in SSC, in SSC authors blogs and google.
February 15, 2012 at 9:02 am
Thanks for your feedback!
I thought that dynamic SQL was faster than 'normal' SQL, which was the reason for using it.
It seems to have made my queries faster!
February 15, 2012 at 9:50 am
Like bteraberry said it's not a good chose to use dynamic SQL like that.
Instead you can use parametized dynamic SQL or static SQL.
A non parametized dynamic SQL ill be a complet different query for each run from the DB engine view point.
It ill not take advantage of statics, for example, to help the optimizer to chose a better execution plan.
It's also faster to code SQL like that only if you are not used to do another way.
But it lets you SP prone to SQL inject for example.
There are dozen articles here in SSC about dynamic SQL, they can help you to do more reliable and faster code.
February 15, 2012 at 11:04 am
isuckatsql (2/15/2012)
Thanks for your feedback!I thought that dynamic SQL was faster than 'normal' SQL, which was the reason for using it.
It seems to have made my queries faster!
This is a pretty valid concern, having a big stack of those parametized query criteria clauses can apparently produce less than ideal results performance-wise, and you probably did get better results with dynamic sql, while probably introducing some security concerns.
I like Erland Sommarskog's analyses this for some different server versions http://www.sommarskog.se/dyn-search.html, and his text on dynamic sql itself http://www.sommarskog.se/dynamic_sql.html is a good read in my opinion about some major concerns with dynamic sql that you should be aware of.
February 15, 2012 at 11:18 am
I would actually structure the application to call separate stored procedures based on the parameters provided. This way you don't have this messy code; i.e. GetResumesByName, GetResumesByNameAndNumber, GetResumesByCity.
More code to manage, but much EASIER to manage.
Jared
CE - Microsoft
February 15, 2012 at 12:36 pm
isuckatsql (2/15/2012)
Thanks for your feedback!I thought that dynamic SQL was faster than 'normal' SQL, which was the reason for using it.
It seems to have made my queries faster!
It can appear so because dynamic SQL not executed by sp_ExecuteSQL will usually force a recompile.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2012 at 2:39 pm
Thanks for the advice 🙂
February 15, 2012 at 3:10 pm
Using Dynamic SQL is an excellent choice for removing the pain point from catchall search queries like this one.
For one of the most effective discussions on it, review this link from Gail Shaw's blog:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
That said, if you don't use parameters you've left yourself open to injection. Definately review that article.
I disagree that, in a controlled structure, Dynamic SQL is evil. The reason it wants different indexes is because they're different queries, as mentioned. Each one will compile on its own, and have its own plan. If you follow Gail's instructions, each *pattern* will compile, and will save you time and cache in the long run.
Now, how many indexes are too many? When you're overlapping uselessly, space is an issue, or your I/U/D times are no longer considered tenable. Indexes help reads but hurt writes. You have to decide where you want to take the compromise. There's usually a 'sweet spot', but it's different for every table.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 15, 2012 at 3:12 pm
SQLKnowItAll (2/15/2012)
I would actually structure the application to call separate stored procedures based on the parameters provided. This way you don't have this messy code; i.e. GetResumesByName, GetResumesByNameAndNumber, GetResumesByCity.More code to manage, but much EASIER to manage.
With the number of possible iterations here (15P15?) that's a LOT of stored procedures to manage. I think I'd take the dynamic route with parameters and sp_executeSQL.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 15, 2012 at 3:18 pm
Evil Kraig F (2/15/2012)
SQLKnowItAll (2/15/2012)
I would actually structure the application to call separate stored procedures based on the parameters provided. This way you don't have this messy code; i.e. GetResumesByName, GetResumesByNameAndNumber, GetResumesByCity.More code to manage, but much EASIER to manage.
With the number of possible iterations here (15P15?) that's a LOT of stored procedures to manage. I think I'd take the dynamic route with parameters and sp_executeSQL.
Knowing what I know now after reading Gail's article, I agree 🙂
Jared
CE - Microsoft
February 16, 2012 at 11:10 am
SQLKnowItAll (2/15/2012)
Evil Kraig F (2/15/2012)
SQLKnowItAll (2/15/2012)
I would actually structure the application to call separate stored procedures based on the parameters provided. This way you don't have this messy code; i.e. GetResumesByName, GetResumesByNameAndNumber, GetResumesByCity.More code to manage, but much EASIER to manage.
With the number of possible iterations here (15P15?) that's a LOT of stored procedures to manage. I think I'd take the dynamic route with parameters and sp_executeSQL.
Knowing what I know now after reading Gail's article, I agree 🙂
That was also my concern, but i appreciate the suggestion!
I will review Gail's article and see if i can use her ideas to get my query performance to a decent level.
Thanks for all the good advice!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply