May 7th, 2011

2009

Scalable Full Text search with location

I have a question for backend developers and admins.

Say you search for resumes on PostJobFree
If you search for some common term, such as software developer - the results are coming back pretty fast.
However if you search for something obscure - such search query may take several seconds (or more) to execute.

Currently such search is handled by Microsoft SQL Server Full-Text (see example of such query below).
If query can quickly find matching resumes in recent records - such query is fast.
If query has to go through all 150K+ resume records in order to find matches - it takes noticeably longer.

My question to database admins:
How do I make that query work faster?
Should I use separate server with plenty of memory (8 GB+) and build Full-Text Catalog for Resume table there?

My question to developers:
Are there other ways to do fast full-text search with location?
May be some search servers or search components?

Here's the example of SQL queries behind resume search query:
declare @upTo int;
set @upTo = @startRowIndex * @pageSize;

with p as (
select top (@upTo)
row_number() over (order by PostedDate desc) as Row,
JobId,PostedDate,JobTitle,JobDescription,CompanyName,Country,State,City,Latitude,Longitude,EmailAlias,UrlDescription
from JobPost with (nolock)
where CONTAINS((JobDescription, JobTitle, CompanyName, City, PostalCode),'SOFTWARE&DEVELOPER')

and (Latitude between 40.352045 and 41.076660) and (Longitude between -74.482126 and -73.529820))
select JobId,PostedDate,JobTitle,JobDescription,CompanyName,Country,State,City,Latitude,Longitude,EmailAlias,UrlDescription
from p where Row between @startRowIndex and @startRowIndex+@pageSize-1

option (force order);

select @totalCount = count(1) from JobPost with (nolock)

where CONTAINS((JobDescription, JobTitle, CompanyName, City, PostalCode),'SOFTWARE&DEVELOPER')

and (Latitude between 40.352045 and 41.076660) and (Longitude between -74.482126 and -73.529820)
option (force order);


Upadate:
Found interesting alternatives to SQL Server full-text search (FTS): Lucene/SOLR and Sphinx.
http://beerpla.net/2009/09/03/comparison-between-solr-and-sphinx-search-servers-solr-vs-sphinx-fight/