db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Edson Carlos Ericksson Richter <edson.rich...@mgrinformatica.com.br>
Subject Re: Very slow
Date Tue, 31 May 2005 19:33:48 GMT
Responses inline:

Sunitha Kambhampati escreveu:

> 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

No difference. May app is query intensive.

> 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

I believe it's prepared statements. I'm using JSTL, so I don't have much 
control about implementation.

> 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.

I'll try that.

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

can you be more clear? The CPU is a Athlon 1.4, 640Mb RAM with 22Mb/s 
ide hard disk.

> Sunitha.

Edson Carlos Ericksson Richter
MGR Informática Ltda.
Fones: 3347-0446 / 9259-2993

View raw message