Why sql.rows Groovy method is so slow

Question

I tried to fetch some data with the sql.rows() Groovy method and it took a very long time to return the values.

So I tried the "standard" way and it's much much faster (150 times faster).

What am I missing ?

Look at the code below : the first method returns results in about 2500ms and the second in 15 ms !

class MyService {

javax.sql.DataSource dataSource

def SQL_QUERY = "select M_FIRSTNAME as firstname, M_LASTNAME as lastname, M_NATIONALITY as country from CT_PLAYER order by M_ID asc";

def getPlayers1(int offset, int maxRows)
{
    def t = System.currentTimeMillis()
    def sql = new Sql(dataSource)
    def rows = sql.rows(SQL_QUERY, offset, maxRows)
    println "time1 : ${System.currentTimeMillis()-t}"
    return rows
}

def getPlayers2(int offset, int maxRows) 
{
    def t = System.currentTimeMillis();
    Connection connection = dataSource.getConnection();
    Statement statement = connection.createStatement();
    statement.setMaxRows(offset + maxRows -1);
    ResultSet resultSet = statement.executeQuery(SQL_QUERY);
    def l_list =[];
    if(resultSet.absolute(offset)) {
        while (true) {
            l_list << [
                'firstname':resultSet.getString('firstname'),
                'lastname' :resultSet.getString('lastname'),
                'country'  :resultSet.getString('country')
            ];
            if(!resultSet.next()) break;
        }
    }
    resultSet.close()
    statement.close()
    connection.close()
    println "time2 : ${System.currentTimeMillis()-t}"
    return l_list
}

tim_yates tim_yates · Accepted Answer · 2012-03-29T11:15:38

When you call sql.rows, groovy eventually calls SqlGroovyMethods.toRowResult for each row returned by the resultSet.

This method interrogates the ResultSetMetaData for the resultSet each time to find the column names, and then fetches the data for each of these columns from the resultSet into a Map which it adds to the returned List.

In your second example, you directly get the columns required by name (as you know what they are), and avoid having to do this lookup every row.

Why sql.rows Groovy method is so slow

3 Answers