介绍:
相比inline sql,很多人更喜欢使用存储过程,为何?可能很多人会说:“因为存储过程是预编译并且缓存了的,因此比原生sql会更快”
这句话某个时候是正确的,先解释一下依据:sql第一次执行的时候,发生了三件事情
1. Sql 语法错误检查
2. 最好的执行计划来执行sql(例如选择使用聚簇索引,或者非聚簇索引等)
3.执行sql
上述流程也适合存储过程第一次执行,只不过存储过程的执行计划会被缓存到内存中,所以,当存储过程下次再被执行的时候,就会从缓存中直接取出缓存的计划执行,而不再执行上述1 、2 两步,因此性能得以提高;而原生内嵌sql(不知道咋翻译,反正就是你在app中定义的sql,new sqlcommand("select * from user" +......))则必须重复上述三个动作。
但是,上述情况的适用范围随着sqlserver的发展而有所变化,上述情况只发生在老版本的Sqlserver上,从sqlserver2005之后,系统一视同仁,存储这个优势没了,都会被预编译和缓存。
自圆其说:
写个简单应用程序,包含以下两个调用
SqlCommand objCommand = new SqlCommand("Select * from Users where UserName='" + UserName + "' and Password='" + Password + "'", objConnection);存储过程 “
sp_login
”.SqlCommand objCommand = new SqlCommand("sp_Login", objConnection); objCommand.Parameters.Add(new SqlParameter("UserName", UserName)); objCommand.Parameters.Add(new SqlParameter("Password", Password)); objCommand.CommandType = CommandType.StoredProcedure;
运行这两个方法时,需要捕获两个事件:CacheHit 、 CacheInsert
先运行存储过过程,看结果:
运行sql,看结果:
看起来都缓存了,结果都一样,没差别
骗子,换换数据看看?
参数变了,果然没有使用原来的缓存,而是创建了新的缓存:
怎么办?调整一下代码,让其支持参数化:
SqlCommand objCommand = new SqlCommand( "Select * from Users where UserName=@userName and Password=@Password", objConnection); objCommand.Parameters.AddWithValue("@userName", UserName); objCommand.Parameters.AddWithValue("@Password", Password);再次执行:使用了缓存内容
所以,简单来说存储过程和参数化的内嵌sql,性能是一样的
嗯,但是网络负载呢?
一个只传送Sp_login,一个要传送Select * from Users where UserName=@UserName andPassword=@Password'这么长,明显第一个有优势么?但从这方面来讲,
没必要吧。
我也偏向使用存储过程
但原因不是性能,而是基于安全性和可维护性方面
下面几点是优于inline sql(咋翻译)的地方:
抽象分离
代码不用再考虑诸如字段名、表名的东西了,将减少改数据库时对代码影响
安全
这或许是最好的理由了,你可以为存储过程分配可执行权限
维护方便
出了问题,直接在存储过程中解决,不必重新发布编译exe/dll了
集中调整
若有sql性能问题,可以由DBA单独隔离搞定
游标,临时表
如有复杂的诸如if else ,cursor等东西,还是存储过程用起来顺手方便
翻完了。题外话(记得08年做美国heardbeat项目,人家就是不让用存储过程,原因好像说是不安全,风险分散了,不容易控制。)