You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Adam Bovill <ab...@fnfr.com> on 2007/04/12 22:20:30 UTC

Derby select query speed questions

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

 


Re: Derby select query speed questions

Posted by Stanley Bradbury <St...@gmail.com>.
Hi Adam -
If I understand you correctly then what you suggest will work fine and 
you don't need to do anything.  Once the PreparedStatement is compiled 
and cached it will remain in the cache until it either 1) ages out 
(because the memory is needed for other PreparedStatements and this one 
hasn't been used) or 2) Derby is shutdown.  This is what Øystein was 
saying when he wrote:

Derby has a statement cache so if the statement is lexically equal to a previous statement, the result of the previous compilation will be reused, and recompilation will not be needed.

If there is a known set of  prepared statements that are executed you 
can prepare them all in a background thread immediately after Derby is 
started.  They will not need to be compiled again, just set the values 
and execute the required statement. 

If this approach is impractical then you will need to pay the price of 
parsing the statement when it is prepared BUT it will not be compiled if 
the plan is found in the statement cache.  If, after parsing, the 
statement is NOT found in the cache then compilation will happen.  The 
next time that statement is prepared only parsing will happen.

HTH

Adam Bovill wrote:
> In my application, I have a blocking queue that different processes can add database tasks to.  We need this because we need to have the application start up instantly and not wait a few seconds for the DB to initialize.
>
> There is one process that handles all database operations and takes items off the queue and runs them.
>
> So this queue could have several items on it.  These items at the moment are just the PreparedStatement, with its values set.  The issue is that I only have one instance of the PreparedStatement now.  So if I reset the parameters on it or do anything like that while one item is in the queue, it would effect both items in the queue.
>
> One option that I have would be to queue up the PreparedStatement and the values that should be set.  Then when it goes to execute, it sets the parameters and executes the query.  
>
> What I was wondering was whether I could make a copy of the already prepared statement and just set the values on that copy.  Thereby avoiding the statement compile time.
>
> Thanks,
>
> -Adam
>
> -----Original Message-----
> From: Oystein.Grovlen@Sun.COM [mailto:Oystein.Grovlen@Sun.COM] 
> Sent: Saturday, April 14, 2007 1:11 AM
> To: Derby Discussion
> Subject: Re: Derby select query speed questions
>
> Adam Bovill wrote:
>  > Hi Olav,
>  >
>  > Thanks.  That seems to have improved things.
>  >
>  > I was wondering whether or not there was a way to create one 
> PreparedStatement from another.  I have the first one that I've created 
> and would like to clone or duplicate this, w/o needing to recompile it.
>  >
>  > So when I'm using a PreparedStatement, I can set the parameters w/o 
> incurring too much of a penalty because it's already compiled?
>
> In order to answer this it would be good to know a bit more about what
> you are trying to achieve.  Why do need several prepared statements?
> Note that a prepared statement is local to a connection.  If you want
> to execute the same statement in another connection, you will have to
> prepare it for that connection.  However, Derby has a statement cache
> so if the statement is lexically equal to a previous statement, the
> result of the previous compilation will be reused, and recompilation
> will not be needed.
>
> --
> Øystein
>
>   



RE: Derby select query speed questions

Posted by Adam Bovill <ab...@fnfr.com>.
In my application, I have a blocking queue that different processes can add database tasks to.  We need this because we need to have the application start up instantly and not wait a few seconds for the DB to initialize.

There is one process that handles all database operations and takes items off the queue and runs them.

So this queue could have several items on it.  These items at the moment are just the PreparedStatement, with its values set.  The issue is that I only have one instance of the PreparedStatement now.  So if I reset the parameters on it or do anything like that while one item is in the queue, it would effect both items in the queue.

One option that I have would be to queue up the PreparedStatement and the values that should be set.  Then when it goes to execute, it sets the parameters and executes the query.  

What I was wondering was whether I could make a copy of the already prepared statement and just set the values on that copy.  Thereby avoiding the statement compile time.

Thanks,

-Adam

-----Original Message-----
From: Oystein.Grovlen@Sun.COM [mailto:Oystein.Grovlen@Sun.COM] 
Sent: Saturday, April 14, 2007 1:11 AM
To: Derby Discussion
Subject: Re: Derby select query speed questions

Adam Bovill wrote:
 > Hi Olav,
 >
 > Thanks.  That seems to have improved things.
 >
 > I was wondering whether or not there was a way to create one 
PreparedStatement from another.  I have the first one that I've created 
and would like to clone or duplicate this, w/o needing to recompile it.
 >
 > So when I'm using a PreparedStatement, I can set the parameters w/o 
incurring too much of a penalty because it's already compiled?

In order to answer this it would be good to know a bit more about what
you are trying to achieve.  Why do need several prepared statements?
Note that a prepared statement is local to a connection.  If you want
to execute the same statement in another connection, you will have to
prepare it for that connection.  However, Derby has a statement cache
so if the statement is lexically equal to a previous statement, the
result of the previous compilation will be reused, and recompilation
will not be needed.

--
Øystein

Re: Derby select query speed questions

Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Adam Bovill wrote:
 > Hi Olav,
 >
 > Thanks.  That seems to have improved things.
 >
 > I was wondering whether or not there was a way to create one 
PreparedStatement from another.  I have the first one that I've created 
and would like to clone or duplicate this, w/o needing to recompile it.
 >
 > So when I'm using a PreparedStatement, I can set the parameters w/o 
incurring too much of a penalty because it's already compiled?

In order to answer this it would be good to know a bit more about what
you are trying to achieve.  Why do need several prepared statements?
Note that a prepared statement is local to a connection.  If you want
to execute the same statement in another connection, you will have to
prepare it for that connection.  However, Derby has a statement cache
so if the statement is lexically equal to a previous statement, the
result of the previous compilation will be reused, and recompilation
will not be needed.

--
Øystein

RE: Derby select query speed questions

Posted by Adam Bovill <ab...@fnfr.com>.
Hi Olav,

Thanks.  That seems to have improved things. 

I was wondering whether or not there was a way to create one PreparedStatement from another.  I have the first one that I've created and would like to clone or duplicate this, w/o needing to recompile it.

So when I'm using a PreparedStatement, I can set the parameters w/o incurring too much of a penalty because it's already compiled?

Thanks,
Adam

-----Original Message-----
From: Olav Sandstå [mailto:olavsa@gmail.com] 
Sent: Thursday, April 12, 2007 2:27 PM
To: Derby Discussion
Subject: Re: Derby select query speed questions

On 12/04/07, Adam Bovill <ab...@fnfr.com> wrote:
> 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?

The reponse times you report is rather slow. Derby should use about 50
microseconds on these queries not 50 milliseconds. On a dual CPU
machine embedded Derby is able to execute about 20.000 queries like
these per second.

> 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 think a lot of this has to do with how you execute the queries:

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

Here it seems like you for every query you execute, you do:

   1. Create a new statement
   2. Execute a query by giving Derby a string containing the SQL
code. If this string differs between runs Derby will have to compile
this query every time you run you query (since you likely change the
value of the id = 19...)

Compiling queries in Derby is expensive.

Suggestions:

  1. Do not create a new statement for every query you run
  2. Use (and re-use) a preparedStatement for each of you query types.

For an example of what it cost to execute and compile a query compared
to executing a prepared statement see slide 22 in the following
presentation given at last years ApacheCon:

  http://home.online.no/~olmsan/publications/pres/apachecon06us/apachecon06.pdf

Hope this help, if not report back and request more advice.

Olav

Re: Derby select query speed questions

Posted by Olav Sandstå <ol...@gmail.com>.
On 12/04/07, Adam Bovill <ab...@fnfr.com> wrote:
> 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?

The reponse times you report is rather slow. Derby should use about 50
microseconds on these queries not 50 milliseconds. On a dual CPU
machine embedded Derby is able to execute about 20.000 queries like
these per second.

> 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 think a lot of this has to do with how you execute the queries:

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

Here it seems like you for every query you execute, you do:

   1. Create a new statement
   2. Execute a query by giving Derby a string containing the SQL
code. If this string differs between runs Derby will have to compile
this query every time you run you query (since you likely change the
value of the id = 19...)

Compiling queries in Derby is expensive.

Suggestions:

  1. Do not create a new statement for every query you run
  2. Use (and re-use) a preparedStatement for each of you query types.

For an example of what it cost to execute and compile a query compared
to executing a prepared statement see slide 22 in the following
presentation given at last years ApacheCon:

  http://home.online.no/~olmsan/publications/pres/apachecon06us/apachecon06.pdf

Hope this help, if not report back and request more advice.

Olav