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 (hermes.apache.org: local policy)
Message-ID: <429F17D3.30904@sbcglobal.net>
Date: Thu, 02 Jun 2005 07:29:39 -0700
From: Jack Klebanoff <klebanoff-derby@sbcglobal.net>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
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>
 <429CC6B3.1020107@mgrinformatica.com.br> <429CCFE4.3070002@gmail.com>
 <429DD267.3060104@sbcglobal.net> <429DE1FC.4060002@gmail.com>
 <429DEE1B.4050205@mgrinformatica.com.br>
In-Reply-To: <429DEE1B.4050205@mgrinformatica.com.br>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Edson Carlos Ericksson Richter wrote:

> Thanks for fast response.
>
> I think the reason Derby didn't used any index  is because since this 
> table, for my tests purposes has only one record, I've not created any 
> index at all.
> I suspect that one record table will be in cache for a long time 
> (since it's a comment making queries over this table in my code). At 
> all, this one record is having "cost 13", so it's not the problem.
>
> My problem appear be in the sub-select. Each sub-select make queries 
> over a 33000 records table. I think I forgot to make a better index 
> for it. After indexing, performance got better (not perfect, but it's 
> usable - I think the large amout of log being generated is the reason 
> for the little delay in responses). The cost for sub-select query 
> dropped down from near 13000 to 19,85.
>
> I other databases (like MaxDB and MS SQL, this table was kept in 
> memory, because he is considered "small"). That's the reason I never 
> made any indexing over it.
>
> I'll try links sent at other mails, so I could discover if Tomcat 
> integration has it's secrets too.
>
> Thank you all for comments,
>
> Edson Richter
>
You might also try increasing the size of the Derby page cache. The 
default page cache size is 1000 pages at 4K bytes per page. The default 
page cache may not be large enough to hold the 33000 row table in 
addition to indexes on your other tables. You can set the page cache 
size using the derby.storage.pageCacheSize property. Normally you set it 
in a file named derby.properties located in the derby home directory. 
For example, to set the page cache size to10000 pages (40M bytes) add 
the line:

derby.storage.pageCacheSize=10000

to your derby.properties file.

See http://incubator.apache.org/derby/docs/tuning/ for more details 
about setting this and other Derby properties and for a larger 
discussion of Derby tuning.

Jack