By BoLOBOOLNE payday loans

Improving SQL Full Text Searching… again

Posted: March 2nd, 2010 | Filed under: Database, Full-Text, General, Search
Author:
No Comments →

I wrote recently about some of my improvements to my SQL search queries. Well no sooner did I wrap those up than I needed to revisit things again.

To start let’s go over a little of what I’m already doing.

Grammar
I used this code along with the Irony library to get myself started with some search grammar. I had to make some modifications for the specifics of the type of data we search and what we need, but this was a solid starting point. Using this I moved to the ContainsTable fulltext predicate which is faster and allows me to tell the engine what I’m searching for more specifically. FreeTextTable is more “automagic” but didn’t allow me the flexibility I needed.

Removing Noise
The type of searching I need the default noise word dictionary turned out to be a massive hinderance. You can find your own full text dictionary in your SQL folder in Program Files. I’ll post the path if I can remember where it is. Anyway, for us single letters aren’t really noise words. We have Account names like “R & L Stine & Co.” I was ending up where a user might search for “R & L Stine” and the search engine automatically cleaned up all that noise so my real search was “Stine”. Which of course would return a whole host of results that aren’t intended and we were in a mess.

For my situation, I emptied the entire noise word dictionary. It simply didn’t apply.

By the way: the noise word removal does impact phrases (e.g. a quoted or exact search). I thought that the use of “” around my search terms would tell it to ignore the removal of noise words. Wrong.
Read the rest of this entry »

Tags: , , , , , ,

A Faster Full-Text Search on SQL Server 2005

Posted: February 2nd, 2010 | Filed under: Database, Full-Text, Search
Author:
2 Comments →

Full-Text Search

When I dove into the SQL Server 2005 Full-text search, I was pretty stoked. At the time we were having a heck of a time with SharePoint full text searching (it was literally take 2-3 days to do a single crawl). We needed a faster more able solution. Tada! SQL Server to the rescue! Full-text capability comes with it. Why did we need that Sharepoint search anyway, in fact, we figured SharePoint was probably using the same service only with a ton of other overhead to gather data into it’s own structure, etc.

The dataset I was working with wasn’t really that big. ALL the data was probably less than 2mil rows. We had tables and tables of related data, some of which we wanted to search. I’d never worked with full-text searching or indexing before so it was a learning experience. I went out and read whatever I could because while the crawl and populates were substantially faster, the queries were still slow, sometimes slower than the Sharepoint counter part. So I’ve been optimizing queries and my full-text properties to get better and better results. There’s a lot of information out there, and a lot of it is useful.

Then I stumbled across a relatively small change that gave me the biggest performance gain yet.

Read the rest of this entry »

Tags: , , , , , ,