db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From yves pielusenet <yvespieluse...@free.fr>
Subject Re: [SELECT][RESULTSET] How Derby deals with very large select ?
Date Fri, 31 Mar 2006 06:19:18 GMT
Le jeudi 30 mars 2006 à 13:19 -0800, Rajesh Kartha a écrit :
> yves piel wrote:
> 
> >Hello,
> >I have a table with more than 1,000,000 records. I have to iterate over
> >all records (from the first to the last) very quicly. What is the best
> >way ?
> >Is I do a naive 'select * from data' how long the ResultSet would be
> >created (30 seconds is the mx that I can spend) ?
> >
> >My table is defined like this :
> >
> >CREATE TABLE data (
> >	typecarnet VARCHAR(100) NOT NULL,
> >	instance INTEGER NOT NULL,
> >	numvign INTEGER NOT NULL,
> >	data BLOB NOT NULL
> >	PRIMARY KEY (typecarnet, instance, numvign)
> >)
> >
> >best regards,
> >
> >--
> >yves piel
> >
> >
> >  
> >
> I assume you are using Derby.
> For faster reads, forward-only cursor could be the solution. Did you try 
> using the ResultSet.TYPE_FORWARD_ONLY
> and ResultSet.TYPE_SCROLL_INSENSITIVE scrolling types ?
> 
> Example:
> conn=DriverManager.getConnection("jdbc:derby:testdb");
> stmt=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.TYPE_SCROLL_INSENSITIVE);
> rs=stmt.executeQuery("select * from data");
> 
> For more info,  check the Derby manuals or the wiki 
> (http://wiki.apache.org/db-derby/).
> 
> Please do send your findings to the list.
> 
> -Rajesh
> 
> 

ho yes ! it is possible so :)
However I want to be sure that all result data wouldn't be loaded into
memory (=> OutOfMemory).
This should be more faster that a :
for(int i=; i<allrows; i++){
   mypreparedstatement.setInt(1, i);
   ResultSet r = myPreparedstatement.execute();
}

I mistaked about my table structure into my previous post. THe table
structure is more simpler :
CREATE TABLE data(
   id INETEGER PRIMARY KEY,
   data BLOB
)


Mime
View raw message