dotProject Performance Tweak

Since my departure from the dotProject team last November, discussion of dotProject in this space has been minimal. There are a number of reasons for that, but the most important is that because CaseySoftware customers are steadily converting over to web2project. They've been happy with the improved performance and UI enhancements and are excited about the development roadmap. Of course, there are always exceptions…

My biggest single dotProject customer is huge and they're using dotProject for all their core operations. Of course, in the past couple months, they've hit a major problem…. performance.

They have approximately 400+ projects with 25% of those active at any given time. In addition, they have anywhere from 150-600 tasks on any given project. No, I'm not kidding. This gives gives them literally tens of thousands of tasks… which is necessary for their accounting and reporting to various government departments and agencies. It is quite literally the core of their operations.

The performance issue they hit was a bit odd. When saving a task – no matter how large the change, if any – it could take anywhere from 30-40s for a single save operation. Everything else happened quite quickly – even mass task updates – so it didn't seem like a database index issue, but there had to be something. After disecting the class structures and the program flow, the solution popped out.

If you open /dotproject/modules/tasks/do_task_aed.php and look around lines 154-160, you'll see the first half of the problem: The entire user allocation checking process is done twice… back to back. For each task, it wipes out the existing assigned users and resaves them. Then, for each user assigned to the task being saved, it recalculates their availability. It's a perfectly reasonable way of doing it, but you don't need to do it twice. In the above file, simply comment out one of the blocks of code. I commented out 158-160, but to each his own. That cut the task save delay by half. Duh. 😉

The second half of the problem is more subtle. When the above process removes the users from the task, it does the delete on the user_tasks table using the task_id. Unfortunately, that key isn't indexed. Add an index to that table and you should see another performance boost. This one is more related to how big your user_tasks table is, but that should be at least as big as your tasks table.

The third half of the problem is even more subtle and probably not relevant to most of you. If you're using any sorts of Custom Fields with a default configuration, you're taking another large hit. The custom_fields_values table does not have the indexes it should. Simply add indexes to the value_field_id and the value_object_id. By fixing this one, we saw a significant improvement not just in the task saving, but also in the View and within other modules that are using Custom Fields.

For reference's sake, these changes were found by comparing with the previous stable_2 branch in addition to their latest and greatest release (November 2007, 2.1.1). If you're using a semi-recent version of dotProject, most likely you are affected by these issues.

These are just a few of the performance fixes in web2project..