User Tools

Site Tools


mantisbt:database_optimization

This is an old revision of the document!


This page describes some database optimisations for SQL Server 2000. These are based on Mantis 1.0.2

The Problem

The project I was working on used a Mantis installation based on Windows 2003 Server/IIS/PHP and SQL Server. The filters were starting to become unusably slow, particularly the view_all_bug_page. Pressing the “Rest Filter” button could produce a delay of 40-50 seconds before a page appeared. This problem was getting worse as we added more and more issues.

The Investigation

The investigation of the problem centered on the filter code in code/filter_api.php. The function filter_get_bug_rows() is the core of the bug filtering system.

The existing filter_get_bug_rows() function is 813 lines of code - arguably a candidate for some “refactoring with extreme prejudice” however that is beyond the scope of what I was trying to do here, which was to make MANTIS work acceptably under SQL server with more than 1000 open issues.

What is interesting is the area after line 724 in the original file. What happens here is that a list of relevant issues is created in the variable $t_id_array. This is then converted into a where clause at line 756. This produces a SQL statement which will be used to query mantis_bug_table of the form: -

select … from mantis_bug_table join …… where mantis_bug_table.id in ( 1, 10, 11, …… long list of bug ids … )

The key observation that I made was that SQL Server hates long in (…) lists. When your project has several thousand open issues, this in (…) clause will contain several thousand numbers. The result, in SQL Server at least, is misery.

The Solution

Given that the problem was caused by the excessively long in() clause, my solutionw as to convert the in(…) clause to a temporary table.

mantisbt/database_optimization.1162204104.txt.gz · Last modified: 2008/10/29 04:31 (external edit)