PreparedStatement真的比Statement快吗？

从开始写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;
    		}
    	});
    	
    }

评论

49 楼 wplqw 2007-08-18  

PreparedStatement可以避免拼sql的时候那些讨厌的引号和字符串连接符，而且将sql文本的书写与参数的绑定分开，代码清晰很多。以前用delphi的时候，一直就是用参数法查询的。

48 楼 dmewy 2007-08-08  

Statement 在select语句的时候比PreparedStatement效率高.
其他的都是PreparedStatement高.

47 楼 seantan 2007-03-01  

有书提到过，不过是针对oracle
http://www.onjava.com/pub/a/onjava/excerpt/oraclejdbc_19/index.html?page=2

按照JDBC规范的想法，实际上preparedstatement的出现主要的目的是为了提高SQL语句的的性能，而防止SQL注入和类型转换实际上并不是规范原本的目标。但是实践中这两个功能却受到更多的欢迎。

preparedstatment性能是否能提高性能，实际要具体的看数据库服务器端处理复杂SQL的情况，对于oracle而言在复杂的SQL上面二者性能差异明显（可以使用报表系统的SQL来测试），而比较简单的数据库MySQL和PostgreSQL其的性能提升不明显。在这些某些版本的JDBC实现Preparedstatement的实现就是直接使用statment的实现。

46 楼 lszone 2007-03-01  

执行次数较多的sql，还是用pstmt快

45 楼 IvanLi 2007-03-01  

pcdos 写道

connection关闭后，PreparedStatement就失去了作用，如果是一个connection那ps的效果就出来了

据说mysql的ps最终也是拼装字符串，所以效率反而差点

所以我们要用connection pool

44 楼 pcdos 2007-03-01  

connection关闭后，PreparedStatement就失去了作用，如果是一个connection那ps的效果就出来了

据说mysql的ps最终也是拼装字符串，所以效率反而差点

43 楼 smilesmith 2007-02-09  

昨天和公司的oracle dba讨论过oracle sql执行的具体细节：oracle 执行sql时，首先要分析sql生成查询计划，具体有：分析sql的合法性（到数据库schema中查询），决定使用某个的哪段索引（好像是这样说的）... ...，然后在第一次查询时，用物理读，其后相同的查询计划都是逻辑读，是到缓存里面读。
所以我想PreparedStatement是不是有利于oracle生成查询计划，或许还有别的帮助，所以提高了性能。
但有一点是肯定的，PreparedStatement一定需要driver具体实现才行，Prepared是缓存在数据库端的，要看看oracle jdbc驱动的实现代码才有发言权吧。
另外：我们讨论这些，纯粹是要理解jdbc具体的细节，和操作数据库的底层实现，这些应该是基础知识吧。知其然，并知其所以然。

42 楼 LucasLee 2007-01-25  

downpour 写道

我觉得很莫名，MySQL是否支持PrepareStatement和你是否使用PrepareStatement根本就是两回事。我绝对不会因为MySQL某个版本不支持的特性而改变一个良好的编程习惯，更何况，楼上的这种测试事实上没有任何实际意义。正如Robbin所说，你如何模拟生产环境呢？

不是同一个问题。
我们做的这个事情，并不能证明日常编程中应该使用Statement而不是PreparedStatement，相反，我觉得仍然是PreparedStatement更合适。有很多理由，1.防止SQL injection，2.自动类型转换，而获得更好的可移植性；3.更好的性能（尤其是对Oracle，MySQL不一定）

但我们的意义在于，深入理解一些常识背后的知识，而且我们发现有些常识不是那么准确的。
这个问题就象很多Java技术书籍里讲String的知识一样，很多细节不是看上去那样的，不是那么统一的。

现在的问题并不是不能模拟生产环境，而是Robbin提出的方案过于难以实现，或者代价太大。
什么叫“做一个真实的web应用”？具体有什么特性？
还要用Statement实现一遍，再用PreparedStatement改写一遍？

我难以做到这样的测试，但另一方面，你们也难以执行你们的方案以证明你们的观点。所以这样的方案对于我们来说，由于操作性低，这才是无意义的。

我们可以做较复杂的SQL测试，也可以多线程的压力测试。唯独难以做到“真实的应用”，假使做到了一个真实的，你又可以说你那种是特例，又需要在什么什么情况下再测试。其实你们所谓的真实应用，最多也不过是某一类应用，不能代表所有的情况，不是么？

41 楼 kabbesy 2007-01-25  

downpour 写道

我觉得很莫名，MySQL是否支持PrepareStatement和你是否使用PrepareStatement根本就是两回事。我绝对不会因为MySQL某个版本不支持的特性而改变一个良好的编程习惯，更何况，楼上的这种测试事实上没有任何实际意义。正如Robbin所说，你如何模拟生产环境呢？

不辩不明事理
良好的编程习惯是什么？习惯又由何而来？
何况楼主、lucas和很多人，都是基于测试数据在进行怀疑，没有任何不好的地方

40 楼 downpour 2007-01-25  

我觉得很莫名，MySQL是否支持PrepareStatement和你是否使用PrepareStatement根本就是两回事。我绝对不会因为MySQL某个版本不支持的特性而改变一个良好的编程习惯，更何况，楼上的这种测试事实上没有任何实际意义。正如Robbin所说，你如何模拟生产环境呢？

39 楼 LucasLee 2007-01-25  

alin_ass 写道

哪里有说不支持了？


http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

13.7. SQL Syntax for Prepared Statements

MySQL 5.0 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol implemented in MySQL 4.1, provided that you use an appropriate client programming interface. Candidate interfaces include the MySQL C API client library (for C programs), MySQL Connector/J (for Java programs), and MySQL Connector/NET.

是的，MySQL4.1以后就支持服务端的Prepared Statement了。
我搞错了，我之前的信息来源是：mysql-connector-java-5.0.3/docs/connect-j.pdf中的：
PreparedStatements are implemented by the driver, as MySQL does not have a prepared statement
feature. Because of this, the driver does not implement getParameterMetaData() or getMetaData() as
it would require the driver to have a complete SQL parser in the client.
Starting with version 3.1.0 MySQL Connector/J, server-side prepared statements and 'binary-encoded'
result sets are used when the server supports them.

之前理解错了。JDBC driver是当MySQL不支持服务端Prepared Statement时(MySQL4.1之前)，在Driver里实现这个特性；而当MySQL支持时(MySQL4.1之后)，使用服务端的特性。

可以参考这个：http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

虽然MySQL5支持服务端precomiple,但它实现得还比较弱啊，相对于Statement我至今没看出性能提升，相反更弱。

38 楼 alin_ass 2007-01-25  

哪里有说不支持了？


http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

13.7. SQL Syntax for Prepared Statements

MySQL 5.0 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol implemented in MySQL 4.1, provided that you use an appropriate client programming interface. Candidate interfaces include the MySQL C API client library (for C programs), MySQL Connector/J (for Java programs), and MySQL Connector/NET.

37 楼 liangguanhui 2007-01-25  

噩耗：mysql原来不支持PreparedStatement特性

36 楼 LucasLee 2007-01-24  

我在google上查了很久，没有我想要的PreparedStatement vs. statement的全面比较。
大部分都是泛泛的说PreparedStatement理论上怎么怎么样。

找到一个对于MySQL JDBC的讨论：
http://www.mysqltalk.org/statements-vs-preparedstatements-vt112753.html
基本上说的是，Oracle里基本上始终是PreparedStatement性能更好，而MySQL则不是。

35 楼 IvanLi 2007-01-24  

codeutil 写道

PreparedStatement 更重要的功能是防sql 注入.

这点我同意

34 楼 codeutil 2007-01-24  

PreparedStatement 更重要的功能是防sql 注入.

33 楼 IvanLi 2007-01-24  

引用

In Embedded SQL, dynamic statements are SQL statements that need to be compiled at runtime, rather than statically. Typically, dynamic statements contain input parameters, although this is not a requirement. In SQL, the prepare command is used to precompile a dynamic statement and save it so that it can be executed repeatedly without being recompiled during a session. If a statement is used multiple times in a session, precompiling it provides better performance than sending it to the database and compiling it for each use. The more complex the statement, the greater the performance benefit.If a statement is likely to be used only a few times, precompiling it may be inefficient because of the overhead involved in precompiling, saving, and later deallocating it in the database.Precompiling a dynamic SQL statement for execution and saving it in memory uses time and resources. If a statement is not likely to be used multiple times during a session, the costs of doing a database prepare may outweigh its benefits. Another consideration is that once a dynamic SQL statement is prepared in the database, it is very similar to a stored procedure. In some cases, it may be preferable to create stored procedures and have them reside on the server, rather than defining prepared statements in the application.

1：在Sybase中，PreparedStatement是基于Session存储的，一个被编译好的dynSQL语句只对一个session有效，对于那些在一个Session中多次使用的dynSQL语句，使用PreparedStatement会带来性能的提升，sql越复杂使用频率越高，性能提升越大。但是如果一个dynSQL语句在一个Session下被调用的次数少，那么PreparedStatement有可能会导致性能的下降，因为编译，存储，销毁dynSQL语句也是花时间的。
2：在一个session中被编译好的dynSQL其执行效率跟储存过程的效率相似，唯一不同的是PreparedStatement编译好的dynSQL是session有效，而存储过程是全数据库有效。
3：在我上面的测试中我是采用直接连数据库的方式，所以分3次执行使用PreparedStatement的数据库插入操作每次都会重新编译，而且在测试结束时还要负责销毁编译好的查询，这也许是PreparedStatement败下阵来的原因，采用robbin的测试方法，并且sql写的很复杂，那么PreparedStatement也许会有更好的性能

32 楼 codeutil 2007-01-24  

我在 Oracle 下的测试结果:

数据库: Oracle9i Enterprise Edition Release 9.2.0.1.0
jdbc驱动 : ojdbc14.jar
数据库表结构脚本:

create table STMTUSER
(
  USERNO   NUMBER default 0 not null,
  USERNAME VARCHAR2(40) default '无名无名无名无名' not null,
  EMAIL    VARCHAR2(100) default '[e-mail]' not null,
  PHONE    VARCHAR2(21) default '1388888888888'
)
;
alter table STMTUSER
  add constraint PK_STMTUSER_USERNO primary key (USERNO);
  

主键序列:

create sequence SEQ_STMTUSER_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 100021
increment by 1
cache 20;

生成测试数据的sql:

insert into stmtuser (userno) values(SEQ_STMTUSER_ID.Nextval);

共生成10万条数据.


在plsql中查看会话观察到的
Statement执行的sql为: select t.*, t.rowid from stmtuser t where t.userno= 3277 (变化的数字)
select t.*, t.rowid from stmtuser t where t.userno= :1 (使用的占位符)

第一轮测试, Statement和PreparedStatement各创建一个连接.
循环内每次创建stmt,并关闭stmt
第一次:
执行 500 次Statement操作耗费的时间为3594
执行 500 次PreparedStatement操作耗费的时间为3125
第二次:
执行 5000 次Statement操作耗费的时间为17985
执行 5000 次PreparedStatement操作耗费的时间为12625
第三次:
执行 50000 次Statement操作耗费的时间为154094
执行 50000 次PreparedStatement操作耗费的时间为113188


第二轮测试,Statement和PreparedStatement各创建一个连接.各只创建一次stmt

第一次:
执行 500 次Statement操作耗费的时间为2875
执行 500 次PreparedStatement操作耗费的时间为938
第二次:
执行 5000 次Statement操作耗费的时间为19453
执行 5000 次PreparedStatement操作耗费的时间为6031
第三次:
执行 50000 次Statement操作耗费的时间为155250
执行 50000 次PreparedStatement操作耗费的时间为43891

附件是测试的 java 文件:

31 楼 LucasLee 2007-01-24  

robbin 写道

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

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

我想，你的这个经验是在oracle上得到的，我在Oracle上也得到了相同的经验，（我在前面的帖子中提及了），
但是，每个数据库是不同的，特别是，MySQL数据库本身并没有PreparedStatemet特性，这一点可以从MySQL JDBC Driver的reference里查到，所以至少，在MySQL上使用PreparedStatement在理论上就没有任何优势，更甚的是，
从我们的测试来看，在MySQL上使用PreparedStatement会比Statement慢一倍左右。

你不认为，了解到不同数据库在这一点上有着较大的差别，是有意义的么？

30 楼 Allen 2007-01-24  

刚才查了一下，MySQL Connector/J Docs 中确实有写到：

引用

PreparedStatement

PreparedStatements are implemented by the driver, as MySQL does not have a prepared statement feature. Because of this, the driver does not implement getParameterMetaData() or getMetaData() as it would require the driver to have a complete SQL parser in the client.

Starting with version 3.1.0 MySQL Connector/J, server-side prepared statements and binary-encoded result sets are used when the server supports them.

所以我在想，是不是在建立MySQL的JDBC连接时，对Driver中PreparedStatement相关属性（例如cachePrepStmts）的配置应该会对将来的性能有不小的影响？