From d370ff3d3dff3cc59d092ce2eb48edd5ae8198df Mon Sep 17 00:00:00 2001 From: simonbrandhof Date: Wed, 2 Nov 2011 07:50:52 +0100 Subject: SONAR-2962 Add an index on dependencies.project_snapshot_id to improve performance --- .../java/org/sonar/jpa/entity/SchemaMigration.java | 2 +- .../resources/org/sonar/persistence/rows-derby.sql | 1 + .../org/sonar/persistence/schema-derby.ddl | 2 ++ ...2_add_index_on_dependencies_project_snapshot.rb | 30 ++++++++++++++++++++++ .../src/main/webapp/WEB-INF/db/migrate/README.txt | 2 +- .../org/sonar/test/persistence/sonar-test.ddl | 1 + 6 files changed, 36 insertions(+), 2 deletions(-) create mode 100644 sonar-server/src/main/webapp/WEB-INF/db/migrate/222_add_index_on_dependencies_project_snapshot.rb diff --git a/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java b/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java index a645d78d5c8..2184d19b05b 100644 --- a/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java +++ b/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java @@ -42,7 +42,7 @@ public class SchemaMigration { - complete the Derby DDL file used for unit tests : sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl */ - public static final int LAST_VERSION = 221; + public static final int LAST_VERSION = 222; public final static String TABLE_NAME = "schema_migrations"; diff --git a/sonar-core/src/main/resources/org/sonar/persistence/rows-derby.sql b/sonar-core/src/main/resources/org/sonar/persistence/rows-derby.sql index 9970b86ccba..66adfa97455 100644 --- a/sonar-core/src/main/resources/org/sonar/persistence/rows-derby.sql +++ b/sonar-core/src/main/resources/org/sonar/persistence/rows-derby.sql @@ -163,6 +163,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('216'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('217'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('220'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('221'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('222'); INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, CRYPTED_PASSWORD, SALT, CREATED_AT, UPDATED_AT, REMEMBER_TOKEN, REMEMBER_TOKEN_EXPIRES_AT) VALUES (1, 'admin', 'Administrator', '', 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', '2011-09-26 22:27:48.0', '2011-09-26 22:27:48.0', null, null); ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2; diff --git a/sonar-core/src/main/resources/org/sonar/persistence/schema-derby.ddl b/sonar-core/src/main/resources/org/sonar/persistence/schema-derby.ddl index cc8a7e40f3e..5a3672b6dd1 100644 --- a/sonar-core/src/main/resources/org/sonar/persistence/schema-derby.ddl +++ b/sonar-core/src/main/resources/org/sonar/persistence/schema-derby.ddl @@ -119,6 +119,8 @@ CREATE INDEX "DEPS_TO_SID" ON "DEPENDENCIES" ("TO_SNAPSHOT_ID"); CREATE INDEX "DEPS_FROM_SID" ON "DEPENDENCIES" ("FROM_SNAPSHOT_ID"); +CREATE INDEX "DEPS_PRJ_SID" ON "DEPENDENCIES" ("PROJECT_SNAPSHOT_ID"); + CREATE INDEX "MEASURES_SID_METRIC" ON "PROJECT_MEASURES" ("SNAPSHOT_ID", "METRIC_ID"); CREATE INDEX "ACTIVE_RULE_CHANGES_PID" ON "ACTIVE_RULE_CHANGES" ("PROFILE_ID"); diff --git a/sonar-server/src/main/webapp/WEB-INF/db/migrate/222_add_index_on_dependencies_project_snapshot.rb b/sonar-server/src/main/webapp/WEB-INF/db/migrate/222_add_index_on_dependencies_project_snapshot.rb new file mode 100644 index 00000000000..77b9e26a93b --- /dev/null +++ b/sonar-server/src/main/webapp/WEB-INF/db/migrate/222_add_index_on_dependencies_project_snapshot.rb @@ -0,0 +1,30 @@ +# +# Sonar, entreprise quality control tool. +# Copyright (C) 2008-2011 SonarSource +# mailto:contact AT sonarsource DOT com +# +# Sonar is free software; you can redistribute it and/or +# modify it under the terms of the GNU Lesser General Public +# License as published by the Free Software Foundation; either +# version 3 of the License, or (at your option) any later version. +# +# Sonar is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# Lesser General Public License for more details. +# +# You should have received a copy of the GNU Lesser General Public +# License along with Sonar; if not, write to the Free Software +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 +# + +# +# Sonar 2.12 +# +class AddIndexOnDependenciesProjectSnapshot < ActiveRecord::Migration + + def self.up + add_index('dependencies', 'project_snapshot_id', :name => 'deps_prj_sid') + end + +end diff --git a/sonar-server/src/main/webapp/WEB-INF/db/migrate/README.txt b/sonar-server/src/main/webapp/WEB-INF/db/migrate/README.txt index ab8f7317bc0..94781a7cf53 100644 --- a/sonar-server/src/main/webapp/WEB-INF/db/migrate/README.txt +++ b/sonar-server/src/main/webapp/WEB-INF/db/migrate/README.txt @@ -3,7 +3,7 @@ HOW TO ADD A MIGRATION * Jump some versions when adding the first Ruby on Rails migration of a new sonar version. For example if sonar 2.10 is 193, then sonar 2.11 should start at 200. * Complete the DDL files for Derby : + sonar-core/src/main/resources/org/sonar/persistence/schema-derby.ddl - + complete sonar-core/src/main/resources/org/sonar/persistence/rows-derby.ddl : + + complete sonar-core/src/main/resources/org/sonar/persistence/rows-derby.sql : - add "INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('')" * Update the migration id defined in sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java * To be removed soon : complete sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl diff --git a/sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl b/sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl index 7fa023eb3f2..60d8419db75 100644 --- a/sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl +++ b/sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl @@ -135,6 +135,7 @@ create table DEPENDENCIES ( ); CREATE INDEX DEPS_FROM_SID ON DEPENDENCIES (FROM_SNAPSHOT_ID); CREATE INDEX DEPS_TO_SID ON DEPENDENCIES (TO_SNAPSHOT_ID); +CREATE INDEX DEPS_PRJ_SID ON DEPENDENCIES (PROJECT_SNAPSHOT_ID); create table EVENTS ( ID INTEGER not null, -- cgit v1.2.3