The website forum search function has been significantly improved.  It should no longer timeout regularly, and the results should be significantly more relevant.

Let me know if you see any sort of wonkiness.

Quoting Dr. Killpatient:

Quote:
Created indexes?
That's my guess as the forum would become unresponsive as a whole if someone performed a search.

Ahh if only it were that simple.   The proper indices were in place long ago, and MS' full text search engine as well.  I had banged my head on this a while ago and had run into an interesting symptom - the query ran fine via query analyzer, but routinely timed out when run via the web page.

When I decided to tackle this again the other day, I rewrote the search to use a stored procedure rather than generated sql, and along the way improved the query quite a bit. (I've learned a bit since I originally wrote it. )

When I was done, I had it very performant compared to the prior version.  Yet in the end I discovered that the query ran fine (many searches are sub-second) when run directly in the query analyzer, but again consistently timed out when running the stored procedure. I had been blaming asp.net for the problem, but now could remove it from the investigation.

This led me to the culprit - something sql server does called parameter sniffing.  Certain cases confuse the hell out of the optimization engine.  The solution in this case was simply to redefine the passed parameters as local variables, and just set them to the passed values.  This apparently causes a reset of the optimization and in this case solved the problem.

Also, thanks for the notion on the greenies, but as you can see at the left, I'm green enough already!