Why is sql.rows Groovy method so slow

Question

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

So, I tried the "standard" method and much faster ( 150 times faster ).

What am I missing?

Look at the following code: the first method returns results in about 2500 ms, and the second - after 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 } 

Answer 1

When you call sql.rows , groovy will end up calling SqlGroovyMethods.toRowResult for every row returned by the result set.

This method interrogates ResultSetMetaData for resultSet each time to find the column names, and then extracts the data for each of these columns from the result set into the 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 this search of every row.

Answer 2

I think I found the reason this method is so slow: statement.setMaxRows() never called!

This means that a lot of useless data is sent by the database (when you want to see the first pages of a large datagrid)

Answer 3

I wonder how your tests will turn out if you try using setFetchSize instead of setMaxRows. Many of them relate to the main behavior of the JDBC Driver.