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.
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.
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 »