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 (asf.osuosl.org: local policy)
Subject: Re: Iterate over big table...
From: yves pielusenet <yvespielusenet@free.fr>
To: Derby Discussion <derby-user@db.apache.org>
In-Reply-To: <446E26E3.3000104@gmail.com>
References: <1148045158.8944.10.camel@localhost.localdomain>
	 <446E26E3.3000104@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Date: Mon, 12 Jun 2006 16:18:07 +0200
Message-Id: <1150121887.6989.16.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

Le vendredi 19 mai 2006 � 13:13 -0700, Stanley Bradbury a �crit :
> yves pielusenet wrote:
> 
> >Hello,
> >I have such a table :
> >CREATE TABLE data (
> >   numvign INTEGER PRIMARY KEY,
> >   data BLOB NOT NULL
> >)
> >
> >This table should have millions of records. I have to iterate over all
> >records.
> >What is the best way (I don't want to have a outOfMemoryException) :
> >
> >for(int i=0; i<nbRows; i++){
> >    theData = select data from data where numvign=i;
> >    doSomething(theData);
> >}
> >
> >or :
> >
> >alldata = select data from data order by numvign;
> >while(alldata.hasNext()){
> >   doSomething(allData.next());
> >}
> >
> >When I do a big select, are all selected rows into memory or derby
> >access to database when it necessary ?
> >
> >thanks :)
> >
> >  
> >
> Hi -
> With the proper Derby pageCache and JVM maxHeap settings you should not 
> get OutOfMemory (OOM) exceptions when using Derby.  Determining the 
> exact, best setting requires iterative testing on your application using 
> different settings.  I've included guidelines below on how to balance 
> these factors for a system.  The default pageCache setting of Derby 
> works well for databases that do not make heavy use of LOB data (thus 
> utilizing many 32 K pages rather than the standard 4 K pages).  When you 
> select  from the above table you will be using all 32 K pages and should 
> compute the proper setting for pageCache and maxHeap to obtain a ratio 
> on 16:1 or higher.  I have not heard of OOM exceptions happening in 
> systems using a 20:1 ratio or higher.
> 
> Here's the basic information you need to know:
> Avoinding  OOM exceptions means balancing three things:
>      How much of the  physical memory of the machine you can allocate to 
> the JVM.
>           IMPORTANT: The JVM must fit in physical memory.
>      The maximum heap size allowed for the jvm (-Xmx )
>      The size of the Derby/Cloudscape pageCache (pageCache * avg. pageSize)
> 
> The JVM maximum Heap size (maxHeap) is set using the java parameter 
> "-Xmx#m" where # is the maximum number of Mb to allocate to the heap 
> (for example java -Xmx128m - for a 128 Mb maxHeap).
> The Derby pageCache size is set using the property 
> derby.storage.pageCacheSize. pageCache and is specified in database 
> pages (which can be of various sizes: 4K for standard tables, 32K for 
> tables with LOBs). 
> 
> The following sizing method computes the number of pages to allocate to 
> the pageCache as a ratio of the JVM maximum Heap size.  This ratio for a 
> default implementation of Derby is 16:1 (default sizes:  pageCache of 
> 1000 pages, only 4 K pages in the database running in a 64 Mb JVM) and 
> can be calculated using the following formula:  JVM maxHeap / ( (avg. 
> page size) * pageCache) / pageCache) [ 64M / ( (1000*4)/1000 ].  If you 
> are getting OOM exceptions in Derby it is likely that your 'avg. page 
> size' is greater than 4 K.
> 
> 
Hello,
I know it is a old message. I understand the aswer. I use this into my
application :

String pageSize = System.getProperty("derby.storage.pageSize", "16384");
System.getProperties().put("derby.storage.pageSize", pageSize);

String pageCacheSize = System.getProperty("derby.storage.pageCacheSize", "500");
System.getProperties().put("derby.storage.pageCacheSize", pageCacheSize);

String nbInitialPage = System.getProperty("derby.storage.initialPages", "100");
System.getProperties().put("derby.storage.initialPages", nbInitialPage);

It works well when I do lots of 'select' which returns me a single row.

As as say in my first post, I have to iterate aver a very big table
(millions of rows).
So can I do a 'select * from data order by index' over this big table ?
is it better than doing :
for(int id=1; id<nbrow; id++){
   ResultSet rs = select * from data where index=id
}
?

is the resultSet of jdbc derby use the pagecache ?

thanks :)

-- 
yves piel