为什么sql.rows Groovy方法太慢了 [英] Why sql.rows Groovy method is so slow
问题描述
我尝试使用sql.rows()Groovy方法获取一些数据,并且花了很长时间才返回值。
所以我尝试了标准的方式,它要快得多( 150倍)。
我缺少什么?
查看下面的代码:第一个方法返回结果大约2500毫秒,第二个方法返回15毫秒!
class MyService {
javax.sql.DataSource dataSource
$ b $ def SQL_QUERY =select M_FIRSTNAME as firstname,M_LASTNAME as lastname,M_NATIONALITY as country from CT_PLAYER order by M_ID asc;
$ b def getPlayers1(int offset,int maxRows)
{
def t = System.currentTimeMillis()
def sql = new Sql(dataSource)
def rows = sql.rows(SQL_QUERY,offset,maxRows)
printlntime1:$ {System.currentTimeMillis() - t}
返回行
}
def getPlayers2(int offset,int maxRows)
{
def t = System.currentTimeMillis();
连接连接= 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){
_list <<< [
'firstname':resultSet.getString('firstname'),
'lastname':resultSet.getString('lastname'),
'country':resultSet.getString('country' )
];
if(!resultSet.next())break;
$ b $ resultSet.close()
statement.close()
connection.close()
printlntime2:$ {System.currentTimeMillis ()-t}
return l_list
}
我想我发现这个方法很慢的原因: statement.setMaxRows()
永远不会被调用!
这意味着数据库发送了大量无用的数据(当您想查看大型数据网格的第一页时)
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
}
I think I found the reason this method is so slow : statement.setMaxRows()
is never called !
That means that a lot of useless data is sent by the database (when you want to see the first pages of a large datagrid)
这篇关于为什么sql.rows Groovy方法太慢了的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!