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: pass (hermes.apache.org: local policy)
Message-ID: <429CC6B3.1020107@mgrinformatica.com.br>
Date: Tue, 31 May 2005 17:18:59 -0300
From: Edson Carlos Ericksson Richter <edson.richter@mgrinformatica.com.br>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Very slow
References: <429CB54A.70902@mgrinformatica.com.br>
 <429CBB83.8080701@gmail.com> <429CBC1C.4080109@mgrinformatica.com.br>
In-Reply-To: <429CBC1C.4080109@mgrinformatica.com.br>
Content-Type: multipart/mixed;
 boundary="------------010609020307030104020804"

This is a multi-part message in MIME format.
--------------010609020307030104020804
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

I've run with derby.language.logQueryPlan=true and result is that I have 
table scan for only one table, that has actually one record.
The most costing queries are sub-selects (cost 13525) inside a almost 
large query, but everything is being run either using indexes (cost 
between 13 and 25) or hashed joins (throught PK, with cost between 50 
and 80).

Richter.


Edson Carlos Ericksson Richter escreveu:

> 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


--------------010609020307030104020804
Content-Type: text/x-vcard; charset=utf-8;
 name="edson.richter.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="edson.richter.vcf"

begin:vcard
fn:Edson Carlos Ericksson Richter
n:Richter;Edson
org;quoted-printable:MGR Inform=C3=A1tica Ltda;Desenvolvimento
adr:Cristo Redentor;;Assis Brasil, 3257, Sala 409;Porto Alegre;RS;91010007;Brasil
email;internet:edson.richter@mgrinformatica.com.br
title:Diretor de Sistemas
tel;work:(51)3347-0446
tel;cell:(51)9259-2993
x-mozilla-html:FALSE
url:http://www.mgrinformatica.com.br
version:2.1
end:vcard


--------------010609020307030104020804--