Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <440F2CC9.4080102@sbcglobal.net>
Date: Wed, 08 Mar 2006 11:13:13 -0800
From: Mike Matrigali <mikem_app@sbcglobal.net>
Reply-To: mikem_app@sbcglobal.net
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Derby JDBC Embedded Driver, Prepared Statements, and Indexes
References: <004701c642dc$5c4d5c90$dd00a8c0@itsdev.infotechsoft.com>
In-Reply-To: <004701c642dc$5c4d5c90$dd00a8c0@itsdev.infotechsoft.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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);
> 
> 
> 
>