Groovy sql dataset causes java.lang.OutOfMemory

Question

I have a table with 252759 tuples. I would like to use DataSet object to make my life easier, however when I try to create a DataSet for my table, after 3 seconds, I get java.lang.OutOfMemory.

I have no experience with Datasets, are there any guidelines how to use DataSet object for big tables?

Answer 1

Do you really need to retrieve all the rows at once? If not, then you could just retrieve them in batches of (for example) 10000 using the approach shown below.

def db = [url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbcDriver']

def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
String query = "SELECT * FROM my_table WHERE id > ? ORDER BY id limit 10000"

Integer maxId = 0

// Closure that executes the query and returns true if some rows were processed
Closure executeQuery = {

    def oldMaxId = maxId 
    sql.eachRow(query, [maxId]) { row ->

         // Code to process each row goes here.....
         maxId = row.id
    }
    return maxId != oldMaxId 
}


while (executeQuery());

AFAIK limit is a MySQL-specific feature, but most other RDBMS have an equivalent feature that limits the number of rows returned by a query.

Also, I haven't tested (or even compiled) the code above, so handle with care!

Answer 2

Why not start with giving the JVM more memory?

java -Xms<initial heap size> -Xmx<maximum heap size>

252759 tuples doesn't sound like anything a maching with 4GB RAM + some virtual memory couldn't handle in memory.