Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 24160 invoked from network); 12 Apr 2007 20:20:56 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 12 Apr 2007 20:20:55 -0000 Received: (qmail 83735 invoked by uid 500); 12 Apr 2007 20:21:01 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 83338 invoked by uid 500); 12 Apr 2007 20:20:59 -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 83327 invoked by uid 99); 12 Apr 2007 20:20:59 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 12 Apr 2007 13:20:59 -0700 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: local policy) Received: from [63.82.5.211] (HELO ffex01.fnfr.com) (63.82.5.211) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 12 Apr 2007 13:20:51 -0700 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C77D40.0432E756" Subject: Derby select query speed questions Date: Thu, 12 Apr 2007 13:20:30 -0700 Message-ID: <619FD34741327C47AC7FBA8DBADBF5E0E19405@ffex01.fnfr.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Derby select query speed questions Thread-Index: Acd9QARHnd3FbiyHQIelnlFAAAMVYA== From: "Adam Bovill" To: X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. ------_=_NextPart_001_01C77D40.0432E756 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello, =20 Sorry if this has already been asked (I've read back through about a year of the archives and haven't seen/have missed it being asked before.) =20 When I perform queries on my derby database, the response times seem to be rather large. (At the bottom of this email you can see my schema) =20 Database: Sessions table has 19 rows Items table has 29000 rows =20 I perform my queries in the following way: return _DBconnection.createStatement().executeQuery(sql); =20 If I perform the following query on the sessions table: select id, sid from sessions where id =3D15 =20 it takes 30-50 ms =20 If I perform the following query on the items table: select id, aid from items where id =3D26111 =20 it takes 40-65 ms =20 Note that both of these queries are on the primary key =20 It would seem that these times were rather slow for such simple queries and small tables. Are these the expected times? Or are they slower that expected? =20 What steps should I take to try to improve the speed? At the moment I'm just running derby w/o changing any settings. (I'm running on a 3ghz p4 with a good bit of ram. Running under linux v. windows doesn't seem to make a difference. =20 I tried to turn on the derby logging with: System.setProperty("derby.language.logQueryPlan", "true"); I've added it before connecting and tried it after, but it never seems to create any log files =20 Thanks in advance for any help. =20 -Adam =20 -=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--= =3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D- -=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--= =3D=3D- #v14 #dropStart DROP TABLE infoTable; DROP TABLE sessionID; DROP TABLE sessions; DROP TABLE itemID; DROP TABLE items; #dropEnd #createStart CREATE TABLE infoTable( schemaVersion INT NOT NULL ); CREATE TABLE itemID( nextID BIGINT NOT NULL ); CREATE TABLE sessions( id BIGINT NOT NULL PRIMARY KEY, sid VARCHAR(16) NOT NULL, orderIndex BIGINT NOT NULL, type VARCHAR(32) DEFAULT '', context SMALLINT NOT NULL DEFAULT 1,=20 start BIGINT NOT NULL, endTime BIGINT, complete SMALLINT DEFAULT 0, application VARCHAR(256), description VARCHAR(256), profile VARCHAR(512), details CLOB ); CREATE TABLE sessionID( nextID BIGINT NOT NULL ); CREATE TABLE items( id BIGINT NOT NULL PRIMARY KEY, aid VARCHAR(16) NOT NULL, sessionID BIGINT NOT NULL, orderIndex BIGINT NOT NULL, type SMALLINT NOT NULL, context SMALLINT NOT NULL DEFAULT 1,=20 start BIGINT NOT NULL, endTime BIGINT, duration DOUBLE, complete SMALLINT, actionType VARCHAR(256), description VARCHAR(1024), details CLOB, response CLOB ); CREATE INDEX session_SID_index ON sessions (sid); CREATE INDEX session_orderIndex_index ON sessions (orderIndex); CREATE INDEX session_start_index ON sessions (start); CREATE INDEX session_start_desc_index ON sessions (start desc); CREATE INDEX session_application_index ON sessions (application); CREATE INDEX item_start_index ON items (start); CREATE INDEX item_start_desc_index ON items (start desc); CREATE INDEX item_sessionID_index ON items (sessionID); CREATE INDEX item_sessionIDorderIndex_index ON items (sessionID, orderIndex); INSERT INTO itemID VALUES(1);=20 INSERT INTO sessionID VALUES(1);=20 INSERT INTO infoTable (schemaVersion) VALUES (14); #createEnd =20 ------_=_NextPart_001_01C77D40.0432E756 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello,

 

Sorry if this has already been asked (I’ve read = back through about a year of the archives and haven’t seen/have missed = it being asked before.)

 

When I perform queries on my derby database, the = response times seem to be rather large.  (At the bottom of this email you = can see my schema)

 

Database:

Sessions table has 19 = rows

Items table has 29000 = rows

 

I perform my queries in the following = way:

        = return _DBconnection.createStatement().executeQuery(sql);

 

If I perform the following query on the sessions = table:

select id, sid from sessions where id = =3D15

 

it takes 30-50 ms

 

If I perform the following query on the items = table:

select id, aid from items where id = =3D26111

 

it takes 40-65 ms

 

Note that both of these queries are on the primary = key

 

It would seem that these times were rather slow for = such simple queries and small tables.  Are these the expected = times?  Or are they slower that expected?

 

What steps should I take to try to improve the = speed?  At the moment I’m just running derby w/o changing any settings. =  (I’m running on a 3ghz p4 with a good bit of ram.  Running under linux = v. windows doesn’t seem to make a = difference.

 

I tried to turn on the derby logging = with:

         =    System.setProperty("derby.language.logQueryPlan", = "true");

I’ve added it before connecting and tried it = after, but it never seems to create any log files

 

Thanks in advance for any = help.

 

-Adam

 

-=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D= --=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D-= -=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--=3D=3D--= =3D=3D--=3D=3D--=3D=3D-

#v14

#dropStart

DROP TABLE = infoTable;

DROP TABLE = sessionID;

DROP TABLE = sessions;

DROP TABLE = itemID;

DROP TABLE = items;

#dropEnd

#createStart

CREATE TABLE = infoTable(

      schemaVersion INT NOT NULL

      );

CREATE TABLE = itemID(

      nextID BIGINT NOT NULL

      );

CREATE TABLE = sessions(

      id BIGINT NOT NULL PRIMARY KEY,

      sid VARCHAR(16) NOT NULL,

      orderIndex BIGINT NOT NULL,

      type VARCHAR(32) DEFAULT '',

      context SMALLINT NOT NULL DEFAULT 1,

      start BIGINT NOT NULL,

      endTime BIGINT,

      complete SMALLINT DEFAULT 0,

      application VARCHAR(256),

      description VARCHAR(256),

      profile VARCHAR(512),

      details CLOB

);

CREATE TABLE = sessionID(

      nextID BIGINT NOT NULL

      );

CREATE TABLE = items(

      id BIGINT NOT NULL PRIMARY KEY,

      aid VARCHAR(16) NOT NULL,

      sessionID BIGINT NOT NULL,

      orderIndex BIGINT NOT NULL,

      type SMALLINT NOT NULL,

      context SMALLINT NOT NULL DEFAULT 1,

      start BIGINT NOT NULL,

      endTime BIGINT,

      duration DOUBLE,

      complete SMALLINT,

      actionType VARCHAR(256),

      description VARCHAR(1024),

      details CLOB,

      response CLOB

      );

CREATE INDEX session_SID_index ON sessions (sid);

CREATE INDEX session_orderIndex_index ON sessions = (orderIndex);

CREATE INDEX session_start_index ON sessions (start);

CREATE INDEX session_start_desc_index ON sessions (start = desc);

CREATE INDEX session_application_index ON sessions = (application);

CREATE INDEX item_start_index ON items (start);

CREATE INDEX item_start_desc_index ON items (start = desc);

CREATE INDEX item_sessionID_index ON items (sessionID);

CREATE INDEX item_sessionIDorderIndex_index ON items (sessionID, = orderIndex);

INSERT INTO itemID VALUES(1);

INSERT INTO = sessionID VALUES(1);

INSERT INTO = infoTable (schemaVersion) VALUES (14);

#createEnd

 

------_=_NextPart_001_01C77D40.0432E756--