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