User Tools

  • Logged in as: anonymous (anonymous)
  • Log Out

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 06:28]
RogerHill Correct code formatting
mantisbt:database_optimization [2008/10/29 04:25] (current)
Line 1: Line 1:
 +====== Database Optimization ======
 +
 This page describes some database optimisations for SQL Server 2000. These are based on Mantis 1.0.2 This page describes some database optimisations for SQL Server 2000. These are based on Mantis 1.0.2
  
Line 4: Line 6:
  
 The project I was working on used a Mantis installation based on Windows 2003 Server/​IIS/​PHP and SQL Server. 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 filters were starting to become unusably slow, particularly the view_all_bug_page. Pressing the "Reset 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 =====
  
-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 investigation of the problem centered on the filter code in core/​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. 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.
Line 27: Line 29:
 ===== The Solution ===== ===== The Solution =====
  
-Given that the problem was caused by the excessively long in() clause, my solution was to convert the in(...) clause to a temporary table, so that the SQL show above becomes...+Given that the problem was caused by the excessively long in() clause, my solution was to convert the in(...) clause to a temporary table, so that the SQL shown above becomes...
  
 <code sql> <code sql>
Line 34: Line 36:
 </​code>​ </​code>​
  
-Where the #​filtered_ids table contains the list of bug_ids that were previously in the in(...) clause.+Where the #​filtered_ids table contains the list of bug_ids that were previously in the in(...) clause. This temporary table id also indexed and clustered on the id field so that we can squeeze the last drop of performance out of the table index.
  
 The full text of my solution is shown below: - The full text of my solution is shown below: -
  
-Firstly replace the code after line 724 with the following : -+Firstly replace the code from line 724 to 764 with the following : -
  
 <code php> <code php>
Line 90: Line 92:
 </​code>​ </​code>​
  
-Then at line 853 change the SELECT to :- +Then at line 853 change the SELECT ​statement ​to :- 
  
  
Line 108: Line 110:
  #  #
 </​code>​ </​code>​
 +
 +
 +===== Alternative solution by RZG =====
 +
 +NOTE BY RZG:
 +My IMHO simpler solution was to use subqueries. With just 2 small changes, we were able to fix the issues described on my company Mantis, which has well over 10000 issues on it. See [[http://​www.mantisbt.org/​bugs/​view.php?​id=7516|Issue 7516]].
  
 ===== How much difference does it make? ===== ===== How much difference does it make? =====
Line 118: Line 126:
  
 We now have over 6400 open issues. The view_all_bugs_page never takes more than 5 seconds to appear. Pages other than the  first  page such as accessed by clicking on the "​Next"​ link can be accessed in under 3 seconds. We now have over 6400 open issues. The view_all_bugs_page never takes more than 5 seconds to appear. Pages other than the  first  page such as accessed by clicking on the "​Next"​ link can be accessed in under 3 seconds.
 +
 +===== Future developments =====
 +
 +This modification has a number of limitations,​ particularly in that it is very much focused on SQL Server syntax. Ideally we would have an architecture in place in filter_api.php that allowed different database backends to use their own optimised search methods. This would probably require the implementation of architectural changes to MANTIS to allow for the use of variations in the query strategy with different SQL dialects. ​
 +
 +For SQL server, I think we would want to look at using a stored procedure to return the required dataset for a particular page in view_all_bugs_page.
  
mantisbt/database_optimization.1162207684.txt.gz ยท Last modified: 2008/10/29 04:31 (external edit)