From derby-user-return-6584-apmail-db-derby-user-archive=db.apache.org@db.apache.org Mon Apr 09 22:27:42 2007 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 72425 invoked from network); 9 Apr 2007 22:27:41 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 9 Apr 2007 22:27:41 -0000 Received: (qmail 41540 invoked by uid 500); 9 Apr 2007 22:27:46 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 41350 invoked by uid 500); 9 Apr 2007 22:27:46 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 41319 invoked by uid 99); 9 Apr 2007 22:27:46 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 09 Apr 2007 15:27:46 -0700 X-ASF-Spam-Status: No, hits=0.6 required=10.0 tests=UPPERCASE_50_75 X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: local policy) Received: from [163.252.31.54] (HELO mailhost1.sra.com) (163.252.31.54) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 09 Apr 2007 15:27:38 -0700 Received: from flex2.sra.com ([163.252.31.9]) by mailhost1.sra.com with Microsoft SMTPSVC(6.0.3790.211); Mon, 9 Apr 2007 18:27:17 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: RE: Performance Tuning Problem Date: Mon, 9 Apr 2007 18:27:16 -0400 Message-ID: <899E3FF729410A419B3D4571332679460E734C7B@flex2.sra.com> In-Reply-To: <461AB581.9070409@gmail.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Performance Tuning Problem Thread-Index: Acd68V8Cpz1ITeHWQ/md7UtrHw8GfwAApujw From: "Kane, David" To: "Derby Discussion" X-OriginalArrivalTime: 09 Apr 2007 22:27:17.0035 (UTC) FILETIME=[3ACC27B0:01C77AF6] X-Virus-Checked: Checked by ClamAV on apache.org Folks, I think I have answers to all of Army's questions here. =20 * The request DDL is listed below. * I included the output in the original posting. The first query has 14 matching records. The second query has 2. * The indexed were created as part of copying the database using (http://dbcopyplugin.sourceforge.net/). I assume the data was copied first, but I do not know for sure. Would it make sense to try: alter table compress [sequential] =20 That is mentioned in DERBY-269, or perhaps just drop and recreate the indexes? * I saw DERBY-47 as I investigated the problem. I fiddled with the query design a bit to avoid the IN operators, and I built a version of derby off the SVN repository to see if I could pick up the fix for that, but neither of those made things better. * The zip archive is about 1gb. The data is public so I don't mind posting it if that would be helpful. Is that too big for Jira? =20 Sincerely, David Kane -- Timestamp: 2007-04-09 18:10:27.739 -- Source database is: C:\gominer10.2 -- Connection URL is: jdbc:derby:C:\gominer10.2 -- appendLogs: false -- ---------------------------------------------- -- DDL Statements for tables -- ---------------------------------------------- CREATE TABLE "APP"."DBXREF" ("ID" INTEGER NOT NULL, "XREF_KEY" VARCHAR(255) NOT NULL, "XREF_KEYTYPE" VARCHAR(32), "XREF_DBNAME" VARCHAR(55) NOT NULL, "XREF_DESC" VARCHAR(255)); CREATE TABLE "APP"."DBCOPYTEST" ("SPECIES" CHAR(10)); CREATE TABLE "APP"."SEQ_PROPERTY" ("ID" INTEGER NOT NULL, "SEQ_ID" INTEGER NOT NULL, "PROPERTY_KEY" VARCHAR(64) NOT NULL, "PROPERTY_VAL" VARCHAR(255) NOT NULL); CREATE TABLE "APP"."GENE_PRODUCT" ("ID" INTEGER NOT NULL, "SYMBOL" VARCHAR(128) NOT NULL, "DBXREF_ID" INTEGER NOT NULL, "SPECIES_ID" INTEGER, "FULL_NAME" CLOB(65535), "OFFICIALNAME" VARCHAR(50)); CREATE TABLE "APP"."TERM_SYNONYM" ("TERM_ID" INTEGER NOT NULL, "TERM_SYNONYM" VARCHAR(255), "ACC_SYNONYM" INTEGER); CREATE TABLE "APP"."EVIDENCE" ("ID" INTEGER NOT NULL, "CODE" VARCHAR(8) NOT NULL, "ASSOCIATION_ID" INTEGER NOT NULL, "DBXREF_ID" INTEGER NOT NULL, "SEQ_ACC" VARCHAR(255)); CREATE TABLE "APP"."GENE_PRODUCT_SEQ" ("GENE_PRODUCT_ID" INTEGER NOT NULL, "SEQ_ID" INTEGER NOT NULL, "IS_PRIMARY_SEQ" INTEGER); CREATE TABLE "APP"."GENE_PRODUCT_SYNONYM" ("GENE_PRODUCT_ID" INTEGER NOT NULL, "PRODUCT_SYNONYM" VARCHAR(255) NOT NULL); CREATE TABLE "APP"."GRAPH_PATH" ("ID" INTEGER NOT NULL, "TERM1_ID" INTEGER NOT NULL, "TERM2_ID" INTEGER NOT NULL, "DISTANCE" INTEGER NOT NULL); CREATE TABLE "APP"."SPECIES" ("ID" INTEGER NOT NULL, "NCBI_TAXA_ID" INTEGER, "COMMON_NAME" VARCHAR(255), "LINEAGE_STRING" CLOB(65535), "GENUS" VARCHAR(55), "SPECIES" VARCHAR(55)); CREATE TABLE "APP"."TERM_DBXREF" ("TERM_ID" INTEGER NOT NULL, "DBXREF_ID" INTEGER NOT NULL); CREATE TABLE "APP"."ASSOCIATION" ("ID" INTEGER NOT NULL, "TERM_ID" INTEGER NOT NULL, "GENE_PRODUCT_ID" INTEGER NOT NULL, "IS_NOT" INTEGER, "ROLE_GROUP" INTEGER); CREATE TABLE "APP"."GENE_PRODUCT_PROPERTY" ("GENE_PRODUCT_ID" INTEGER NOT NULL, "PROPERTY_KEY" VARCHAR(64) NOT NULL, "PROPERTY_VAL" VARCHAR(255)); CREATE TABLE "APP"."GENE_PRODUCT_COUNT" ("TERM_ID" INTEGER NOT NULL, "CODE" VARCHAR(8), "SPECIESDBNAME" VARCHAR(55) NOT NULL, "PRODUCT_COUNT" INTEGER NOT NULL); CREATE TABLE "APP"."TERM_DEFINITION" ("TERM_ID" INTEGER NOT NULL, "TERM_DEFINITION" CLOB(65535) NOT NULL, "COMMENT" CLOB(16277215), "REFERENCE" VARCHAR(255)); CREATE TABLE "APP"."INSTANCE_DATA" ("RELEASE_NAME" VARCHAR(255), "RELEASE_TYPE" VARCHAR(255), "RELEASE_NOTES" CLOB(65535)); CREATE TABLE "APP"."SEQ_DBXREF" ("SEQ_ID" INTEGER NOT NULL, "DBXREF_ID" INTEGER NOT NULL); CREATE TABLE "APP"."GRAPH_PATH2TERM" ("GRAPH_PATH_ID" INTEGER NOT NULL, "TERM_ID" INTEGER NOT NULL, "RANK" INTEGER NOT NULL); CREATE TABLE "APP"."TERM2TERM" ("ID" INTEGER NOT NULL, "RELATIONSHIP_TYPE_ID" INTEGER NOT NULL, "TERM1_ID" INTEGER NOT NULL, "TERM2_ID" INTEGER NOT NULL); CREATE TABLE "APP"."TERM" ("ID" INTEGER NOT NULL, "NAME" VARCHAR(255) NOT NULL, "TERM_TYPE" VARCHAR(55) NOT NULL, "ACC" VARCHAR(32) NOT NULL, "IS_OBSOLETE" INTEGER NOT NULL, "IS_ROOT" INTEGER NOT NULL); -- ---------------------------------------------- -- DDL Statements for indexes -- ---------------------------------------------- CREATE INDEX "APP"."SEQP1" ON "APP"."SEQ_PROPERTY" ("PROPERTY_KEY"); CREATE INDEX "APP"."SEQP2" ON "APP"."SEQ_PROPERTY" ("PROPERTY_VAL"); CREATE INDEX "APP"."SEQ_ID" ON "APP"."SEQ_PROPERTY" ("SEQ_ID", "PROPERTY_KEY", "PROPERTY_VAL"); CREATE INDEX "APP"."SEQP0" ON "APP"."SEQ_PROPERTY" ("SEQ_ID"); CREATE INDEX "APP"."T1" ON "APP"."TERM" ("NAME"); CREATE INDEX "APP"."T2" ON "APP"."TERM" ("TERM_TYPE"); CREATE INDEX "APP"."T3" ON "APP"."TERM" ("ACC"); CREATE INDEX "APP"."GPP2" ON "APP"."GENE_PRODUCT_PROPERTY" ("PROPERTY_KEY"); CREATE INDEX "APP"."GPP3" ON "APP"."GENE_PRODUCT_PROPERTY" ("PROPERTY_VAL"); CREATE INDEX "APP"."GPPU4" ON "APP"."GENE_PRODUCT_PROPERTY" ("GENE_PRODUCT_ID", "PROPERTY_KEY", "PROPERTY_VAL"); CREATE INDEX "APP"."GPP1" ON "APP"."GENE_PRODUCT_PROPERTY" ("GENE_PRODUCT_ID"); CREATE INDEX "APP"."A3" ON "APP"."ASSOCIATION" ("TERM_ID", "GENE_PRODUCT_ID"); CREATE INDEX "APP"."A4" ON "APP"."ASSOCIATION" ("ID", "TERM_ID", "GENE_PRODUCT_ID"); CREATE INDEX "APP"."A1" ON "APP"."ASSOCIATION" ("TERM_ID"); CREATE INDEX "APP"."A2" ON "APP"."ASSOCIATION" ("GENE_PRODUCT_ID"); CREATE INDEX "APP"."TT1" ON "APP"."TERM2TERM" ("TERM1_ID"); CREATE INDEX "APP"."TERM1_ID" ON "APP"."TERM2TERM" ("TERM1_ID", "TERM2_ID", "RELATIONSHIP_TYPE_ID"); CREATE INDEX "APP"."TT2" ON "APP"."TERM2TERM" ("TERM2_ID"); CREATE INDEX "APP"."TT3" ON "APP"."TERM2TERM" ("TERM1_ID", "TERM2_ID"); CREATE INDEX "APP"."TT4" ON "APP"."TERM2TERM" ("RELATIONSHIP_TYPE_ID"); CREATE INDEX "APP"."GS1" ON "APP"."GENE_PRODUCT_SYNONYM" ("GENE_PRODUCT_ID"); CREATE INDEX "APP"."GS2" ON "APP"."GENE_PRODUCT_SYNONYM" ("PRODUCT_SYNONYM"); CREATE INDEX "APP"."GENE_PRODUCT_ID" ON "APP"."GENE_PRODUCT_SYNONYM" ("GENE_PRODUCT_ID", "PRODUCT_SYNONYM"); CREATE INDEX "APP"."TX0" ON "APP"."TERM_DBXREF" ("TERM_ID"); CREATE INDEX "APP"."TX1" ON "APP"."TERM_DBXREF" ("DBXREF_ID"); CREATE INDEX "APP"."TX2" ON "APP"."TERM_DBXREF" ("TERM_ID", "DBXREF_ID"); CREATE INDEX "APP"."EV4" ON "APP"."EVIDENCE" ("ASSOCIATION_ID", "CODE"); CREATE INDEX "APP"."ASSOCIATION_ID" ON "APP"."EVIDENCE" ("ASSOCIATION_ID", "DBXREF_ID", "CODE", "SEQ_ACC"); CREATE INDEX "APP"."EV1" ON "APP"."EVIDENCE" ("ASSOCIATION_ID"); CREATE INDEX "APP"."EV2" ON "APP"."EVIDENCE" ("CODE"); CREATE INDEX "APP"."EV3" ON "APP"."EVIDENCE" ("DBXREF_ID"); CREATE INDEX "APP"."SEQX0" ON "APP"."SEQ_DBXREF" ("SEQ_ID"); CREATE INDEX "APP"."SEQX1" ON "APP"."SEQ_DBXREF" ("DBXREF_ID"); CREATE INDEX "APP"."SEQX2" ON "APP"."SEQ_DBXREF" ("SEQ_ID", "DBXREF_ID"); CREATE INDEX "APP"."TD1" ON "APP"."TERM_DEFINITION" ("TERM_ID"); CREATE INDEX "APP"."MYINDEXTEST1" ON "APP"."GENE_PRODUCT" ("ID", "SYMBOL", "DBXREF_ID"); CREATE INDEX "APP"."G2" ON "APP"."GENE_PRODUCT" ("DBXREF_ID"); CREATE INDEX "APP"."G3" ON "APP"."GENE_PRODUCT" ("SPECIES_ID"); CREATE INDEX "APP"."G4" ON "APP"."GENE_PRODUCT" ("ID", "SPECIES_ID"); CREATE INDEX "APP"."G5" ON "APP"."GENE_PRODUCT" ("DBXREF_ID", "SPECIES_ID"); CREATE INDEX "APP"."G6" ON "APP"."GENE_PRODUCT" ("ID", "DBXREF_ID"); CREATE INDEX "APP"."OFFNAMEINDEX" ON "APP"."GENE_PRODUCT" ("OFFICIALNAME"); CREATE INDEX "APP"."SYMBOL" ON "APP"."GENE_PRODUCT" ("SYMBOL", "DBXREF_ID"); CREATE INDEX "APP"."G1" ON "APP"."GENE_PRODUCT" ("SYMBOL"); CREATE INDEX "APP"."SP2" ON "APP"."SPECIES" ("COMMON_NAME"); CREATE INDEX "APP"."SP3" ON "APP"."SPECIES" ("GENUS"); CREATE INDEX "APP"."SP4" ON "APP"."SPECIES" ("SPECIES"); CREATE INDEX "APP"."SP5" ON "APP"."SPECIES" ("GENUS", "SPECIES"); CREATE INDEX "APP"."SP6" ON "APP"."SPECIES" ("ID", "NCBI_TAXA_ID"); CREATE INDEX "APP"."SP1" ON "APP"."SPECIES" ("NCBI_TAXA_ID"); CREATE INDEX "APP"."GRAPH_PATH1" ON "APP"."GRAPH_PATH" ("TERM1_ID"); CREATE INDEX "APP"."GRAPH_PATH2" ON "APP"."GRAPH_PATH" ("TERM2_ID"); CREATE INDEX "APP"."GRAPH_PATH3" ON "APP"."GRAPH_PATH" ("TERM1_ID", "TERM2_ID"); CREATE INDEX "APP"."GRAPH_PATH4" ON "APP"."GRAPH_PATH" ("TERM1_ID", "DISTANCE"); CREATE INDEX "APP"."DX1" ON "APP"."DBXREF" ("XREF_DBNAME"); CREATE INDEX "APP"."DX2" ON "APP"."DBXREF" ("XREF_KEY"); CREATE INDEX "APP"."DX3" ON "APP"."DBXREF" ("ID", "XREF_DBNAME"); CREATE INDEX "APP"."DX4" ON "APP"."DBXREF" ("ID", "XREF_KEY", "XREF_DBNAME"); CREATE INDEX "APP"."XREF_KEY" ON "APP"."DBXREF" ("XREF_KEY", "XREF_DBNAME"); CREATE INDEX "APP"."TS1" ON "APP"."TERM_SYNONYM" ("TERM_ID"); CREATE INDEX "APP"."TS2" ON "APP"."TERM_SYNONYM" ("TERM_SYNONYM"); CREATE INDEX "APP"."TS3" ON "APP"."TERM_SYNONYM" ("TERM_ID", "TERM_SYNONYM"); CREATE INDEX "APP"."GPSEQ1" ON "APP"."GENE_PRODUCT_SEQ" ("GENE_PRODUCT_ID"); CREATE INDEX "APP"."GPSEQ2" ON "APP"."GENE_PRODUCT_SEQ" ("SEQ_ID"); CREATE INDEX "APP"."GPSEQ3" ON "APP"."GENE_PRODUCT_SEQ" ("SEQ_ID", "GENE_PRODUCT_ID"); CREATE INDEX "APP"."GPC1" ON "APP"."GENE_PRODUCT_COUNT" ("TERM_ID"); CREATE INDEX "APP"."GPC2" ON "APP"."GENE_PRODUCT_COUNT" ("CODE"); CREATE INDEX "APP"."GPC3" ON "APP"."GENE_PRODUCT_COUNT" ("SPECIESDBNAME"); CREATE INDEX "APP"."GPC4" ON "APP"."GENE_PRODUCT_COUNT" ("TERM_ID", "CODE", "SPECIESDBNAME"); -- ---------------------------------------------- -- DDL Statements for keys -- ---------------------------------------------- -- primary/unique ALTER TABLE "APP"."SEQ_PROPERTY" ADD CONSTRAINT "SQL070329021711680" PRIMARY KEY ("ID"); ALTER TABLE "APP"."TERM" ADD CONSTRAINT "SQL070329113915100" PRIMARY KEY ("ID"); ALTER TABLE "APP"."ASSOCIATION" ADD CONSTRAINT "SQL070329021801090" PRIMARY KEY ("ID"); ALTER TABLE "APP"."TERM2TERM" ADD CONSTRAINT "SQL070329113935130" PRIMARY KEY ("ID"); ALTER TABLE "APP"."EVIDENCE" ADD CONSTRAINT "SQL070329082038020" PRIMARY KEY ("ID"); ALTER TABLE "APP"."GENE_PRODUCT" ADD CONSTRAINT "SQL070329100902940" PRIMARY KEY ("ID"); ALTER TABLE "APP"."SPECIES" ADD CONSTRAINT "SQL070330024322060" PRIMARY KEY ("ID"); ALTER TABLE "APP"."GRAPH_PATH" ADD CONSTRAINT "SQL070329112728690" PRIMARY KEY ("ID"); ALTER TABLE "APP"."DBXREF" ADD CONSTRAINT "SQL070329072505180" PRIMARY KEY ("ID"); -----Original Message----- From: Army [mailto:qozinx@gmail.com]=20 Sent: Monday, April 09, 2007 5:52 PM To: Derby Discussion Subject: Re: Performance Tuning Problem Kane, David wrote: >=20 > Here are the indexes that are being reported on the ASSOCIATION table: [ snip index display ] Hate to be a nuisance, but can you post the DDL for the tables in the query? I=20 find it easier to make sense of the DDL than to parse the index table (maybe=20 it's just me). You can get that by using "dblook": http://db.apache.org/derby/docs/dev/tools/ctoolsdblook.html Ex. java org.apache.derby.tools.dblook -d jdbc:derby:mydb -t association Might be nice to get the DDL for all of the tables in the query, not just=20 "association". > Even if there was an index problem here, would that explain why it seems > to be taking so long to loop through the ResultSet object? Can you say how many rows the two queries that you posted return? If you have a table with over 10 million rows in it (which you do) then any=20 index problem that causes the optimizer to choose a table scan (which it does,=20 according to the program output) could definitely cause a slowdown. Especially=20 if that 10 million row table is then joined with another table that has over a=20 million rows of its own (which is, I think, what your query is doing). So yes,=20 an index problem could definitely explain the behavior you're seeing. Did you by chance create the indexes before you inserted the data? If so, there=20 are known problems with doing so: see esp. DERBY-269. Given that, it's=20 generally a good idea to populate the tables first and *then* create the indexes=20 afterward. Doing so provides Derby with better statistics, which in turn leads=20 to better cost estimates and (hopefully) better plans. I also noticed that the "moreComplexQueryResults" query includes several IN=20 clauses. Depending on the indexes present, that query may be suffering from=20 DERBY-47, for which some changes have already been made for the upcoming release. See that Jira (https://issues.apache.org/jira/browse/DERBY-47) for=20 more details. > I have put a copy of the derby database at ftp:// How large is this database zip file? Is it possible to post it to a Derby Jira=20 issue for ease of community access? Army