我已经为我的一位客户开发了一个招标管理系统代码。这是一段代码:
try {
Connection con = Mycon.getConnection();
PreparedStatement ps = con.prepareStatement("TRUNCATE tempcalcplan");
ps.executeUpdate();
ps.clearBatch();
ps = con.prepareStatement("INSERT INTO tempcalcplan SELECT v.conid,c.conname, v.rate, v.venid FROM venprices v LEFT JOIN country c ON c.conid = v.conid WHERE (v.conid, v.rate) IN ( SELECT v.conid, MIN(v.rate) FROM venprices v GROUP BY v.conid) GROUP BY v.conid");
ps.executeUpdate();
ps.clearBatch();
ps = con.prepareStatement("select * from tempcalcplan");
ResultSet rs = ps.executeQuery();
jTable1.setModel(DbUtils.resultSetToTableModel(rs));
} catch(Exception e){
e.printStackTrace();
}
这段代码旨在用作:
清空表
tempcalcplan
(假设已存储以前的结果。)借助2个不同的表(表如下所示)生成所需数据并将其存储在
tempcalcplan
表中在jTable上显示
tempcalcplan
表的结果。这是
country
和tempcalcplan
表。country
(包含30,000条记录)+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| conid | int(10) | NO | PRI | NULL | |
| conname | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
tempcalcplan
(包含1,80,000条以上的记录)+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| conid | int(10) | NO | PRI | NULL | |
| conname | varchar(50) | YES | | NULL | |
| rate | double | YES | | NULL | |
| venid | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
venprices
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| conid | int(10) | NO | PRI | NULL | |
| rate | double | YES | | NULL | |
| venid | varchar(50) | NO | PRI | | |
+-------+--------------+------+-----+---------+-------+
结果最多需要12-15分钟才能显示在jTable中。
我希望将其减少到1-2分钟。
最佳答案
这是可能花费最多时间的查询:
INSERT INTO tempcalcplan
SELECT v.conid, c.conname, v.rate, v.venid
FROM venprices v LEFT JOIN
country c
ON c.conid = v.conid
WHERE (v.conid, v.rate) IN ( SELECT v.conid, MIN(v.rate) FROM venprices v GROUP BY v.conid) GROUP BY v.conid");
我的猜测是
WHERE
子句引起了问题。尝试将其移至FROM
子句: SELECT v.conid, c.conname, v.rate, v.venid
FROM venprices v LEFT JOIN
country c
ON c.conid = v.conid JOIN
(SELECT v.conid, MIN(v.rate) as rate
FROM venprices v
GROUP BY v.conid
) vr
ON v.conid = vr.conid and v.rate = vr.rate;
我认为您的索引对此查询设置良好。
关于java - 优化帮助:SQL查询以显示每个国家/地区的最低价格,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/39659729/