Replies to post #51269 on The Question and Answer Board (IHUB)

[Bob Zumbrunnen]

Update: On second thought, we might've reached some functional threshhold for SQL's CONTAINS() function. It's being reported as 98% of the cost.

My hypothesis at this point is that either MSSearch is unable to allocate the amount of resources it really needs to do its thing quickly, or we've been reaching a point of exponential increase in the relationship between dataset size and search times. That a 1% increase in dataset size may have been resulting in a 10% increase in execution cost, then a 20% increase in cost for the next 1% dataset growth, etc.

I'm not going to try to fix the current methodology because the "breakage" seems to be in MSSearch, which I can't do anything about directly.

I'm going to go ahead and redo the backend part of it so it works more like SI. iHub searches the actual message table. SI has the message table copied (with only the necessary fields) into what I call "search tables", and those are the tables that actually get searched.

Just copying public messages to one table and private messages to another table will help a lot, since nearly a quarter of the 5.7MM messages are private. And part of the query has been to use a text field (very expensive) to limit the query to either public or private. The change I'll make will allow the system to just search the relevant table and not have a separate expensive where clause making sure only public or private messages are searched.

So, bottom line is Advanced Search is very, very broken right now. And the system is going to slow down a bit because the work I'm going to do is so time-consuming I can't do it at night.

I'll shoot for having Advanced Search working for Public messages again sometime next week. Private messages the week after.