db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sunitha Kambhampati <ksunitha...@gmail.com>
Subject Re: Very slow
Date Tue, 31 May 2005 19:31:15 GMT
Edson Carlos Ericksson Richter wrote:

> Hi!
>
> I'm trying to embed Derby into Tomcat web apps (using Embedded 
> driver). But I found it's very, very slow.
> My tables are not so big (majority have 10 or 20 records), and 2 have 
> many records (27000 in one, 436000 in other).
> I have same database running at full speed using MaxDB (SapAG/MySQL 
> product) and MS SQL Server (Microsoft product). I have all indexes 
> that someone could think (I've spent several hours tweaking for MaxDB 
> and MS SQL several time ago, and I've found what are best fields to be 
> indexed for my queries).
> If fields in where clauses are out of order in relation to indexes, 
> Derby is capable to reorganize query and use the index (like MaxDB, 
> PostgreSQL, MS SQL and other products)?
> Is there any tips for using derby inside Tomcat? Should I use it in 
> Server mode?
>
> Thanks for any tips,
>
Some things to check :
1) Check if you are running in autocommit mode set to true.  Inserts can 
be painfully slow in autocommit mode. The reason is that each commit 
involves a flush of the log to the disk for each insert statement. The 
commit will not return until a physical disk write has been executed

2) Are you using prepared statements with parameter markers (?)  , or 
are you using Statements
It is not a good idea to use Statement, use PreparedStatement instead. 
Using prepared statements instead of statements can help avoid 
unnecessary compilation which saves time.

So  statements like
insert into t values(1);
insert tinto t values(2)
.....
will involve compilation cost for each of the statements

but if you use PreparedStatement
insert into t values (?)

The statement will be compiled once and subsequent executions will save 
the compilation step. 

Also check the following links in the tuning manual:
http://incubator.apache.org/derby/manuals/tuning/perf21.html#HDRSII-PERF-18705 

http://incubator.apache.org/derby/manuals/tuning/perf34.html#IDX438

3) What queries are performing slow ?  If you set 
derby.language.logQueryPlan=true, this will print the query plans used 
for the queries in derby.log. This might help to check if the indexes 
are being used correctly or not.

4) Is your app cpu bound or i/o bound.

Sunitha.

Mime
View raw message