PreparedStatement真的比Statement快吗？

IvanLi

浏览: 604605 次
性别:
来自: 北京

最近访客更多访客>>

zhaoliuchuan

hnuxgp

hujihong

ayizylusi

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

技术天地

MySQL SQL Oracle SQL Server JDBC

从开始写java程序就一直被灌输着一种思想，如果使用jdbc一定要使用PreparedStatement，而不要使用Statement对象。
其中的原因有好多，比如可以防止SQL注入攻击，防止数据库缓冲池溢出，代码的可读性，可维护性。这些都很正确。
但是还有一点人们经常提的就是PreparedStatement能够显著的提高执行效率。
看了两篇关于PreparedStatement和Statement的帖子
http://dev2dev.bea.com.cn/bbs/thread.jspa?forumID=121&threadID=10397&start=0&tstart=0
http://www.iteye.com/topic/5631?page=1
里面提到，PreparedStatement并不一定比Statement快，于是想自己动手测试一下
一共分5种情况测试

stmt	  pre-stmt	
--1:每次都单独连数据库--100条
21.936	22.076
22.05	  22.051	
22.172	22.178	
--2:Stmt使用相同的SQL语句，参数值也相同--100条
0.78	  0.796	
0.718	  0.749	
0.749	  0.764	
--3:Stmt使用相同的SQL语句，参数值不同--100条
0.967	  0.749	
0.78	  0.874	
0.749	  0.749	
--4:Stmt使用相同的SQL语句，参数值也相同--10000条
33.079	36.154	
34.156	39.306	
34.359	36.138	
--5:Stmt使用相同的SQL语句，参数值不同--10000条
32.799	34.125	
32.564	35.53	
35.701	34.952	
32.582	40.798	
38.893	42.345	
35.082	41.736

分析：
第一种情况：
由于我测试时采用jdbc直接链接数据库，所以在第一种情况下插入速度很慢，时间都消耗在了数据库链接的创建上了，符合常理，没什么疑义。
第二种情况：
由于使用Statement插入时sql语句都相同，我通过阅读上面的两篇帖子，猜想可能是Statement语句也被DB缓存了，所以跟
PreparedStatement速度差不多，我想如果每次插入的sql都不同，那么Statement应该慢下来了吧。
第三种情况：
并非像我在执行第二种情况时想象的那样，PreparedStatement还是跟Statement差不多，于是我想是不是数据量不够大呀，于是我增大了
插入数据量，希望PreparedStatement能够比Statement快些
第四/五种情况：
测试结果PreparedStatement反到更慢了。

PreparedStatement什么情况下才会比Statement快？那位大侠来指点一下！

代码如下：
DDL

CREATE TABLE dbo.jdbc_stmt 
(
    id   numeric(10,0) IDENTITY,
    name varchar(10)   NOT NULL,
    grad int           NOT NULL,
    age  int           NOT NULL,
    dept int           NOT NULL
)

测试程序

public void jdbcStmtPerformance()
    {
    	//1:每次都单独连数据库--100条
//    	String stmtSQL = "insert into jdbc_stmt values('ivanl',2,25,1001)";
//    	for(int i = 0; i< 100; i++)
//    	{
//    		logger.debug("stmt #"+i);
//    		getJdbcTemplate().update(stmtSQL);
//    	}
    	
    	//2:Stmt使用相同的SQL语句，参数值也相同--100条
//    	getJdbcTemplate().execute(new StatementCallback(){
//    		public Object doInStatement(Statement stmt) throws SQLException
//    		{
//    			String stmtSQL = "insert into jdbc_stmt values('ivanl',2,25,1001)";
//    	    	for(int i = 0; i< 100; i++)
//    	    	{
//    	    		logger.debug("stmt #"+i);
//    	    		stmt.executeUpdate(stmtSQL);
//    	    	}
//    			return null;
//    		}
//    	});
    	
    	//3:Stmt使用相同的SQL语句，参数值不同--100条
//    	getJdbcTemplate().execute(new StatementCallback(){
//    		public Object doInStatement(Statement stmt) throws SQLException
//    		{
//    			String stmtSQL = "insert into jdbc_stmt values('ivanl',2,25,";
//    	    	for(int i = 0; i< 100; i++)
//    	    	{
//    	    		logger.debug("stmt #"+i);
//    	    		stmt.executeUpdate(stmtSQL+i+")");
//    	    	}
//    			return null;
//    		}
//    	});
    	
//    	4:Stmt使用相同的SQL语句，参数值也相同--10000条
//    	getJdbcTemplate().execute(new StatementCallback(){
//    		public Object doInStatement(Statement stmt) throws SQLException
//    		{
//    			String stmtSQL = "insert into jdbc_stmt values('ivanl',2,25,1001)";
//    	    	for(int i = 0; i< 10000; i++)
//    	    	{
//    	    		logger.debug("stmt #"+i);
//    	    		stmt.executeUpdate(stmtSQL);
//    	    	}
//    			return null;
//    		}
//    	});
    	
//    	5:Stmt使用相同的SQL语句，参数值不同--10000条
    	getJdbcTemplate().execute(new StatementCallback(){
    		public Object doInStatement(Statement stmt) throws SQLException
    		{
    			String stmtSQL = "insert into jdbc_stmt values('ivanl',2,25,";
    	    	for(int i = 0; i< 10000; i++)
    	    	{
    	    		logger.debug("stmt #"+i);
    	    		stmt.executeUpdate(stmtSQL+i+")");
    	    	}
    			return null;
    		}
    	});
    }
    
    
    public void jdbcPreStmtPerformance()
    {
    	//1:每次都单独连数据库--100条
//    	String stmtSQL = "insert into jdbc_stmt values(?,?,?,?)";
//    	for(int i = 0; i< 100; i++)
//    	{
//    		logger.debug("pre-stmt #"+i);
//    		getJdbcTemplate().update(stmtSQL, new Object[]{"ivanl", new Integer(2), new Integer(25), new Integer(1002)});
//    	}
    	
    	//2:Stmt使用相同的SQL语句，参数值也相同--100条
//    	String stmtSQL = "insert into jdbc_stmt values(?,?,?,?)";
//    	getJdbcTemplate().execute(stmtSQL,new PreparedStatementCallback(){
//    		public Object doInPreparedStatement(PreparedStatement ps)  throws SQLException
//    		{
//    	    	for(int i = 0; i< 100; i++)
//    	    	{
//    	    		logger.debug("pre-stmt #"+i);
//    	    		ps.setString(1, "ivanl");
//    	    		ps.setInt(2, 2);
//    	    		ps.setInt(3, 25);
//    	    		ps.setInt(4, 1002);
//    	    		ps.execute();
//    	    	}
//    			return null;
//    		}
//    	});
    	//3:Stmt使用相同的SQL语句，参数值不同--100条
//    	String stmtSQL = "insert into jdbc_stmt values(?,?,?,?)";
//    	getJdbcTemplate().execute(stmtSQL,new PreparedStatementCallback(){
//    		public Object doInPreparedStatement(PreparedStatement ps)  throws SQLException
//    		{
//    	    	for(int i = 0; i< 100; i++)
//    	    	{
//    	    		logger.debug("pre-stmt #"+i);
//    	    		ps.setString(1, "ivanl");
//    	    		ps.setInt(2, 2);
//    	    		ps.setInt(3, 25);
//    	    		ps.setInt(4, i);
//    	    		ps.execute();
//    	    	}
//    			return null;
//    		}
//    	});
    	//4:Stmt使用相同的SQL语句，参数值也相同--10000条
//    	String stmtSQL = "insert into jdbc_stmt values(?,?,?,?)";
//    	getJdbcTemplate().execute(stmtSQL,new PreparedStatementCallback(){
//		public Object doInPreparedStatement(PreparedStatement ps)  throws SQLException
//		{
//	    	for(int i = 0; i< 10000; i++)
//	    	{
//	    		logger.debug("pre-stmt #"+i);
//	    		ps.setString(1, "ivanl");
//	    		ps.setInt(2, 2);
//	    		ps.setInt(3, 25);
//	    		ps.setInt(4, 1002);
//	    		ps.execute();
//	    	}
//			return null;
//		}
//	});
    	//5:Stmt使用相同的SQL语句，参数值不同--10000条
    	String stmtSQL = "insert into jdbc_stmt values(?,?,?,?)";
    	getJdbcTemplate().execute(stmtSQL,new PreparedStatementCallback(){
    		public Object doInPreparedStatement(PreparedStatement ps)  throws SQLException
    		{
    	    	for(int i = 0; i< 10000; i++)
    	    	{
    	    		logger.debug("pre-stmt #"+i);
    	    		ps.setString(1, "ivanl");
    	    		ps.setInt(2, 2);
    	    		ps.setInt(3, 25);
    	    		ps.setInt(4, i);
    	    		ps.execute();
    	    	}
    			return null;
    		}
    	});
    	
    }

分享到：

我来说说数据库中的identity gap问题 | 用AjaxProxyServlet 跨域ajax请求

2007-01-22 16:15
浏览 39443
评论(49)
论坛回复 / 浏览 (49 / 43634)
分类:企业架构
查看更多

29 楼 IvanLi 2007-01-24

robbin 写道

是的，你们确实做了无用功。测试的目的在于指导实践。就单个请求来说statement要快，但是如果按照这种思路去写代码，后果是很严重的。因为会对数据库服务器造成很重的压力，系统吞吐量也要降低。早在2001年的时候，我维护的一个系统(访问量巨大，每天上百万)，Oracle数据库每周都要宕机一次，经过几个月的排查，最终找到的原因就是我们程序所有的条件查询全部都是statement拼SQL字符串。全部改掉以后恢复正常，性能也明显提高。

实际的应用程序都是多用户并发的web应用，不是单机自己一个人用的，你们那种测试根本就不能反应实际的多用户并发的系统吞吐量和数据库负载情况，不是误导是什么？

受教了，我不是想误导谁，我在写程序时也都是用PreparedStatement,这是我刚开始学习java时就被灌输的思想,只是看了我在一楼提到的那两篇帖子,心中不免有些疑问，所以索性就测试了一下，不过MySQL官方文档说它不支持Prepared Statement的feature所以如果使用MySQL(感谢Lucas Lee的发现),使用PreparedStatement是不会有性能的提升的.

28 楼 robbin 2007-01-24

JDBC连接池那点缓存起不了多大作用的。

27 楼 qingyujingyu427 2007-01-24

不好意思,我刚才说的好像基本都错了.
JDBC1.0和2.0的连接池缓存Statement对数据库影响不大,所有sql的第一次编译应该还是在数据库端,数据库还是要缓存所有的sql.只是sql第二次执行的时候,数据库不需要去判断这个sql是不是在缓存里是否有编译过的执行计划了,减少的这点工作应该是没什么太大用处.
好像JDBC3.0是可以把编译工作和缓存转移到web server端的(初步理解,不知到是否正确).

26 楼 qingyujingyu427 2007-01-24

连接池缓存Statement,是JDBC3.0的新特性?还是JDBC一直有这种特性?
如果有这种特性的话,数据库的压力应该会小些.不过按照我说的那中例子,Statement应当还是要编译5,6千次吧.只不过把这个工作放在了web server端.
只不过不知道数据库端还会不会缓存那么多sql.以前没了解过这种特性,也不知道一般这种特性JDBC驱动是怎么实现的.
JDBC2.0的时候数据库应该还是缓存了那些sql的.

25 楼 likeblood 2007-01-23

如果你用了连接池的话,很难说的
因为你的连接池是帮你缓存statement
而且你的sql这么简单,比较难测试出来的

24 楼 qingyujingyu427 2007-01-23

支持robin的看法，感觉这是很自然的事情，个人认为本来PreparedStatement设计的原因就是充分利用好数据库的sql预编译特性。
好像一年前在ibm developerwork上还是什么论坛看过有人做过比较详细的测试数据。
例如一个sql：
select a.col1,a.col2,a.col3,b.col1,b.col3,c.col1,d.col2,e.col1
from
tablea a,tableb b,tablec c,tabled d,tablee,e
where
a.col5=b.col3
and
a.col6=b.col4
and
b.col2=e.col5
and
a.col3=?
and
e.col1=?

例如网站有这么一个常用查询，每天可能要执行几十万次，用PreparedStatement的话，理论上数据库只编译一次。只要有一个用户执行过此查询，其它用户执行的时候就会相对变快了。尤其是比较复杂的sql可能会比较明显。如果用Statement的话，当然一般由于有很多条件是相同的，理论上一般可能数据库编译5，6千次吧，这样占的数据库内存也要多一些。

一般来讲瓶颈都在数据库吧，能给它减少点负担就减少点负担。

23 楼 robbin 2007-01-23

22 楼 LucasLee 2007-01-23

robbin 写道

做这样的测试没有任何意义，因为测试场景和实际使用场景根本就不一样。

何以见得会没有任何意义？
至少说明PreparedStatement不是在任何情况下都比Statement快。
而且看起来是，需要很多条件满足，PreparedStatement才会比Statement快，尽管可能常见的web应用中比较典型，但那个我们尚未验证。

robbin 写道

对于数据库来说，单个PrepareStatement因为还需要参数代入过程，所以比单个Statement执行速度慢是很正常的。但是对于一个真实的应用来说，单个请求执行速度快几十毫秒，还是慢几十毫秒，根本无关紧要。

重要的是你要考察模拟几十个用户并发访问应用的时候，应用程序的总吞吐量究竟是上升还是下降，数据库的负载是上升还是下降，这才是至关重要的！

具体来说，要这样来测试：

写一个真实的web应用，尽量多执行一些条件查询，但是全部使用statement，字符串拼SQL。然后用loadrunner录制用户使用脚本，模拟几十个用户并发访问web应用，进行压力测试。在测试过程中，注意记录数据库服务器的CPU使用率和数据库自身的性能参数(例如Oracle的SGA各项参数)

然后修改程序，全部改成PrepareStatment，用loadrunner再次执行同样的压力测试，在测试过程中，记录数据库服务器的CPU使用率和数据库自身的性能参数。

然后再对比两种不同查询方法的压力测试，究竟哪种对数据库负载比较重，对数据库资源消耗比较大，究竟哪种方法web应用的吞吐量更好。

这才是科学的测试。有没有人有兴趣按照我的方案测一把？

不过我可以预先告诉大家答案，那就是用preparestatment的时候，web应用的吞吐量更大，数据库负载更低，不信的呢就自己去试。

为什么以这种口气提你的方案？给了这么一个复杂又不明确的方案，你自己还不想动手做，你要说明什么？
说明大家都在做一个无意义的事情？

21 楼 robbin 2007-01-23

做这样的测试没有任何意义，因为测试场景和实际使用场景根本就不一样。

对于数据库来说，单个PrepareStatement因为还需要参数代入过程，所以比单个Statement执行速度慢是很正常的。但是对于一个真实的应用来说，单个请求执行速度快几十毫秒，还是慢几十毫秒，根本无关紧要。

重要的是你要考察模拟几十个用户并发访问应用的时候，应用程序的总吞吐量究竟是上升还是下降，数据库的负载是上升还是下降，这才是至关重要的！

具体来说，要这样来测试：

写一个真实的web应用，尽量多执行一些条件查询，但是全部使用statement，字符串拼SQL。然后用loadrunner录制用户使用脚本，模拟几十个用户并发访问web应用，进行压力测试。在测试过程中，注意记录数据库服务器的CPU使用率和数据库自身的性能参数(例如Oracle的SGA各项参数)

然后修改程序，全部改成PrepareStatment，用loadrunner再次执行同样的压力测试，在测试过程中，记录数据库服务器的CPU使用率和数据库自身的性能参数。

然后再对比两种不同查询方法的压力测试，究竟哪种对数据库负载比较重，对数据库资源消耗比较大，究竟哪种方法web应用的吞吐量更好。

这才是科学的测试。有没有人有兴趣按照我的方案测一把？

不过我可以预先告诉大家答案，那就是用preparestatment的时候，web应用的吞吐量更大，数据库负载更低，不信的呢就自己去试。

20 楼 LucasLee 2007-01-23

Ivan Li 写道

jamesby 写道

PrepareStatement是预编译,而Statement是解释,应该PrepareStatement对于数据库来说执行速度更快才对.

我也一直是这种思想，但是测试的结果确不是这样的

对阿，我也是，但是凡事不能想当然啊。有的时候事实真的出乎意料。

19 楼 jamesby 2007-01-23

Ivan Li 写道

jamesby 写道

PrepareStatement是预编译,而Statement是解释,应该PrepareStatement对于数据库来说执行速度更快才对.

我也一直是这种思想，但是测试的结果确不是这样的

感觉PrepareStatement对数据库来说性能影响小,毕竟不用解释,是不是多个线程下做测试,让数据库服务器在一定压力的情况下测试结果会不同?

18 楼 IvanLi 2007-01-23

jamesby 写道

PrepareStatement是预编译,而Statement是解释,应该PrepareStatement对于数据库来说执行速度更快才对.

我也一直是这种思想，但是测试的结果确不是这样的

17 楼 dengyin2000 2007-01-23

Ivan Li 写道

dengyin2000 写道

PreparedStatement预编译应该是在数据库server端吧。是不是要close Statement 和 PreparedStatement来测试。因为如果都是使用同一个stmt的话，就没有考虑到编译的效率了。

明白你的意思了，用ConnectionCallback写了个测试
测试结果比单个stmt慢了，但是还是比PreparedStatement快，四次的执行时间分别是
34.357
35.965
36.184
32.184

           getJdbcTemplate().execute(new ConnectionCallback(){
    		public Object doInConnection(Connection conn) throws SQLException
    		{
    			String stmtSQL = "insert into jdbc_stmt values('ivanl',2,25,";
    			for(int i = 0; i< 10000; i++)
    	    	{
    				Statement stmt = conn.createStatement(); 
    	    		logger.debug("stmt #"+i);
    	    		stmt.executeUpdate(stmtSQL+i+")");
    	    		stmt.close();
    	    	}
    			return null;
    		}
    	   });

可能是数据库的实现不一样吧。当然还是力推使用Pre statement

16 楼 jamesby 2007-01-23

PrepareStatement是预编译,而Statement是解释,应该PrepareStatement对于数据库来说执行速度更快才对.

15 楼 ggyy 2007-01-23

你给出的测试例子都是执行很简单的sql, 在处理复杂的sql上面使用pre是有一些优势的.

14 楼 IvanLi 2007-01-23

dengyin2000 写道

明白你的意思了，用ConnectionCallback写了个测试
测试结果比单个stmt慢了，但是还是比PreparedStatement快，四次的执行时间分别是
34.357
35.965
36.184
32.184

           getJdbcTemplate().execute(new ConnectionCallback(){
    		public Object doInConnection(Connection conn) throws SQLException
    		{
    			String stmtSQL = "insert into jdbc_stmt values('ivanl',2,25,";
    			for(int i = 0; i< 10000; i++)
    	    	{
    				Statement stmt = conn.createStatement(); 
    	    		logger.debug("stmt #"+i);
    	    		stmt.executeUpdate(stmtSQL+i+")");
    	    		stmt.close();
    	    	}
    			return null;
    		}
    	   });

13 楼 IvanLi 2007-01-23

我是使用Sybase ASE12.5测试的，跟MSSQL Server 2000差不多，都是PreparedStatement慢

12 楼 LucasLee 2007-01-23

kwang 写道

楼上的，如果你把Statement st = con.createStatement();
和PreparedStatement pst = con.prepareStatement(psql); 都写在for循环外面，应该就更能看出谁快谁慢了。我在roacle里测了一下，查询5000条数据，PreparedStatement比Statement快一倍。

我也这么测试过，结果跟你一样。但是这种方式不能代表普通的web应用常见的用法，除了代码中有批处理的情况，大部分代码(比如CRUD)都是创建一个PreparedStatement然后执行、关闭，所以可以粗略的跟循环中创建、执行、关闭相比较，而不能跟你说的创建依次再多次运行相比。
不过在另一方面说明了，如果有批处理，更应该使用PreparedStatement .

11 楼 LucasLee 2007-01-23

codeplay 写道

count大点然后Math.random()*100 换成 Math.random()*10000000结果会不会更明显?

对于prepared stmt和stmt,Oracle应该都缓存其编译后的stmt,缓存100个对它来说太易了

对这个测试来说,所有pre stmt只需要编译一个即可,而stmt只要id不同就得编译

说得有道理，我使用count=1000 ,Math.random()*1024*1024,果然结果有区别。
Oracle10g 6359 1422
PostgreSQL8.1 1219 1281
MySQL5 688 968
MSSQL2K(JTDS) 516 453
MSSQL2K(MS) 516 719

count=10000 ,Math.random()*1024*1024:
Oracle10g 59953 11937
PostgreSQL8.1 7844 8328
MySQL5 4266 5843
MSSQL2K(JTDS) 3735 2562
MSSQL2K(MS) 3125 4235

说明Oracle在PreparedStatement比Statement的性能优势这方面更明显。

10 楼 codeplay 2007-01-23

有道理呵呵

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论