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:09] – 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 14: | Line 16: | ||
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: - | 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: - | ||
- | '' | + | <code sql> |
select ... from mantis_bug_table join ...... | 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. | The key observation that I made was that SQL Server **hates** long in (...) lists. | ||
Line 25: | 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> |
select ... from mantis_bug_table join ...... | select ... from mantis_bug_table join ...... | ||
# | # | ||
- | '' | + | </ |
- | 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> |
# Possibly do two passes. First time, grab the IDs of issues that match the filters. Second time, grab the IDs of issues that | # Possibly do two passes. First time, grab the IDs of issues that match the filters. Second time, grab the IDs of issues that | ||
# have bugnotes that match the text search if necessary. | # have bugnotes that match the text search if necessary. | ||
Line 86: | Line 90: | ||
# Get the total number of bugs that meet the criteria. | # Get the total number of bugs that meet the criteria. | ||
$bug_count = $row_count; #count( $t_id_array ); | $bug_count = $row_count; #count( $t_id_array ); | ||
- | '' | + | </ |
- | Then at line 853 change the SELECT to :- | + | Then at line 853 change the SELECT |
- | '' | + | <code php> |
$t_order = " ORDER BY " . implode( ', ', $t_order_array ); | $t_order = " ORDER BY " . implode( ', ', $t_order_array ); | ||
$t_select = implode( ', ', array_unique( $t_select_clauses ) ); | $t_select = implode( ', ', array_unique( $t_select_clauses ) ); | ||
Line 105: | Line 109: | ||
# Figure out the offset into the db query | # Figure out the offset into the db query | ||
# | # | ||
- | '' | + | </ |
+ | |||
+ | |||
+ | ===== 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 111: | Line 121: | ||
I will admit that I haven' | I will admit that I haven' | ||
- | What I do know is that when we had about 1000 open issues, the view_all_bug_page was regularly taking over 40s to appear. (Sometimes the SQL caching would work, and it would appear straight away, other times, when a newly added bug changed the text of that in(...) clause the SQL would change, and everything would slow down again...) | + | What I do know is that when we had about 1000 open issues, the view_all_bug_page was regularly taking over 40s to appear. (Sometimes the SQL caching would work, and it would appear straight away, other times, when a newly added bug changed the text of that in(...) clause the SQL would change, and everything would slow down again...) |
In addition to the above SQL optimisation, | In addition to the above SQL optimisation, | ||
Line 117: | Line 127: | ||
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.1162206587.txt.gz · Last modified: 2008/10/29 04:31 (external edit)