db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Adam Bovill" <abov...@fnfr.com>
Subject Derby select query speed questions
Date Thu, 12 Apr 2007 20:20:30 GMT
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 =15

 

it takes 30-50 ms

 

If I perform the following query on the items table:

select id, aid from items where id =26111

 

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

 

-==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==-
-==--==--==--==--==--==--==--==--==--==-

#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

 


Mime
View raw message