June 8, 2005 at 9:38 pm
Hi,
I need to know whether it is possible to find out the number of time a particluar record has occured in searches.
Consider a field with column "LNAME" and i give a SELECT for giving the list of names which starts with 'R'.
If a record contains 'RAJ', then i want to find out how many times 'RAJ' has occurred in various searches??
June 8, 2005 at 9:45 pm
Unless you logged the searched, or the results, I don't think it can be done.
You could always use a log reader but then don't log selects so I doubt that would even be possible.
Are you trying to do a top X of most popular results or someting like that??
June 8, 2005 at 11:08 pm
Consider the example of a job site. You have uploaded ur resume. They keep a track of no. of times your RESUME has been found in various searches (not selected) done by various employers.
How are they maintaining it??? Any idea!!!!
June 9, 2005 at 12:45 am
Here is an pseudo-code example of how it can be done:
SELECT searchresults INTO #temptable
INSERT INTO searchstatistics FROM #temptable, with searchinfo
SELECT #temptable as output to client
June 9, 2005 at 12:49 am
However, do they actually record the number of times that your resume was found for a specific search, or is it just the number of times it was then actually selected and viewed? Because then they would simply do the search, and then when someone selects a specific resume they will update the stats for that resume with the search used to find it and return the full resume to the client.
June 10, 2005 at 12:17 am
The job sites actually record the number of times it was found in a SEARCH... Selection or Viewing can be done by maintaining the COUNT for the no. of times it has been selected or viewed (obviously, to view the RESUME, it has to be SELECTED first!!!). Will you suggestion for storing it in TEMPORARY table work here???
June 11, 2005 at 8:06 pm
If the volume of searches is small, you could update a hitcount in each record after it is found. But I would consider instead logging the data on which items were found in searches and summarizing that data periodically (perhaps daily). Then the number of hits for each resume wouldn't be up-to-date at any moment in time, but you wouldn't be slowing down searches to record the counts.
Since triggers are only on INSERT or UPDATE, you would have to run your search again, eg:
INSERT HITS
SELECT id FROM resumes
WHERE [same where clause]
That seems a bit inefficient. Maybe putting all fields into a temp table first would be better than selecting it from the resumes table twice.
Of course, your "hits" table could get quite large.
Whether this is really more efficient than just running an UPDATE after each SELECT, I'm not sure. In my experience, UPDATE tends to be rather expensive, and that would greatly increase contention and likely cause locking issues at even moderate loads.
June 14, 2005 at 3:48 pm
Something Like That ? The HITS table will contain only so many records as the number of original IDs in RESUMES
select ID INTO ##TempTable from resumes where <search conditions>
Update HITS SET Hitcount = Hitcount + 1 where ID IN (Select ID from ##Temptable)
<Process IDs from ##Temptable in the User interface of Search results Window, storing IDs in ##TempTable will allow browsing>
Regards,Yelena Varsha
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply