Hello SQL wizards,

I'm trying to match saved searches to a newly inserted "job", and send
an email for matching searches. get about 20 job postings a day, and
have about 150k saved searches. want to do this as quickly as possible.
Please advise...

here's what I need to do:

1. job seekers create saved searches, with criteria such as location
and some keywords
2. job is posted by a employer and inserted in job table
3. find all saved searches that match the newly inserted job
4. send emails to job seekers with matching searches

right now, i'm doing the following:

1. using insert trigger on job table
2. put matching searches into a cursor (except by keyword search as I
can't figure out how to match by keyword using full text index all in
one statement)

select savedSearchId,...from savedSearches where (location='' OR
location=@JobLocation) AND (duration='' or duration=@jobDuration)...

3. loop throught cursor, doing

if(savedSearch has keywords)
select count(*) from jobtable where jobid=@newlyInsertedJobId and
CONTAINS(*, keywords)

4. send email if matches keywords

this takes a while. there are about 150k saved searches. filtering on
non keywords returns about 3000 records to the cursor. the CONTAINS
search takes a long time.

Questions:

1. possible to do an asynchronous insert using ADO.net 1.1?
2. should i find the matching saved searches, put them in a table, and
do the keyword search/email later? if so, how?
3. how would you do it differently?
4. how to send email? xpsendmail or external component?

Thanks in advance!

Neil

found some problems myself:

1. full text index doesn't contain the new posting as it was just
inserted. should i do an incremental catalog population on insert?

2. contains() returns all rows that match the keywords, and THEN it's
filtered by jobid, so that's why it's slow...

any advice would be greatly appreciated.

For instant propagation of changes to the FTI, you should use change tracking
and background propagation.

Look up sp_fulltext_table in Books Online.


ML

Yes, both are true for SQL Server 2000....
For #1 you should enabled "Change Tracking" with "Update Index in
Background". The first initial setting of the CT with UIiB will
automatically run either a Full or Incremental population depending upon a
timestamp column in the table and if the FT Catalog is already populated.

For #2 you may want to use more sophisticated filtering with pre- and post-
processing as I once worked with a client in Europe who had a similar
requirement, except they were using FTS with a custom new clipping service
where the newspaper publishers were the employers and the newspaper reader
was the job seeker. If you're interested, I may be able to put you in touch
with them. Feel free to email me directly if you want.

Thanks,
John
Show quoteHide quote

(neilmcgui***@gmail.com) writes:
No and yes. There is no such thing as an asynchrounous insert, but
in your INSERT trigger just write a row to an alert table, and have
a job to run from SQL Agent (or scheduled by your own app) once a minute
or how often you see fit, to check for new entries.

how do I prevent duplicate ID in my situation
Date issue
DB Design issue
Help selecting maximum number of characters
how to process each record of select results