如何更快速的查询1000条数据？

现在的需求是这样的，我有一个模板，里面是JsonArray数据，里面有1000条JSONObject，每个object都有一个客户和产品属性，形如这样

{"data":[{"account":"01","product":"p1"},...]}

我现在要解析这个JSON，根据每一个object里的客户和产品，到另一个表里来查询它的平均销量和平均单价，
然后返回一个加了平均销量和平均单价的data，就是这样

{"data":[{"account":"01","product":"p1","avgNum":111,"avgPrice":222},...]}

我现在的做法是循环data，取出每个object，然后取出object的Account和Product,然后到一个查询方法里查询取得数据后，

object.put("avgNum","")...

;
这样一番下来，整个过程耗时26秒，可以说非常慢，另附上我的查询方法

	String sql="SELECT avg(il.UnitPrice),sum(il.Quantity)/12 FROM Invoice i LEFT  JOIN InvoiceLineItem  il " +
				"ON i.InvoiceId=il.InvoiceId " +
				"LEFT JOIN Product p ON p.ProductId=il.ProductId "+
				"LEFT JOIN Account a ON i.AccountId=a.AccountId "+
				"WHERE datediff(m,InvoicedOn,getDate())<=12 " +
				"AND p.ProductNumber=? AND a.AccountName=? ";
...
for(int i=0;i<jArray.size();i++){
			JSONObject o=jArray.getJSONObject(i);
				if(o.isEmpty())
					break;
				ps.setString(1,o.getString("product"));
				ps.setString(2,o.getString("account"));
				rs=ps.executeQuery();
				Map map=new HashMap<String,String>();
				map.put("$$avgPrice",0);
				map.put("$$avgNum",0);
				while(rs.next()){
					map.put("$$avgPrice",  rs.getDouble(1));
					map.put("$$avgNum", rs.getDouble(2));
				}
				o.putAll(map);
			}

不知道各位有什么好的主意，集思广益，

20 个解决方案

#1

就不能一次全提出来？还要弄了个 for 循环，反复数据库操作

#2

楼上所说甚对啊。如果说你的JsonArray也是从数据库里取出来封装好的，你完全可以用一个sql将你要的平均价格和数量一并都取出来啊，就是sql实现，最起码比你现在的思路要效率很多。

#3

真正慢的是你的sql语句，连4张表查

#4

引用 1 楼 defonds 的回复:

就不能一次全提出来？还要弄了个 for 循环，反复数据库操作

引用 2 楼 lgc8023 的回复:

楼上所说甚对啊。如果说你的JsonArray也是从数据库里取出来封装好的，你完全可以用一个sql将你要的平均价格和数量一并都取出来啊，就是sql实现，最起码比你现在的思路要效率很多。

模板里面是没有平均销量那些的，只有客户和产品，我也想过用一条SQL语句，奈何实在想不出来，因为JSONArray里的客户和产品是不固定的，有可能重复等等，如何才能用一条语句就搞定，还得请教

引用 3 楼 zuoziji_lj 的回复:

真正慢的是你的sql语句，连4张表查

有何高效语句，还望解答

#5

引用 4 楼 coolboyyzy 的回复:

Quote: 引用 1 楼 defonds 的回复:

就不能一次全提出来？还要弄了个 for 循环，反复数据库操作

引用 2 楼 lgc8023 的回复:

楼上所说甚对啊。如果说你的JsonArray也是从数据库里取出来封装好的，你完全可以用一个sql将你要的平均价格和数量一并都取出来啊，就是sql实现，最起码比你现在的思路要效率很多。

模板里面是没有平均销量那些的，只有客户和产品，我也想过用一条SQL语句，奈何实在想不出来，因为JSONArray里的客户和产品是不固定的，有可能重复等等，如何才能用一条语句就搞定，还得请教

引用 3 楼 zuoziji_lj 的回复:

真正慢的是你的sql语句，连4张表查

有何高效语句，还望解答

貌似真没有 ，看到left join 就有强迫症要去掉

#6

没人吗

#7

我也是，我看到left join我就想吐，我没有看明白你用个for是干嘛的，干嘛用这个呀，你一次性全拿出来不行吗？

#8

该回复于2014-02-14 08:24:08被版主删除

#9

你也可以单个表的查，在用程序组合起来啊 ！

#10

该回复于2014-02-14 08:24:10被版主删除

#11

不能搞个存储过程吗？

#12

"AND p.ProductNumber in (?,?,?) AND a.AccountName=(?,?,?) ";
?的个数由for循环定（ps.setString也一样），这样一次查询就可以得到所有结果，然后再打印

#13

1000次查询26秒，平均每次查询0.026秒，应该说不算慢。
12L的代码虽然不对，但是那种拼接SQL的方式应该可以减少查询次数。

其实，对于Oracle之类的数据库而言，
可以用类似以下的方式构造SQL语句

SELECT t.id, avg(il.UnitPrice),sum(il.Quantity)/12 FROM 
                                (
                                   SELECT 0 as id, 'a0' as AccountName, 'b0' as ProductNumber from dual
                                   union all
                                   SELECT 1 as id, 'a1' as AccountName, 'b1' as ProductNumber from dual
                                   union all
                                   SELECT 2 as id, 'a2' as AccountName, 'b2' as ProductNumber from dual
                                   union all ...
                                ) t
                                INNER JOIN Product p ON p.ProductNumber = t.ProductNumber
                                INNER JOIN Account a ON a.AccountName = t.AccountName
                                INNER JOIN Invoice i ON i.AccountId=a.AccountId
                                INNER JOIN InvoiceLineItem  il ON il.InvoiceId=i.InvoiceId AND il.ProductId=p.ProductId
WHERE datediff(m,InvoicedOn,getDate())<=12 
Group By t.id



这样，只要执行少数几次就可以了，而输出字段Id其实就是Jason中的元素编号

#14

把后面2个表，用exists会不会效果好点？

#15

帮顶吧

#16

一次全提取出来，放缓存中，开数据库取连接对资源消耗很大，数据量确实过大的话，建议虚模式加载

#17

13楼好强大，不过没看懂

#18

引用 4 楼 coolboyyzy 的回复:

Quote: 引用 1 楼 defonds 的回复:

就不能一次全提出来？还要弄了个 for 循环，反复数据库操作

引用 2 楼 lgc8023 的回复:

楼上所说甚对啊。如果说你的JsonArray也是从数据库里取出来封装好的，你完全可以用一个sql将你要的平均价格和数量一并都取出来啊，就是sql实现，最起码比你现在的思路要效率很多。

模板里面是没有平均销量那些的，只有客户和产品，我也想过用一条SQL语句，奈何实在想不出来，因为JSONArray里的客户和产品是不固定的，有可能重复等等，如何才能用一条语句就搞定，还得请教

引用 3 楼 zuoziji_lj 的回复:

真正慢的是你的sql语句，连4张表查

有何高效语句，还望解答

可以建个临时表，然后用insert select语句把需要的数据插入到这个表里。
用存储过程也行。

#19

时间消耗在你的循环ps.executeQuery();
考虑优化sql，能一次查询就一次全搞出来

#20

真是谢谢大家了，因为数据不会时时变动，所以现在改成先全部查出来，存在一个表里，然后查询这个表就可以了

#21

• Oracle查询大于1000条处理