I know that the number of messages in that thread is huge (33,000+) but there's nothing I can do to change that as it's an existing, very popular forum.

By looking at that sproc we've identified that it's the 'message' column that's slowing everything down in the select. Without it in the sproc it runs in ~4 seconds, with it added it takes nearly a minute to run.

The Message column is an ntext column, and knowing what we know I was wondering if anyone has any tips or pointers towards how we can speed up this query?

Obviously the message column is critical to any given thread as it contains the content of the replies.

Again, upgrading to 1.9.4 isn't an option (no .Net 3.5 on the server) so we're working with 1.9.3 only.

Thanks in advance for any help.

UPDATE

Just noticed that in 1.9.4 (which I've downloaded to see if the sprocs changed, the post_list sproc specifically doesn't return the message, there's a comment saying " [Message] = '', -- no longer returns message"

Question is, where's the message data now coming from?

FURTHER UPDATE

Found this method in the 1.9.4 code:

// dynamic load messages that are needed...

YafServices.DBBroker.LoadMessageText(pagedData);

but am having trouble tracking down where YAFservices is defined....

Posts can be moved one by one until there'll be no logical problems.

Not sure I understand, I probably haven't explained properly.

This is a huge forum, migrated to YAF and running slowly. There are thousands of huge topics so closing it and reopening it won't work really.

Out of interest, how would that speed things up?

If you have a table with many millions of records it'll work slow anyway.

Even if you use smth. like IPB you close topics when they reach a post limit about 1000 as they become VERY slow and open a topic with the same name adding smth. like Vol.2 to its name.

I think I've sorted it out to a certain extent. I removed 'Message' from the sproc (as in 1.9.4) and I put the rendering of the acutal message into the displaypost control (added a method to get the message text from the message ID).

This has worked hugely in speeding things up and I'm hoping that it won't have any negative impact.