参数化SQL与非参数化SQL效率比较的测试

在讨论持久层框架时项目组成员提到这样一个问题：使用参数化SQL对性能会有怎样的影响？

使用参数化时，将是这样的语句：

insert into TableName(col1, col2) values(@p1, @p2) @p1 = 'aaaa', @p2 = 'bbbb'

不用参数化时，将是这样的语句：

insert into TableName(col1, col2) values('aaaa', 'bbbb')

当时有两种意见：
1、使用参数化SQL会提高性能
2、使用参数化SQL会降低性能

周末，我针对这个问题做了一个测试。

一、测试方案

通过ADO.NET操作数据库，数据量大小为10000条记录或更多，操作类型包括插入和更新
先使用参数化SQL执行以上操作，测出花费的时间t1，再使用非参数化SQL执行以上操作，测出花费的时间t2，比较t1和t2的大小
测试数据库为SQL Server 2000和Oracle 8.17

二、测试用例

1.在SQL Server 2000中新建两个相同的表，Authors1,Authors2,表中设置一个主键，除主键外没有其它索引,比较操作将在这两个表上进行。
  注意：因为插入第1－10000条数据和插入第10001－20000条数据的时间是肯定不想同的，因此不能在同一个表中测试

2.分别使用参数化SQL和不使用参数化SQL插入10000条语句，在同一个方法中执行，测试其所花费的时间。为避免第一次打开数据库连接而产生的影响，在执行以上两步操作前，先执行了一个其它的无关操作，以打开数据库连接，不算入测试计时。

3.在Oracle中执行同样的操作

三、测试过程：
1.在SqlServer中创建两张结构完全相同的表
2.编写测试用例代码，.Net访问提供者为 System.Data.SqlClient
3.使用NUnit进行测试，将时间间隔输出到控制台(NUnit中可以显示)，记录此时间
4.清空掉表中记录（仅对插入操作），重复第3步的测试，记录4组数据
5.改用Oracle数据库，重复上面的测试，.Net访问提供者为 System.Data.OracleClient

 

四、测试结果

数据库	操作类型	数据量	参数化平均时间ms	非参数化平均时间ms	百分比
SQL Server 2000	Insert	10000	10785.5088	11176.0704	96.5%
SQL Server 2000	Update	10000	10279.7816	10590.228	97.1%
SQL Server 2000	Insert	100000	264590.4624	262006.7472	100.9%
Oracle 8.17	Insert	10000	27962.7084	31790.7128	87.95%
Oracle 8.17	Update	10000	24029.5528	28240.608	85.1%

 详情请见测试原始记录

五、扩展测试
分别用存储过程和参数化SQL比较，存储过程和非参数化SQL比较
发现SQL Server中，存储过程明显比SQL快百分之十几，而在Oracle中，存储过程的效率高于非参数化SQL，而低与参数化SQL。详情请见测试原始记录。

六、结果分析：
参数化SQL与非参数化SQL在效率方面与具体数据库有关，对于主流数据库，从上面的测试可以看出：
SQL Server 2000使用参数化SQL与非参数化SQL性能几乎没有什么区别，而存储过程的效率则明显高于SQL
Oracle使用参数化SQL的性能要明显高于非参数化SQL，而存储过程的效率虽然高于非参数化SQL，但却比参数化SQL低。
出现以上情况的原因可能是各个数据库的共享池机制不相同，对于Oracle，它把参数值不同的语句当成相同的SQL，当传入不同的参数值时，能从共享池中找到这条SQL，从而不必再次编译，这也是为什么比存储过程还快的原因吧。而SQL Server则把参数值不同的语句也当成不同的SQL，因而传入不同的参数时，总是要重新编译SQL，所以我们几乎看不出什么区别来。
我所作的测试是对同一个表执行同样的操作，SQL语句都是相同的，因此SQL语句的缓存利用率非常高，所以使用参数和不使用参数的差别会比较明显，在实际环境中，重复执行SQL的可能性会低很多，因此不代表实际环境中效率也能提高这么多。