First step – find piece of code in JForum that handles moving threads. It’s GenericForumDAO.moveTopics. I see this method was modified by us (JavaRanch) to support leaving a copy of the thread in the original forum or creating a new copy and locking the original (for use in moving to our private “Trash Can” forum.) We made that change a long time ago though and it wasn’t slow then. Looking at the method, I see a number of database queries. I’m adding logging (nothing fancy – just System.out’s) to see which query is using up so much time. The method I added logging to takes 5-8 milliseconds. (across two trials). Yet the browser plugs away for almost a full minute after that. Ah! The web/action layer class ModerationHelper makes a bunch of calls to clear/reload the cache. I bet that’s where all the time is going. Adding logging on that level and redeploying. Now we are getting somewhere. Everything goes very quickly until the part where the hotest topics get reloaded and then that takes almost a minute. Yuck. Table scans left and right. No wonder it takes so long. Three tables scans, joining on the result and THEN discarding almost all the records. (see “original query” and “original explain” in supplemental materials section below) If I was doing this by brute force, I would add an index on topic_views and refactor the query to search on the the topics table only joining with the others on result. This is similar to the last post where I walked through an optimization. However, I’m not sure that makes sense here. This definition of hottest posts uses the most page views forever. This is ok for now because all the migrated posts started with zero views against them. Making the hottest threads really “the hottest threads since our Saturday January 3rd migration.” What happens down the road. When a thread stops being viewed altogether, it’s not hot anymore. I’m thinking it would be better to put a time based constraint on things. I’ll post in our Moderators Only forum to get some feedback on my proposed definition change and go have dinner. Unanimous agreement on my proposed definition from everyone who saw the question while I was eating. Including the site owner. Good news. Modify the query to add a where clause by thread last modified date This modified query takes 80 thousand units instead of 253 thousand units. Better but still way too high. Next I’ll try adding index on topic_last_post_id, topic_views Running explain again gives the exact same output. It isn’t using the index. Dropping the index since it didn’t work. I may re-add it later but I like to test only one variable at a time. The database seems to be favoring the table scan of the forum table first. Probably because this table is order of magnitudes smaller than the other two in the join. Now I’ll try removing the forum table from the query and run an explain to see what happens. It’s still 78 thousand units, but at least it is using an index (post_time) as the first step. Time to try to add the index and run explain again. Nothing. Still 78 thousand units. All right. This tweaking thing isn’t getting me anywhere. New approach. How would I build this query if I were writing it from scratch. The base query that I want to happen first is explain select p.post_id from jforum_posts p WHERE p.post_time >= ‘2008-01-01’. Running an explain on just this says it takes 1 thousand cost units. That sounds high – I’ll query some details. Over 100 thousand posts in a few days? Oh yeah! It’s 2009 – a New Year. I should know that – I celebrated recently. Re-running the original query with the post_time clause gives me under two thousand cost units. Whew. That’s much better. Now I’m uploading the changed query to our sandbox environment to see how the move performs. Three to five seconds. Good. Another moderator suggested using replies and not views. This is a good idea, but has no effect on performance so I switched it. (see “Final query” in supplemental materials) And a good techie verifies everything so running explain one last time to confirm this last change didn’t affect performance. It didn’t. Performance is still ok. ( see “Final explain” in supplemental materials)