mantisbt:database_optimization
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
mantisbt:database_optimization [2006/10/30 06:28] – Correct code formatting RogerHill | mantisbt:database_optimization [2008/10/29 04:25] (current) – external edit 127.0.0.1 | ||
---|---|---|---|
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/ | The project I was working on used a Mantis installation based on Windows 2003 Server/ | ||
- | The filters were starting to become unusably slow, particularly the view_all_bug_page. Pressing the "Rest Filter" | + | The filters were starting to become unusably slow, particularly the view_all_bug_page. Pressing the "Reset Filter" |
===== The Investigation ===== | ===== The Investigation ===== | ||
- | The investigation of the problem centered on the filter code in code/ | + | The investigation of the problem centered on the filter code in core/ |
The existing filter_get_bug_rows() function is 813 lines of code - arguably a candidate for some " | The existing filter_get_bug_rows() function is 813 lines of code - arguably a candidate for some " | ||
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: | ||
</ | </ | ||
- | Where the # | + | Where the # |
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: | ||
</ | </ | ||
- | Then at line 853 change the SELECT to :- | + | Then at line 853 change the SELECT |
Line 108: | Line 110: | ||
# | # | ||
</ | </ | ||
+ | |||
+ | |||
+ | ===== 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:// | ||
===== 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 " | 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 " | ||
+ | |||
+ | ===== Future developments ===== | ||
+ | |||
+ | This modification has a number of limitations, | ||
+ | |||
+ | 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)