两个一样的sql
SELECT period,idCnt FROM MyTb WHERE
((idCnt - 1) IN (SELECT idCnt FROM MyTb WHERE name ='book' AND idCnt>=44905)) AND idCnt>=44905
SELECT period,idCnt FROM MyTb WHERE
((idCnt - 1) IN (SELECT idCnt FROM MyTb WHERE name =
(SELECT name FROM MyTb WHERE
idCnt =(SELECT MAX(idCnt) FROM MyTb ))) AND idCnt>=44905) AND idCnt>=44905
如上(SELECT name FROM MyTb WHEREidCnt =(SELECT MAX(idCnt) FROM MyTb ))这个实际上就是name ='book'
测试程序---------
long start=System.currentTimeMillis(); //获取最初时间
stmt = dbConn.prepareStatement(sql);
rs=stmt.executeQuery();
long end=System.currentTimeMillis(); //获取运行结束时间
out.print(end-start)+"ms");
rs.close();
stmt.close();
测试结果:
我在本地测试的、数据库SqlServer2000,数据总量47000条,两条sql执行结果一样
前者直接写name ='book' 的耗时29547ms
而后者只有436ms,按理说应该直接写name ='book' 的快些呀。
另外--------------
stmt = dbConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs=stmt.executeQuery(sql);
用上面的替代stmt = dbConn.prepareStatement(sql);
居然两者的执行时间就差不多了,而且达到了正常的直接写name=‘book’快
大家分析下,什么原因啊 ???