Java Spring jdbcTemplate queryForList 慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18078781/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java Spring jdbcTemplate queryForList slow
提问by Caballero
I'm using Spring jdbcTemplate to run MySQL queries in my java application. This is my function:
我正在使用 Spring jdbcTemplate 在我的 Java 应用程序中运行 MySQL 查询。这是我的功能：
public static ArrayList<Map<String, Object>> query(String q) throws Exception {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
@SuppressWarnings("unchecked")
List<Map<String, Object>> result = jdbcTemplate.queryForList(q);
return (ArrayList<Map<String, Object>>) result;
}
In this particular case, I pass the function this query:
在这种特殊情况下，我将这个查询传递给函数：
"SELECT * FROM test WHERE name IN('string1', 'string2', 'string3', ...)";
Table testonly has 6 columns, strings in INvary from few to 100 symbols. jdbcTemplate.queryForList()takes 280 milisecondsto run the query. NOTE: it takes 280 miliseconds for jdbcTemplate.queryForList()to run, not the whole function query. When I run exactly the same query on MySQL client (HeidiSQL) it only takes 16 miliseconds. UPDATE:Heidi maintains open connection, so that is not a fair comparison.
表test只有 6 列，字符串IN从几个符号到 100 个符号不等。运行查询jdbcTemplate.queryForList()需要280 毫秒。注意：jdbcTemplate.queryForList()运行需要 280 毫秒，而不是整个函数query。当我在 MySQL 客户端 (HeidiSQL) 上运行完全相同的查询时，它只需要16 毫秒。更新：海蒂保持开放连接，所以这不是一个公平的比较。
Question: why is jdbcTemplate.queryForList()so miserably slow in comparison to the same query ran on MySQL client? I'm using it purely for convienence reasons, it can retrieve result in ArrayList<Map<String, Object>>which is what I need. Is there anything I can do to speed it up or should I just drop jdbcTemplateall together and use something else?
问题：jdbcTemplate.queryForList()与在 MySQL 客户端上运行的相同查询相比，为什么如此缓慢？我纯粹是出于方便的原因使用它，它可以检索ArrayList<Map<String, Object>>我需要的结果。有什么我可以做的来加速它还是我应该把jdbcTemplate所有东西放在一起使用其他东西？
UPDATE:OK, so I've changed my function from jdbcTemplate to using plain jdbc. This is how that abomination looks like. It's the reason why I went with jdbcTemplate in the first place. It now takes 200 milisecondsto run the same query - slight improvement, but still too slow...
更新：好的，所以我已经将我的函数从 jdbcTemplate 更改为使用普通 jdbc。这就是那个可憎的样子。这就是我首先使用 jdbcTemplate 的原因。现在运行相同的查询需要200 毫秒- 略有改进，但仍然太慢......
public static ArrayList<Map<String,Object>> query(String Full_Command) {
try {
String URL = "jdbc:mysql://localhost/dbname";
String USER = "root";
String PASS = "";
java.sql.Connection Con = DriverManager.getConnection(URL, USER, PASS);
//create statement
Statement Stm = null;
//Stm = Con.createStatement();
Stm = (Statement) Con.createStatement();
//query
ResultSet Result = null;
boolean Returning_Rows = Stm.execute(Full_Command);
if (Returning_Rows) {
Result = Stm.getResultSet();
} else {
return new ArrayList<Map<String,Object>>();
}
//get metadata
ResultSetMetaData Meta = null;
Meta = Result.getMetaData();
//get column names
int Col_Count = Meta.getColumnCount();
ArrayList<String> Cols = new ArrayList<String>();
for (int Index=1; Index<=Col_Count; Index++) {
Cols.add(Meta.getColumnName(Index));
}
//fetch out rows
ArrayList<Map<String, Object>> Rows = new ArrayList<Map<String,Object>>();
while (Result.next()) {
HashMap<String,Object> Row = new HashMap<String,Object>();
for (String Col_Name:Cols) {
Object Val = Result.getObject(Col_Name);
Row.put(Col_Name,Val);
}
Rows.add(Row);
}
//close statement
Stm.close();
//pass back rows
return Rows;
} catch (Exception Ex) {
System.out.print(Ex.getMessage());
return new ArrayList<Map<String,Object>>();
}
}
UPDATE2:I have not broken down JDBC function into execution times, and this single line is the bottleneck, taking ~190 miliseconds every time:
UPDATE2：我没有将 JDBC 函数分解为执行时间，这一行是瓶颈，每次大约需要 190 毫秒：
java.sql.Connection Con = DriverManager.getConnection(URL, USER, PASS);
Any comments?
任何意见？
采纳答案by ktm5124
It's not JdbcTemplate's fault. Here's why:
这不是 JdbcTemplate 的错。原因如下：
First of all, you have to discount the overhead in establishing a connection. This overhead can be as much as 500 milliseconds. This is normal.
首先，您必须降低建立连接的开销。此开销可能高达 500 毫秒。这是正常的。
Second of all, when you run the query on MySQL client, are you running it immediately after you ran your program? Because queries get cached... even at the level of disk I/O. So if you run the same query twice, the second time it will run faster due to caching.
其次，当您在 MySQL 客户端上运行查询时，您是否在运行程序后立即运行它？因为查询会被缓存......即使在磁盘 I/O 级别也是如此。因此，如果您两次运行相同的查询，由于缓存，第二次它会运行得更快。
Third of all, are you indexing the column name?
第三，你在索引列name吗？
Fourth of all, if the connection overhead really matters that much to you, you could consider conserving a connection or even connection pooling.
第四，如果连接开销对您来说真的很重要，您可以考虑保留连接甚至连接池。
回答by Evgeniy Dorofeev
Try to increase fetch size (JdbcTemplate.setFetchSize), it might be the bottleneck.
尝试增加获取大小（JdbcTemplate.setFetchSize），这可能是瓶颈。
回答by Brian
Could be the overhead to establish your first DB connection. Try benchmarking performance after you've definitely already got a connection.
可能是建立第一个数据库连接的开销。在您确定已经建立连接后，尝试对性能进行基准测试。
This stuff shouldn'tbe slow, the gains are in there somewhere!
这东西不应该慢，收益就在那里！
回答by Lokesh
I dont think you can get any faster than raw jdbc call. When you say that you are able fetch it faster with sql client then is it in first attempt? Client and db can cache results, so check then timing of first query run from your sql client after session is created. Also post java logs , that will help to see where time is spent.
我认为您无法获得比原始 jdbc 调用更快的速度。当你说你可以用 sql 客户端更快地获取它时，它是第一次尝试吗？客户端和数据库可以缓存结果，因此请检查会话创建后从 sql 客户端运行的第一个查询的时间。还发布 java 日志，这将有助于查看时间花费在哪里。
