先站在应用角度说说它们区别

1、 直接拼SQL

就像大家了解那样直接拼SQL带来了SQL注入攻击带来了拼时些许性能损失但是拼不用添加SqlParameter会少写很多代码——很多人喜欢直接拼也许就这点这种做法会把你拼好SQL原样直接发送到DB服务器去执行(注意类似”exec yourproc ‘param1’, 12”语句不在此范畴这是存储过程种方式)

2、 参数化SQL

所谓“参数化SQL”就是在应用侧设置SqlCommand.CommandText时候使用参数(如:@param1)然后通过SqlCommand.Parameters.Add

来设置这些参数值这种做法会把你准备好命令通过sp_executesql系统存储过程来执行通过参数化SQL和直接拼SQL相比最直接好处就是没有SQL注入攻击了

3、 存储过程

直接存储过程其实和参数化SQL非常相似唯本质区别在于你发送到DB服务器指令不再是sp_executesql而是直接存储过程而已



很多人非常非常厌恶在应用中使用存储过程而宁愿使用拼SQL或者参数化SQL理由是它们提供了更好灵活性——这个理由其实非常非常发指(俺现在喜欢上这个词了)

现在做设计般都是从上到下来重心都在业务逻辑上传说中领域模型设计完测试用例都通过的后才会考虑数据持久化方式数据持久化是系统部分但绝对不是最重要部分设计应该围绕业务逻辑开展持久化应该仅仅是个附件至少高层应用应该尽可能不关心处于最底层物理存储结构(如:表)和数据持久、反持久方式(是拼SQL

还是存储过程)所以用不用存储过程根本不重要很多人害怕存储过程其实是害怕存储过程中包括业务逻辑——真实情况是如果存储过程中包含了业务逻辑那定最初需求分析不够导致用例提取不足导致测试用例覆盖不够导致领域模型设计不充分要不就是偷懒



=

站在DB角度讨论它们区别主要从cpu、内存方面来考虑其他诸如安全性msdn上都有google也能拿到堆资料不再赘述

首先是查询计划

SQL编译完条SQL的后会把它缓存Cache起来(可以通过sys.syscacheobjects系统视图查看)以后再有相同查询过来(注意sys.syscacheobjects视图中sql字段和它存储东西完全样才能称为“相同查询”)会直接使用缓存Cache而不再重新编译

Ø 存储过程伊只编译遍(如果没有指定with recompile选项话如果指定了根本就不会生成计划缓存Cache)



Ø 参数化SQL和存储过程基本样只要是相同查询也都是只编译次以后重用(当然指定了option(recompile)除外)这里不得不提.NET SqlClient组件个龌龊:如果你参数中包含varchar或者char类型参数你在Parameters.Add时候又没有指定长度它都会根据你实际传入串长度(假设是n)给你重新定义成nvarchar(n)如:select * from mytable where col1 = @p1你设置@p1为’123456’实际传到sql这边命令是:exec sp_executesql N\'select * from mytable where col1 = @p1\',N\'@p1 nvarchar(6)\',@p1=N\'123456\'[Page]这样系统缓存Cache中实际存储sql是:(@p1 nvarchar(6))select * from mytable where col1 = @p1

看到了吧？如果你输入参数变动比较多那么看起来同样条语句会被编译很多次在缓存Cache中存储很多份cpu和内存都浪费了这也是在写有效率SQL查询IV中建议使用最强类型参数匹配原因的

Ø 拼SQL到这里不说大家也猜出来拼SQL要浪费大量cpu进行编译浪费大量缓存Cache空间来存储只用次查询计划



服务器物理内存有限SQLServer缓存Cache空间也有限有限空间应该被充分利用通过性能计数器SQL Server:Buffer Manager\\Buffer Cache hit ratio来观察缓存Cache命中率如果它小于百分的90你就得研究研究了关注把诸如sys.dm_os_memory_cache_counters、sys.dm_os_memory_cache_entries、sys.dm_os_memory_cache_hash_tables、sys.syscacheobjects等视图基本可以确定问题出在哪儿



cpu方面需要关注 3个性能计数器:SQLServer:SQL Statistics\\Batch Requests/Sec、SQLServer:SQL Statistics\\\'; mso-fareast-font-family: 宋体; mso-bidi-font-family: \'Times New Roman\'; mso-font-kerning: 12.0pt\"> SQLCompilations/sec、SQLServer:SQL Statistics\\\'; mso-fareast-font-family: 宋体; mso-bidi-font-family: \'Times New Roman\'; mso-font-kerning: 12.0pt\"> SQL Re-Compilations/sec如果compilations数目超过batch请求数目百分的10或者recompilations数目超过compilations数目百分的10那基本可以介绍说明cpu消耗了太多在编译查询计划上面

最后我建议是:
1、DB中所有操作都尽可能使用存储过程哪怕只是句简单select
2、鄙视拼SQL

btw:MSDN中对拼SQL称为\"ad hoc\"呵呵


补充点介绍说明下N\'@p1 nvarchar(6)\'换成N\'@p1 nvarchar(30)\'会重新编译:)
代码如下:


1\" align=top>//\">
2\" align=top>SqlCommandcmd=SqlCommand(

\"select*frommytwheredata=@d\",conn);
3\" align=top>cmd.Parameters.Add(SqlParameter(\"@d\",\"1234567890\"));
4\" align=top>cmd.ExecuteNonQuery;
5\" align=top>
6\" align=top>cmd=SqlCommand(\"select*frommytwheredata=@d\",conn);
7\" align=top>cmd.Parameters.Add(SqlParameter(\"@d\",\"123\"));
8\" align=top>cmd.ExecuteNonQuery;
9\" align=top>

执行完这段可以观察观察sys.syscacheobjects:


上图中5、6行标记了缓存Cache查询计划
=

另外再来说个更应该注意地方:


1\" align=top>//\">
2\" align=top>SqlCommandcmd=SqlCommand(\"select*frommytwheredata=@d\",con);
3\" align=top>cmd.Parameters.Add(SqlParameter(\"@d\",\"1234567890\"));
4\" align=top>cmd.ExecuteNonQuery;
5\" align=top>


6\" align=top>cmd=SqlCommand(\"select*frommytwheredata=@d\",con);
7\" align=top>cmd.Parameters.Add(SqlParameter(\"@d\",\"123\"));
8\" align=top>cmd.ExecuteNonQuery;
9\" align=top>
10\" align=top>cmd=SqlCommand(\"select*frommytwheredata=@a\",con);
11\" align=top>cmd.Parameters.Add(SqlParameter(\"@a\",\"123\"));
12\" align=top>cmd.ExecuteNonQuery;
13\" align=top>

注意上述代码中最后次操作我把@d参数重命名成了@a然后再来看看sys.syscacheobjects里面有啥:[Page]


注意第 6行