The Question and Answer Board

[Bob Zumbrunnen]

The bottleneck in it is that it uses MS-Search as the engine. It'll automagically make a catalog separate from SQL Server to use for the searches.

Actually, it's not so much that it uses MS-Search; it's that it uses MS-Search *first*, then applies other criteria you specified.

So, if you do some limitation like "select * from message where contains(message_text, "INTC") and message_date > getdate()-365"...

it'll first go to the catalog and select all messages containing "INTC", *then* it'll apply the message_date filter.

There's no way to force it to limit the contains() search to messages meeting your other criteria. It'll always run the contains() first (perhaps returning hundreds of thousands of records), then the other criteria get applied before the results are returned.

The only way I know of to get around this is to roll our own.

iHub's size is starting to reach the point at which searches are slowing down enough to be a bit of an annoyance to me. Using this method on SI is definitely in the "too slow" category. If I remember right, doing it on the Dev server resulted in elapsed times approaching a minute.

I finally had it proven to me that SQL's full-text search works this way when I had the bright idea of searching in batches of 10k messages at a time until I'd accumulated 50 hits to display. No good.

I understand the basics of how it's working on SI and should be able to recreate it on the new version eventually. The only thing lacking in SI's version is that it doesn't go back very far and since it involves a separate machine running a different OS and programming language to build it, it increases the failure opportunities.

But one thing about it is that it's FAST.