User Tools

Site Tools


mantisbt:database_optimization

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
mantisbt:database_optimization [2006/10/30 05:28]
RogerHill
mantisbt:database_optimization [2006/12/31 01:23]
rzg Alternative IMHO simpler solution
Line 1: Line 1:
-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.txt ยท Last modified: 2008/10/29 04:25 (external edit)