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 "Thomas J. Taylor" <th...@itqa.miami.edu> on 2006/03/08 19:16:03 UTC
Derby JDBC Embedded Driver, Prepared Statements, and Indexes
Hi everyone,
I have a table with 400,000+ records in it and a foreign key (and index) to
a BIGINT value in another table. Through JDBC/Embedded Driver it takes about
2 seconds to retrieve a single value from the table using a simple SELECT
query as a PreparedStatement. I use a PreparedStatement as I call the query
up to 1,000 times, replacing the values of 'fk' and 'date_millis' during
each call.
Through SQurrieL, with the same database and Derby Embedded driver,
executing the query takes 15ms. If I drop the foreign key and index, it
takes 8862ms - both times using Derby runtime statistics.
Does anybody know of a reason that the PreparedStatement is always taking
~2sec to complete, while SQuirreL takes 15ms to complete a similar query?
Any help is appreciated.
Thomas
-- Example Table
CREATE TABLE MyTable (
dbid BIGINT IDENTITY GENERATED BY DEFAULT,
col1 VARCHAR(255),
col2 VARCHAR(255),
date_millis BIGINT NOT NULL,
fk BIGINT,
FOREIGN KEY fk REFERENCES AnotherTable (dbid)
);
-- Example Java Code
PreparedStatement pStmt = connection.prepareStatement("SELECT * from MyTable
WHERE fk = ? AND date_millis <= ? ORDER BY date_millis DESC");
..
ResultSet rs = pStmt.executeQuery();
-- Example SQuirreL SQL Code for stats
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
SELECT * from MyTable WHERE fk = 413689 AND date_millis <= 9999999999999999
ORDER BY date_millis DESC;
VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0);
RE: Derby JDBC Embedded Driver, Prepared Statements, and Indexes
Posted by "Thomas J. Taylor" <th...@itqa.miami.edu>.
Thanks for everybody's replies.
I managed to get someone to run DBLook on the database and found that the
foreign key (and index) does not exist in the database. After having him
create an index on FK, it solved the problem. But it is strange that the
foreign key wasn't present - as the SQL script that created the database
also created three other foreign keys on the same table (fk_1, fk_2, fk_3),
which are still present in the database.
Anyway, my specific problem is solved.
However, it's strange that this index is missing - first, because the person
accessing the database has been doing so since January without trouble until
a few days ago, and - second, because we have 10+ remote databases in
identical configurations that are functioning correctly (and have the
index).
Is there any reason or condition that might cause Derby to automatically
drop a foreign key or index? Or not create one on an empty table?
Thanks, again, for your feedback.
Thomas
In case you're still interested in a clarification of the Java code:
===================================================================
As for the performance measurements, the JDBC-referenced database is in a
location that I don't have access to, so all I had was application timing
information about when the query started in Java and when it completed in
Java. I also don't have access to the same data in the remote database, so
the queries in squirrel were against 'test' data that approximated the
content of the remote database (perhaps off by up to 100,000 records).
The Java code is basically this, where the time is based on start/end
method, and the method may be called 1,000 times - each time the method was
called, it took 2sec to complete (first time, last time, etc.)
PreparedStatement pStmt = null;
MyTableObject myMethod(int id, long millis) throws SQLException {
System.out.println("START:"+ new Date());
MyTableObject myObject = null;
If (pStmt==null)
pStmt = connection.prepareStatement("SELECT * from MyTable WHERE fk
= ? AND date_millis <= ? ORDER BY date_millis DESC");
pStmt.setMaxRows(1);
pStmt.setInt(1, getSomeNumber());
pStmt.setLong(2, getSomeBigNumber());
ResultSet rs = pStmt.executeQuery();
if (rs.next()) {
myObject = new MyTableObject();
myObject.setXXX( rs.getXXX(1) );
...
}
rs.close();
System.out.println("END:"+new Date());
return myObject;
}
-----Original Message-----
From: Kristian Waagan [mailto:Kristian.Waagan@Sun.COM]
Sent: Wednesday, March 08, 2006 4:52 PM
To: Derby Discussion
Subject: Re: Derby JDBC Embedded Driver, Prepared Statements, and Indexes
Michael Segel wrote:
>I have to agree with Mike that there is something missing.
>
>First, how often are you preparing the statement?
>You should only be preparing it once and within the look, set the
>variables and then execute it your 1000 times.
>
>
Hi Michael,
The way I interpret Thomas' mail, I think the paragraph above can be
misunderstood. The variables, assuming these refer to the question marks in
the SQL prepared statement, must also be set 1000 times. Thomas, if I'm
wrong, please correct me!
In code (parts from Thomas' mail):
-- Example Java Code - NB! No exception handling in the example code!!!!
PreparedStatement pStmt = connection.prepareStatement("SELECT * from MyTable
WHERE fk = ? AND date_millis <= ? ORDER BY date_millis DESC"); ResultSet rs
= null; // Execute the prepared statement 1000 times.
for (int i=0; i < 1000; i++) {
pStmt.setInt(1, getSomeNumber());
pStmt.setLong(2, getSomeBigNumber());
rs = pStmt.executeQuery();
while (rs.next()) {
// Do something with the result.
}
rs.close();
}
pStmt.close();
It is not quite clear to me from the mail if the prepared statement is
called in such a tight loop as above. If not, it is still important to only
prepare the statement once as Michael says, and only set the variables and
call one of the execute methods for each time. Also, closing database
resources when they are not going to be used any more is recommended to free
up resources in the database.
--
Kristian
>You may see 2 seconds on the first iteration, but after that, it should
>be very fast.
>
>>From your code example, it looks like you're calling the query once and
>then running through the result set. Is that what you're timing?
>
>Sorry, perhaps I'm running slow today... (late night drinking with some
>of my wife's co-workers) but I'm not sure I'm following you.
>
>Is the goal to prepare a statement, then time the amount of time
>required to run through the query's result set?
>
>Or are you preparing a statement, to be used 1000 times and you're
>timing the execution of the prepared statement?
>
>That's the first issue.
>
>The second issue, that Mike points out is that you may be comparing
>apples to oranges depending on how you reset your test environment
>between querries.
>
>So can we start again, with some more background detail?
>
>Danke!
>
>-Mikey
>
>
>
>
>>-----Original Message-----
>>From: Mike Matrigali [mailto:mikem_app@sbcglobal.net]
>>Sent: Wednesday, March 08, 2006 1:13 PM
>>To: Derby Discussion
>>Subject: Re: Derby JDBC Embedded Driver, Prepared Statements, and
>>Indexes
>>
>>I believe that no index is actually created on MyTable, only on
>>AnotherTable. From the one query below it seems like you want an
>>index on (fk asc, date_millis desc). But if you run other queries
>>then other indexs may make more sense.
>>
>>I am not sure exactly what is going on with your performance
>>measurements, it has the feel of comparing compile time vs execute
>>time and/or cached table data vs uncached table data.
>>
>>Could you explain exactly how you get your performance measurement, it
>>is clear below with the SQuirreL example but not with the java code.
>>The results have the feel of comparing compile time vs. execution time
>>of a prepared statement and/or cached db vs. not cached db. Note that
>>once a query is compiled it is cached, so you may be saving compile in
>>squirrel without knowing it if you are leaving the db booted and the
>>query was sometime compiled what squirrel.
>>
>>Thomas J. Taylor wrote:
>>
>>
>>>Hi everyone,
>>>
>>>I have a table with 400,000+ records in it and a foreign key (and
>>>index)
>>>
>>>
>>to
>>
>>
>>>a BIGINT value in another table. Through JDBC/Embedded Driver it
>>>takes
>>>
>>>
>>about
>>
>>
>>>2 seconds to retrieve a single value from the table using a simple
>>>
>>>
>>SELECT
>>
>>
>>>query as a PreparedStatement. I use a PreparedStatement as I call the
>>>
>>>
>>query
>>
>>
>>>up to 1,000 times, replacing the values of 'fk' and 'date_millis'
>>>during each call.
>>>
>>>Through SQurrieL, with the same database and Derby Embedded driver,
>>>executing the query takes 15ms. If I drop the foreign key and index,
>>>it takes 8862ms - both times using Derby runtime statistics.
>>>
>>>Does anybody know of a reason that the PreparedStatement is always
>>>
>>>
>>taking
>>
>>
>>>~2sec to complete, while SQuirreL takes 15ms to complete a similar
>>>
>>>
>>query?
>>
>>
>>>Any help is appreciated.
>>>
>>>Thomas
>>>
>>>
>>>-- Example Table
>>>CREATE TABLE MyTable (
>>> dbid BIGINT IDENTITY GENERATED BY DEFAULT,
>>> col1 VARCHAR(255),
>>> col2 VARCHAR(255),
>>> date_millis BIGINT NOT NULL,
>>> fk BIGINT,
>>> FOREIGN KEY fk REFERENCES AnotherTable (dbid) );
>>>
>>>-- Example Java Code
>>>PreparedStatement pStmt = connection.prepareStatement("SELECT * from
>>>
>>>
>>MyTable
>>
>>
>>>WHERE fk = ? AND date_millis <= ? ORDER BY date_millis DESC"); ..
>>>ResultSet rs = pStmt.executeQuery();
>>>
>>>-- Example SQuirreL SQL Code for stats CALL
>>>SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
>>>CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
>>>
>>>SELECT * from MyTable WHERE fk = 413689 AND date_millis <=
>>>
>>>
>>9999999999999999
>>
>>
>>>ORDER BY date_millis DESC;
>>>
>>>VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>>>CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
>>>CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0);
>>>
>>>
>>>
>>>
>>>
>>>
>
>
>
>
>
RE: Derby JDBC Embedded Driver, Prepared Statements, and Indexes
Posted by Michael Segel <ms...@segel.com>.
> -----Original Message-----
> From: Kristian Waagan [mailto:Kristian.Waagan@Sun.COM]
> Sent: Wednesday, March 08, 2006 3:52 PM
> To: Derby Discussion
> Subject: Re: Derby JDBC Embedded Driver, Prepared Statements, and Indexes
>
> Michael Segel wrote:
>
> >I have to agree with Mike that there is something missing.
> >
> >First, how often are you preparing the statement?
> >You should only be preparing it once and within the look, set the
> variables
> >and then execute it your 1000 times.
> >
> >
>
> Hi Michael,
>
> The way I interpret Thomas' mail, I think the paragraph above can be
> misunderstood. The variables, assuming these refer to the question marks
> in the SQL prepared statement, must also be set 1000 times. Thomas, if
> I'm wrong, please correct me!
[mjs]
Yes. That's the point I'm trying to make.
You prepare the statement once, then in your loop you set your variables and
then execute the statement.
On this list, I've seen some questionable code.
So its not out of the realm that someone is mistakenly preparing the
statement within the loop.
Using the setXXX() methods, does not require a lot of overhead.
Which is why I was asking about how often he's preparing the statement.
But lets look at the code fragment....
> In code (parts from Thomas' mail):
>
> -- Example Java Code - NB! No exception handling in the example code!!!!
> PreparedStatement pStmt = connection.prepareStatement("SELECT * from
> MyTable
> WHERE fk = ? AND date_millis <= ? ORDER BY date_millis DESC");
> ResultSet rs = null;
> // Execute the prepared statement 1000 times.
> for (int i=0; i < 1000; i++) {
> pStmt.setInt(1, getSomeNumber());
> pStmt.setLong(2, getSomeBigNumber());
> rs = pStmt.executeQuery();
> while (rs.next()) {
> // Do something with the result.
> }
> rs.close();
> }
> pStmt.close();
[mjs]
Ok, this is fine, except that his 2 seconds timing doesn't make sense.
So something is missing....
Again the other point I tried to make is that we don't know what he did in
between the tests. Or had he repeated the test over several iterations and
then average them.
(Which actually goes to Mike M's point about caching...)
>
>
> It is not quite clear to me from the mail if the prepared statement is
> called in such a tight loop as above. If not, it is still important to
> only prepare the statement once as Michael says, and only set the
> variables and call one of the execute methods for each time. Also,
> closing database resources when they are not going to be used any more
> is recommended to free up resources in the database.
>
>
>
[mjs] Yeah. And that's why I'm confused. If you're working with a tight
loop, I can imagine taking a hit once on the first query (iteration). But
then it should run fine.
> --
> Kristian
>
> >You may see 2 seconds on the first iteration, but after that, it should
> be
> >very fast.
> >
> >From your code example, it looks like you're calling the query once and
> then
> >running through the result set. Is that what you're timing?
> >
> >Sorry, perhaps I'm running slow today... (late night drinking with some
> of
> >my wife's co-workers) but I'm not sure I'm following you.
> >
> >Is the goal to prepare a statement, then time the amount of time required
> to
> >run through the query's result set?
> >
> >Or are you preparing a statement, to be used 1000 times and you're timing
> >the execution of the prepared statement?
> >
> >That's the first issue.
> >
> >The second issue, that Mike points out is that you may be comparing
> apples
> >to oranges depending on how you reset your test environment between
> >querries.
> >
> >So can we start again, with some more background detail?
> >
> >Danke!
> >
> >-Mikey
> >
> >
> >
> >
> >>-----Original Message-----
> >>From: Mike Matrigali [mailto:mikem_app@sbcglobal.net]
> >>Sent: Wednesday, March 08, 2006 1:13 PM
> >>To: Derby Discussion
> >>Subject: Re: Derby JDBC Embedded Driver, Prepared Statements, and
> Indexes
> >>
> >>I believe that no index is actually created on MyTable, only on
> >>AnotherTable. From the one query below it seems like you want
> >>an index on (fk asc, date_millis desc). But if you run other
> >>queries then other indexs may make more sense.
> >>
> >>I am not sure exactly what is going on with your performance
> >>measurements, it has the feel of comparing compile time vs execute
> >>time and/or cached table data vs uncached table data.
> >>
> >>Could you explain exactly how you get your performance measurement, it
> >>is clear below with the SQuirreL example but not with the java code.
> The
> >>results have the feel of comparing compile time vs. execution time of
> >>a prepared statement and/or cached db vs. not cached db. Note that once
> >>a query is compiled it is cached,
> >>so you may be saving compile in squirrel without knowing it if you are
> >>leaving the db booted and the query was sometime compiled what squirrel.
> >>
> >>Thomas J. Taylor wrote:
> >>
> >>
> >>>Hi everyone,
> >>>
> >>>I have a table with 400,000+ records in it and a foreign key (and
> index)
> >>>
> >>>
> >>to
> >>
> >>
> >>>a BIGINT value in another table. Through JDBC/Embedded Driver it takes
> >>>
> >>>
> >>about
> >>
> >>
> >>>2 seconds to retrieve a single value from the table using a simple
> >>>
> >>>
> >>SELECT
> >>
> >>
> >>>query as a PreparedStatement. I use a PreparedStatement as I call the
> >>>
> >>>
> >>query
> >>
> >>
> >>>up to 1,000 times, replacing the values of 'fk' and 'date_millis'
> during
> >>>each call.
> >>>
> >>>Through SQurrieL, with the same database and Derby Embedded driver,
> >>>executing the query takes 15ms. If I drop the foreign key and index, it
> >>>takes 8862ms - both times using Derby runtime statistics.
> >>>
> >>>Does anybody know of a reason that the PreparedStatement is always
> >>>
> >>>
> >>taking
> >>
> >>
> >>>~2sec to complete, while SQuirreL takes 15ms to complete a similar
> >>>
> >>>
> >>query?
> >>
> >>
> >>>Any help is appreciated.
> >>>
> >>>Thomas
> >>>
> >>>
> >>>-- Example Table
> >>>CREATE TABLE MyTable (
> >>> dbid BIGINT IDENTITY GENERATED BY DEFAULT,
> >>> col1 VARCHAR(255),
> >>> col2 VARCHAR(255),
> >>> date_millis BIGINT NOT NULL,
> >>> fk BIGINT,
> >>> FOREIGN KEY fk REFERENCES AnotherTable (dbid)
> >>>);
> >>>
> >>>-- Example Java Code
> >>>PreparedStatement pStmt = connection.prepareStatement("SELECT * from
> >>>
> >>>
> >>MyTable
> >>
> >>
> >>>WHERE fk = ? AND date_millis <= ? ORDER BY date_millis DESC");
> >>>..
> >>>ResultSet rs = pStmt.executeQuery();
> >>>
> >>>-- Example SQuirreL SQL Code for stats
> >>>CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> >>>CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
> >>>
> >>>SELECT * from MyTable WHERE fk = 413689 AND date_millis <=
> >>>
> >>>
> >>9999999999999999
> >>
> >>
> >>>ORDER BY date_millis DESC;
> >>>
> >>>VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> >>>CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
> >>>CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0);
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >
> >
> >
> >
> >
Re: Derby JDBC Embedded Driver, Prepared Statements, and Indexes
Posted by Kristian Waagan <Kr...@Sun.COM>.
Michael Segel wrote:
>I have to agree with Mike that there is something missing.
>
>First, how often are you preparing the statement?
>You should only be preparing it once and within the look, set the variables
>and then execute it your 1000 times.
>
>
Hi Michael,
The way I interpret Thomas' mail, I think the paragraph above can be
misunderstood. The variables, assuming these refer to the question marks
in the SQL prepared statement, must also be set 1000 times. Thomas, if
I'm wrong, please correct me!
In code (parts from Thomas' mail):
-- Example Java Code - NB! No exception handling in the example code!!!!
PreparedStatement pStmt = connection.prepareStatement("SELECT * from MyTable
WHERE fk = ? AND date_millis <= ? ORDER BY date_millis DESC");
ResultSet rs = null;
// Execute the prepared statement 1000 times.
for (int i=0; i < 1000; i++) {
pStmt.setInt(1, getSomeNumber());
pStmt.setLong(2, getSomeBigNumber());
rs = pStmt.executeQuery();
while (rs.next()) {
// Do something with the result.
}
rs.close();
}
pStmt.close();
It is not quite clear to me from the mail if the prepared statement is
called in such a tight loop as above. If not, it is still important to
only prepare the statement once as Michael says, and only set the
variables and call one of the execute methods for each time. Also,
closing database resources when they are not going to be used any more
is recommended to free up resources in the database.
--
Kristian
>You may see 2 seconds on the first iteration, but after that, it should be
>very fast.
>
>>From your code example, it looks like you're calling the query once and then
>running through the result set. Is that what you're timing?
>
>Sorry, perhaps I'm running slow today... (late night drinking with some of
>my wife's co-workers) but I'm not sure I'm following you.
>
>Is the goal to prepare a statement, then time the amount of time required to
>run through the query's result set?
>
>Or are you preparing a statement, to be used 1000 times and you're timing
>the execution of the prepared statement?
>
>That's the first issue.
>
>The second issue, that Mike points out is that you may be comparing apples
>to oranges depending on how you reset your test environment between
>querries.
>
>So can we start again, with some more background detail?
>
>Danke!
>
>-Mikey
>
>
>
>
>>-----Original Message-----
>>From: Mike Matrigali [mailto:mikem_app@sbcglobal.net]
>>Sent: Wednesday, March 08, 2006 1:13 PM
>>To: Derby Discussion
>>Subject: Re: Derby JDBC Embedded Driver, Prepared Statements, and Indexes
>>
>>I believe that no index is actually created on MyTable, only on
>>AnotherTable. From the one query below it seems like you want
>>an index on (fk asc, date_millis desc). But if you run other
>>queries then other indexs may make more sense.
>>
>>I am not sure exactly what is going on with your performance
>>measurements, it has the feel of comparing compile time vs execute
>>time and/or cached table data vs uncached table data.
>>
>>Could you explain exactly how you get your performance measurement, it
>>is clear below with the SQuirreL example but not with the java code. The
>>results have the feel of comparing compile time vs. execution time of
>>a prepared statement and/or cached db vs. not cached db. Note that once
>>a query is compiled it is cached,
>>so you may be saving compile in squirrel without knowing it if you are
>>leaving the db booted and the query was sometime compiled what squirrel.
>>
>>Thomas J. Taylor wrote:
>>
>>
>>>Hi everyone,
>>>
>>>I have a table with 400,000+ records in it and a foreign key (and index)
>>>
>>>
>>to
>>
>>
>>>a BIGINT value in another table. Through JDBC/Embedded Driver it takes
>>>
>>>
>>about
>>
>>
>>>2 seconds to retrieve a single value from the table using a simple
>>>
>>>
>>SELECT
>>
>>
>>>query as a PreparedStatement. I use a PreparedStatement as I call the
>>>
>>>
>>query
>>
>>
>>>up to 1,000 times, replacing the values of 'fk' and 'date_millis' during
>>>each call.
>>>
>>>Through SQurrieL, with the same database and Derby Embedded driver,
>>>executing the query takes 15ms. If I drop the foreign key and index, it
>>>takes 8862ms - both times using Derby runtime statistics.
>>>
>>>Does anybody know of a reason that the PreparedStatement is always
>>>
>>>
>>taking
>>
>>
>>>~2sec to complete, while SQuirreL takes 15ms to complete a similar
>>>
>>>
>>query?
>>
>>
>>>Any help is appreciated.
>>>
>>>Thomas
>>>
>>>
>>>-- Example Table
>>>CREATE TABLE MyTable (
>>> dbid BIGINT IDENTITY GENERATED BY DEFAULT,
>>> col1 VARCHAR(255),
>>> col2 VARCHAR(255),
>>> date_millis BIGINT NOT NULL,
>>> fk BIGINT,
>>> FOREIGN KEY fk REFERENCES AnotherTable (dbid)
>>>);
>>>
>>>-- Example Java Code
>>>PreparedStatement pStmt = connection.prepareStatement("SELECT * from
>>>
>>>
>>MyTable
>>
>>
>>>WHERE fk = ? AND date_millis <= ? ORDER BY date_millis DESC");
>>>..
>>>ResultSet rs = pStmt.executeQuery();
>>>
>>>-- Example SQuirreL SQL Code for stats
>>>CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
>>>CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
>>>
>>>SELECT * from MyTable WHERE fk = 413689 AND date_millis <=
>>>
>>>
>>9999999999999999
>>
>>
>>>ORDER BY date_millis DESC;
>>>
>>>VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
>>>CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
>>>CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0);
>>>
>>>
>>>
>>>
>>>
>>>
>
>
>
>
>
RE: Derby JDBC Embedded Driver, Prepared Statements, and Indexes
Posted by Michael Segel <ms...@segel.com>.
I have to agree with Mike that there is something missing.
First, how often are you preparing the statement?
You should only be preparing it once and within the look, set the variables
and then execute it your 1000 times.
You may see 2 seconds on the first iteration, but after that, it should be
very fast.
>From your code example, it looks like you're calling the query once and then
running through the result set. Is that what you're timing?
Sorry, perhaps I'm running slow today... (late night drinking with some of
my wife's co-workers) but I'm not sure I'm following you.
Is the goal to prepare a statement, then time the amount of time required to
run through the query's result set?
Or are you preparing a statement, to be used 1000 times and you're timing
the execution of the prepared statement?
That's the first issue.
The second issue, that Mike points out is that you may be comparing apples
to oranges depending on how you reset your test environment between
querries.
So can we start again, with some more background detail?
Danke!
-Mikey
> -----Original Message-----
> From: Mike Matrigali [mailto:mikem_app@sbcglobal.net]
> Sent: Wednesday, March 08, 2006 1:13 PM
> To: Derby Discussion
> Subject: Re: Derby JDBC Embedded Driver, Prepared Statements, and Indexes
>
> I believe that no index is actually created on MyTable, only on
> AnotherTable. From the one query below it seems like you want
> an index on (fk asc, date_millis desc). But if you run other
> queries then other indexs may make more sense.
>
> I am not sure exactly what is going on with your performance
> measurements, it has the feel of comparing compile time vs execute
> time and/or cached table data vs uncached table data.
>
> Could you explain exactly how you get your performance measurement, it
> is clear below with the SQuirreL example but not with the java code. The
> results have the feel of comparing compile time vs. execution time of
> a prepared statement and/or cached db vs. not cached db. Note that once
> a query is compiled it is cached,
> so you may be saving compile in squirrel without knowing it if you are
> leaving the db booted and the query was sometime compiled what squirrel.
>
> Thomas J. Taylor wrote:
> > Hi everyone,
> >
> > I have a table with 400,000+ records in it and a foreign key (and index)
> to
> > a BIGINT value in another table. Through JDBC/Embedded Driver it takes
> about
> > 2 seconds to retrieve a single value from the table using a simple
> SELECT
> > query as a PreparedStatement. I use a PreparedStatement as I call the
> query
> > up to 1,000 times, replacing the values of 'fk' and 'date_millis' during
> > each call.
> >
> > Through SQurrieL, with the same database and Derby Embedded driver,
> > executing the query takes 15ms. If I drop the foreign key and index, it
> > takes 8862ms - both times using Derby runtime statistics.
> >
> > Does anybody know of a reason that the PreparedStatement is always
> taking
> > ~2sec to complete, while SQuirreL takes 15ms to complete a similar
> query?
> >
> > Any help is appreciated.
> >
> > Thomas
> >
> >
> > -- Example Table
> > CREATE TABLE MyTable (
> > dbid BIGINT IDENTITY GENERATED BY DEFAULT,
> > col1 VARCHAR(255),
> > col2 VARCHAR(255),
> > date_millis BIGINT NOT NULL,
> > fk BIGINT,
> > FOREIGN KEY fk REFERENCES AnotherTable (dbid)
> > );
> >
> > -- Example Java Code
> > PreparedStatement pStmt = connection.prepareStatement("SELECT * from
> MyTable
> > WHERE fk = ? AND date_millis <= ? ORDER BY date_millis DESC");
> > ..
> > ResultSet rs = pStmt.executeQuery();
> >
> > -- Example SQuirreL SQL Code for stats
> > CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> > CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
> >
> > SELECT * from MyTable WHERE fk = 413689 AND date_millis <=
> 9999999999999999
> > ORDER BY date_millis DESC;
> >
> > VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> > CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
> > CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0);
> >
> >
> >
> >
Re: Derby JDBC Embedded Driver, Prepared Statements, and Indexes
Posted by Mike Matrigali <mi...@sbcglobal.net>.
I believe that no index is actually created on MyTable, only on
AnotherTable. From the one query below it seems like you want
an index on (fk asc, date_millis desc). But if you run other
queries then other indexs may make more sense.
I am not sure exactly what is going on with your performance
measurements, it has the feel of comparing compile time vs execute
time and/or cached table data vs uncached table data.
Could you explain exactly how you get your performance measurement, it
is clear below with the SQuirreL example but not with the java code. The
results have the feel of comparing compile time vs. execution time of
a prepared statement and/or cached db vs. not cached db. Note that once
a query is compiled it is cached,
so you may be saving compile in squirrel without knowing it if you are
leaving the db booted and the query was sometime compiled what squirrel.
Thomas J. Taylor wrote:
> Hi everyone,
>
> I have a table with 400,000+ records in it and a foreign key (and index) to
> a BIGINT value in another table. Through JDBC/Embedded Driver it takes about
> 2 seconds to retrieve a single value from the table using a simple SELECT
> query as a PreparedStatement. I use a PreparedStatement as I call the query
> up to 1,000 times, replacing the values of 'fk' and 'date_millis' during
> each call.
>
> Through SQurrieL, with the same database and Derby Embedded driver,
> executing the query takes 15ms. If I drop the foreign key and index, it
> takes 8862ms - both times using Derby runtime statistics.
>
> Does anybody know of a reason that the PreparedStatement is always taking
> ~2sec to complete, while SQuirreL takes 15ms to complete a similar query?
>
> Any help is appreciated.
>
> Thomas
>
>
> -- Example Table
> CREATE TABLE MyTable (
> dbid BIGINT IDENTITY GENERATED BY DEFAULT,
> col1 VARCHAR(255),
> col2 VARCHAR(255),
> date_millis BIGINT NOT NULL,
> fk BIGINT,
> FOREIGN KEY fk REFERENCES AnotherTable (dbid)
> );
>
> -- Example Java Code
> PreparedStatement pStmt = connection.prepareStatement("SELECT * from MyTable
> WHERE fk = ? AND date_millis <= ? ORDER BY date_millis DESC");
> ..
> ResultSet rs = pStmt.executeQuery();
>
> -- Example SQuirreL SQL Code for stats
> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
>
> SELECT * from MyTable WHERE fk = 413689 AND date_millis <= 9999999999999999
> ORDER BY date_millis DESC;
>
> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0);
>
>
>
>