mantisbt:database_optimization
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionLast revisionBoth sides next revision | ||
mantisbt:database_optimization [2006/10/30 06:09] – RogerHill | mantisbt:database_optimization [2006/12/31 01:23] – Alternative IMHO simpler solution rzg | ||
---|---|---|---|
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/ | ||
- | The filters were starting to become unusably slow, particularly the view_all_bug_page. Pressing the "Rest Filter" | ||
- | |||
- | ===== The Investigation ===== | ||
- | |||
- | The investigation of the problem centered on the filter code in code/ | ||
- | |||
- | The existing filter_get_bug_rows() function is 813 lines of code - arguably a candidate for some " | ||
- | |||
- | 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 ...... | ||
- | | ||
- | | ||
- | '' | ||
- | |||
- | 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 solution was to convert the in(...) clause to a temporary table, so that the SQL show above becomes... | ||
- | |||
- | '' | ||
- | select ... from mantis_bug_table join ...... | ||
- | # | ||
- | '' | ||
- | |||
- | Where the # | ||
- | |||
- | The full text of my solution is shown below: - | ||
- | |||
- | Firstly replace the code after line 724 with the following : - | ||
- | |||
- | '' | ||
- | # 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. | ||
- | |||
- | # | ||
- | # Using a long list of bug id's for subsequent select statements does not seem to work well in SQL server | ||
- | # so.... we will select the list of bug id's into a temp table | ||
- | # | ||
- | db_query(" | ||
- | " | ||
- | db_query( " | ||
- | db_query( " | ||
- | |||
- | $t_id_array = array(); | ||
- | |||
- | $t_id_where = $t_where; | ||
- | $t_id_join = $t_join; | ||
- | if ( !is_blank( $t_id_where ) && !is_blank( $t_textsearch_where_clause ) ) { | ||
- | $t_id_where = $t_id_where . ' AND ' . $t_textsearch_where_clause; | ||
- | } | ||
- | $query | ||
- | $t_from | ||
- | $t_id_join | ||
- | $t_id_where"; | ||
- | db_query( $query ); | ||
- | |||
- | $t_id_where = $t_where; | ||
- | $t_id_join = $t_join; | ||
- | if ( !is_blank( $t_textsearch_wherejoin_clause ) ) { | ||
- | $t_id_where = $t_id_where . ' AND ' . $t_textsearch_wherejoin_clause; | ||
- | $t_id_join = $t_id_join . " INNER JOIN $t_bugnote_table ON $t_bugnote_table.bug_id = $t_bug_table.id"; | ||
- | $t_id_join = $t_id_join . " INNER JOIN $t_bugnote_text_table ON $t_bugnote_text_table.id = $t_bugnote_table.bugnote_text_id"; | ||
- | $query | ||
- | $t_from | ||
- | $t_id_join | ||
- | $t_id_where"; | ||
- | db_query( $query ); | ||
- | } | ||
- | |||
- | $query | ||
- | $result = db_query( $query ); | ||
- | $row = db_fetch_array( $result); | ||
- | $row_count = $row[' | ||
- | |||
- | $t_from = 'FROM ' . $t_bug_table; | ||
- | |||
- | # Get the total number of bugs that meet the criteria. | ||
- | $bug_count = $row_count; #count( $t_id_array ); | ||
- | '' | ||
- | |||
- | Then at line 853 change the SELECT to :- | ||
- | |||
- | |||
- | '' | ||
- | $t_order = " ORDER BY " . implode( ', ', $t_order_array ); | ||
- | $t_select = implode( ', ', array_unique( $t_select_clauses ) ); | ||
- | |||
- | # Modified to optimize it for SQL Server | ||
- | # Join to the temp table of ids that we have created. | ||
- | $t_join .= " JOIN # | ||
- | $query2 | ||
- | $t_from | ||
- | $t_join | ||
- | $t_order"; | ||
- | |||
- | # Figure out the offset into the db query | ||
- | # | ||
- | '' | ||
- | |||
- | ===== How much difference does it make? ===== | ||
- | |||
- | 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...) | ||
- | |||
- | In addition to the above SQL optimisation, | ||
- | |||
- | 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 " | ||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
mantisbt/database_optimization.txt · Last modified: 2008/10/29 04:25 by 127.0.0.1