September 22, 2005 at 11:55 am
Hi
Firstly, thanks to all who answered my previous post on this (same) subject http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=219329#bm220011- the reason for this new thread is I need more help extending the same query (or re-defining the db, which I'm fortunately able to do).
To outline the situation: I have the following tables:
1) Candidates - stores candidate names etc (PK: CandidateID)
2) CandidateExperience - stores what experience a candidate has (PK: CandExperienceID, FK: CandidateID, FK: ExperienceID which links to a table holding a number of disciplines a candidate may have experience in)
3) CandidateQualifications - stores a list of what quals a candidate has (PK: CandQualsID, FK: CandidateID, FK:QualificationID - linking to a table holding possible qualifications)
Problem:
I need to be able to get a list of candidates who MUST have a,b,c qualifications, and it would be nice if they had optional d,e,f qualifications, and who MUST have g,h,k experience and might have x,y,z experience.
The initial solutions posted to my previous post (which only dealt with the qualifications bit) included a Split function (from Remi) which to be honest I didn't really understand (although I'm sure works a treat for that particular question - just don't really understand how it works!), and the creation of a 'Profiles' table to store details of the job, what experience is REQUIRED (marked by a 'required' column) and what experience is desired (marked by a lack of the 'REQUIRED' flag) which I quite liked (and understood). I've posted the stored proc for this method at the end of this post and it works a charm.
The problem now is how to deal with the Qualifications side of things - is it possible to do this in one query or not? Is this the best way to go (As I imagine going down this road would mean creating two 'profile' tables - one for experience and another for qualifications)?
As I say, I am able to add/edit the tables in this if required (lucky me!) but really need some advice/help on how to get the info out once it's in there!
Thanks in advance
Martin
----Code for stored proc using 'JobProfiles' table---
CREATE PROCEDURE [dbo].[CandExperienceSearch]
(@MyconstantforRequired [Int]=Null,
@MyProfileID [int]=Null)
AS
SET NOCOUNT ON
(
select MustHave.candidateid,NiceToHave.Extras
from
(
select candidateid
from dbo.CandidateExperience CandidateExperience
inner join dbo.JobProfiles Jobprofiles
/*matches all required qualifications*/
on CandidateExperience.ExperienceID = Jobprofiles.ExperienceID
and Jobprofiles.required = @MyconstantforRequired
and Jobprofiles.ClientJobID=@MyProfileId
group by candidateid
/*calculates the amount of necessary qualifications*/
/*count >= is having them all*/
having count(CandidateExperience.ExperienceID)=(select count(*) from dbo.JobProfiles where required=@MyconstantforRequired and ClientJobID=@MyProfileId)
) as MustHave
left join /*All of musthave, Nice to have if possible*/
(
SELECT candidateid,count(*) AS Extras /* sum(required) possible to sum required*/
from dbo.CandidateExperience CandidateExperience
inner join /*0 or more*/
dbo.JobProfiles Jobprofiles
on CandidateExperience.ExperienceID = Jobprofiles.ExperienceID
and Jobprofiles.required<>@MyconstantforRequired /* or >@MyconstantforRequired */
and Jobprofiles.ClientJobID=@MyProfileId
group by candidateid
) AS NiceToHave
on MustHave.candidateid=NiceToHave.candidateid
)
Return (@@Rowcount)
set nocount off
September 22, 2005 at 12:42 pm
Nothing's changed really. It's the exact same problem with the same solution.
Just redo what you did only with another set of tables. Now you got 2 different results set than aswer part of the question... The previous code will be the first part of the answer, make that a derived table and join (inner join) to the new code you'll create and that'll be it.
September 22, 2005 at 12:45 pm
BTW, see the split function as a flat loop. The break conditions are in the where clause and the output work is done in the select. The rank comes from counting number of separators between the beginning of the string untill where the data is extracted, hence faking an identity column.
September 22, 2005 at 12:52 pm
Hi
Thanks for the reply Remi - again, it's appreciated. So would you suggest having two profile tables, one for experience and one for qualifications? Just wasn't sure whether to do that or not as it effectively splits a single job profile across tables (as I said in my initial post, I'm still learning - both design and querying the data so please do bear with me).
Thanks in advance
Martin
September 22, 2005 at 1:02 pm
They'll serve the same purpose in the end but they are not the same thing, so they require different tables.
Also the users will surely request to check for only one of the 2 in the end so you'll be ready for that.
Now that I thik of that possibility, I'd move the selects into inline table functions. That way you can query either one of them if the user wishes, and you can join 'em in case of a more complexe search.
September 22, 2005 at 1:08 pm
Hi
Just a question (another!) - if I create the queries as inline table functions is that going to have a potential hit on performance as, from what I've read, UDF execution plans don't get cached as stored procs do. I've got no issues with using UDFs but just don't want to put myself in a position where performance becomes an issue because of using them.
Thanks again
Martin
September 22, 2005 at 1:15 pm
Hmm, they seem to be cached on my server (as adhoc objectTypes and executable plans). The real performance killer is when you use a function that selects from a table. This is where you lose all performance.
September 22, 2005 at 1:23 pm
Hi
Thanks for that - bit confused about the performance side though - when you say that performance will suffer when using a function that selects from a table, isn't that what these (two) suggested functions would be doing - i.e. selecting values from a table? Sorry if this sounds really stupid but I'm just a little confused.
TIA
Martin
September 22, 2005 at 1:28 pm
It depends on the number of calls to the function.. These would be called once each tops. When you use a function in another select >>
Select id, dbo.GetSomethingFromSomeTable(id) from dbo.Table
then that function is called once for each row in that table. It's that sheer number of calling that makes this slow.
September 22, 2005 at 1:30 pm
Ahh - now I understand, thanks for clarifying (and answering the original post).
Martin
September 22, 2005 at 1:36 pm
HTH.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply