db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas J. Taylor" <thomas.tay...@itqa.miami.edu>
Subject Derby JDBC Embedded Driver, Prepared Statements, and Indexes
Date Wed, 08 Mar 2006 18:16:03 GMT
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);



Mime
View raw message