To kind of get the ball rolling, here's my current struggle. I think I've got a handle on it, but any ASP/SQL geeks out there are more than welcome to offer their opinion.
The site has been getting frequent timeouts. Specifically, web pages that don't load and instead give the "timeout expired" message. I haven't gotten enough cut and paste copies to know for certain, but I think the main culprit might be a particular SELECT statement in read_msg.asp. However, the trace that's currently running might be identifying boards.asp as the biggest culprit. Won't know for sure until I scrutinize the trace log tonight.
Both the SELECT statement I've seen bomb out via cut and paste, and the one that's being identified as a resource pig in Trace right now have at least one thing in common (that I've noticed so far): the SELECT statement specifically naming every field to retrieve (over a dozen) rather than using a "*". Oddly, when I tried changing it to "*" on my local setup, I got errors further down the script. Go figure.
I doubt the explicit naming of fields is really the problem anyway. It's not the angle I'm going to pursue first to try to tweak it.
I think the problem might instead be the WHERE clauses. I haven't checked yet (I will tonight), but it's entirely possible that the fields named in the WHERE clauses aren't indexed.
One of the fields can contain one of only two possible values and another can contain only one of 3. It's my understanding that you don't typically index such fields. Thoughts?
The field that contains 3 possibilities is used to identify what type of message is being looked at and to exclude private messages. I've got an idea on that one. It's looking for one value to exist in that field (which is a Text field, btw -- I'm thinking that could be a big problem) and I'm thinking that I should add a field (type Bit) to the table that's used to identify the message as either private or not.
I'll likely try that tonight as well as making it a Stored Proc to see if it can work a bit faster.
So, while I have my best friend (who's a major SQL Server geek) look at the server configuration, I'll try to tweak all queries so that none of them take more than 1k milliseconds to run. The first approach I'll use is trying to get them to do the fewest reads possible.
