August 26, 2010 at 2:59 am
Hi everyone,
My current problem lies with coming up with a good way to solve a search and match problem.
I have Job Positions available and also Applicants who can fit into the different job positions. Each job Position have requirements like Company, Job Industry requirements, Job Skills requirements, Job Education Qualification requirements, and Number Required. Job Industry, Job Skill, Job Education Qualification requirements are all foreign key values from their respective parent tables.
Applicant table contains the Id and Date of Birth, and other personnel information on the Applicants. The Applicant's Job Industry, Job Skills, and Education Qualification information are stored on separate association tables like Applicant_Industry, Applicant_Skill, Applicant_Education. All the Applicant and Applicant related tables are Views as the information are from 2 different databases.
The Companies listed in the Job Positions are arranged into 3 priority groups. The Companies in the priority group can be changed depending on the user's settings. Therefore, the Companies must be assigned based on how high the priority level. Meaning the companies in priority group 1 will be searched and matched first and the companies in priority group 3 will be the last to be searched and matched. In each PriorityGroup, the companies with the group also have its own order listing starting from 1 onwards.
The search and match algorithm has to go in the order of the Job Position's Job Industry requirement will be searched against the Applicant's Applicant_Industry information, if there are matches, the Applicant's ID and Job Position Id will be inserted in the JobMatchApplicant table. If there are still number of people required after the matching, the Job Position's Job Skill requirements are searched and matched against the Applicant's Applicant_Skill table. The Job Position's Education Qualification requirement are matched last.
Applicant's that are already matched and inserted into the MatchApplicant table should not be selected again. Each Applicant may have more than one Job Industry, Skill, and/or Education Qualification information.
Currently, the idea I have is to have a loop to loop through the Priority Group to acquire the companies in each group, and in each loop, go through the search and matching of the job position. While matching the job positions, I will loop through the job positions of the companies to search and match against the applicant information. However, this way is way too slow.
I am wondering if there are any other strategies or ways to overcome this problem in a faster and scalable manner. The Applicant table have around 2 million records.
I have attached a sample table schema to this post.
Please kindly advise. Thanks a lot. 🙂
August 26, 2010 at 3:18 am
I've thought about the possibility of using CTE and tally tables. Not sure if those would be applicable. Also, I'm definitely looking into removing as much loop as possible as current, the number of loops id 3 loops per priority group. I think this is killing the performance.
Thanks.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply