Re: Very slow

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

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

edson.richter.vcf
Description: Vcard