Help needed!

Question

Help needed!

devilzadvocate07

Newbie

Points: 1
More actions
October 2, 2008 at 10:37 pm

#103595

Am working on a .Net web application which uses SQL server 2008 as database.
Its a "Job search" application through which end users can search for a job in my database using comma separated search keywords.My database could be having tens of thousands of jobs.Each job would have a title and a description.
I need to return all the matching jobs to the user based on his search criteria and also give an appropriate "rating" for each job with respect to the percentage of matched keywords found.Users would have a feature of sorting the returned jobs results on "Rating","Date posted", etc.
I guess, using a sql cursor in the stored procedure to loop through each matched job and then calculating & assigning rating to each job would be pretty resource intensive..more so, if the number of records are too high.
So, any ideas on how to go about efficiently designing this kind of application?...how do i optimally search and fetch all the relevant records alongwith "rating" from the database without having a significant performance hit??
Any help would be greatly appreciated!:)
Thanks.
Simon.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Have you considered using the full-text search? It's one of the easier ways to do rating of matches.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

john_jakob Mr or Mrs. 500 Points: 587 More actions · Answer 2

If you don't wanna use full text (it's a bit tedious to set up and configure), and your description field in the table is less than 900 bytes, you could write a stored proc that takes an input string of delimited jobs -- e.g., "DBA, Engineer, Developer, Project Manager", parses it out into a series of tokens, and then builds a dynamic SQL query to return the results with a series of OR-ed "LIKE" statements -- i.e.,

SELECT * FROM Job WITH (NOLOCK)

WHERE JobDescription LIKE '%DBA%'

OR JobDescription LIKE '%Engineer%'

OR JobDescription LIKE '%Developer%'

OR JobDescription LIKE '%Project Manager%';

You get the idea.

To get the ratings, you could throw the results of the query into a temp table, and add an additional column "Rating INT NOT NULL" field to the temp table.

Then do something like this:

-- Initialize

UPDATE #Temp SET Rating = 0;

-- Do this once for each Job Token -- In this case 4 times, to Calculate the Rating

UPDATE #Temp SET Rating = Rating + 1 WHERE JobDescription LIKE '%DBA%';

UPDATE #Temp SET Rating = Rating + 1 WHERE JobDescription LIKE '%Engineer%';

UPDATE #Temp SET Rating = Rating + 1 WHERE JobDescription LIKE '%Developer%';

UPDATE #Temp SET Rating = Rating + 1 WHERE JobDescription LIKE '%Project Manager%';

-- to Return Results:

SELECT Rating, * FROM #Temp;

-john