在1台数据库服务器、9台web服务器的情况下如何使用存储过程与T-SQL语句以及两者区别

[lsd123]

以下根据网上资料和自己的使用经验整理：
存储过程：
优点：
1.执行效率
如果某操作需要大量Transact-SQL代码或需重复执行，存储过程将比Transact-SQL
批代码的执行要快。将在创建存储过程时对其进行分析和优化，并可在首次执行
该过程后使用该过程的内存中版本。每次运行Transact-SQL语句时，都要从客户端
重复发送，并且在SQL Server每次执行这些语句时，都要对其进行编译和优化。
2.增强安全性
a.可以控制对存储过程的访问权限
b.提高代码安全，防止SQL注入
3.减少网络流量
存储过程代码直接存储于数据库中，不会产生大量的T-SQL语句的代码流量。

缺点：
1.可移值性差
由于存储过程将应用程序绑定到SQL Server，因此使用存储过程封装将限制应用程序的可移植性。
2.可扩展性差


在1台数据库服务器、9台web服务器以及在内网安全性要求不是很高的情况下（该系统支持多种数据库）:
1.由于存储过程中有各种逻辑判断以及业务处理大大的增加了数据库服务器的压力

，所以除了实时性要求比较高的，所有与数据库交互的我们都用T-SQL语句。

疑问：用T-SQL语句除了会产生大量的T-SQL语句的代码流量外，最终的T-SQL语句还

是要在数据库服务器执行，跟存储过程相比，能降低多少数据库服务器的压力？
还有产生的大量的T-SQL语句的代码流量会对执行效率有多大影响？


请各位说说
在1台数据库服务器、9台web服务器以及在内网安全性要求不是很高的情况下（该系统支持多种数据库）:
1.你会如何使用存储过程和T-SQL语句？
2.你这样用的原因？

[JonasFeng]

关于存储过程和T-SQL，想必楼主已经看到很多楼上的介绍了。

但是个人感觉，系统效率的高低，尤其是像楼主这种9台WEB服务器的情况，不是取决于是否采用多少存储过程。

而是取决于服务器的负载是否均衡，取决于带宽是否够大，等等因素。

[lsd123]

[Quote=引用 7 楼 jinjazz 的回复:]
web负载均衡和是否使用存储过程是八竿子打不着的关系...
[/Quote]
都用存储过程是不是会增加数据库服务器的压力？
我们的数据库服务器曾经崩溃过

[jinjazz]

不会..

[lsd123]

[Quote=引用 9 楼 JonasFeng 的回复:]
关于存储过程和T-SQL，想必楼主已经看到很多楼上的介绍了。

但是个人感觉，系统效率的高低，尤其是像楼主这种9台WEB服务器的情况，不是取决于是否采用多少存储过程。

而是取决于服务器的负载是否均衡，取决于带宽是否够大，等等因素。
[/Quote]

我们的系统在内网中，所以带宽要求可以不考虑

[lsd123]

[Quote=引用 11 楼 jinjazz 的回复:]
不会..
[/Quote]

为什么不会？
存储过程的处理逻辑不是都在数据库服务器上处理的，
如果用SQL语句的话，这部分的压力不是会交给web服务器？
我很菜，指点下，非常感谢

[lsd123]

在1台数据库服务器、9台web服务器以及在内网安全性要求不是很高的情况下（该系统支持多种数据库）:
1.由于存储过程中有各种逻辑判断以及业务处理大大的增加了数据库服务器的压力，所以除了实时性要求比较高的，所有与数据库交互的我们都用T-SQL语句。


疑问：用T-SQL语句除了会产生大量的T-SQL语句的代码流量外，最终的T-SQL语句还是要在数据库服务器执行，跟存储过程相比，能降低多少数据库服务器的压力？
还有产生的大量的T-SQL语句的代码流量会对执行效率有多大影响？

[hery2002]

1.支持多数据库的话,存储过程不一定合适.嵌入式SQL的灵活性在于可以根据不同的数据库来选择和编写不同的SQL语句已达到支持异构数据库的效果,

2.无论是SP还是嵌入式SQL最终都会在数据库服务器上执行,所以,无论SP或者嵌入式SQL都无法降低数据库服务器的压力,主要是要看SQL语句或者存储过程的优化上面做文章.

3.大量的嵌入式SQL的传入会对网络流量照成影响,比SP只传几个参数的网络流量要大的多.

对于你的问题,需要按照你实际的情况来处理.

[lsd123]

来了这么多的高手，该贴应该被推荐，嘿嘿

无论SP或者嵌入式SQL都无法降低数据库服务器的压力
就这两者比较，嵌入式SQL是不是会对数据库服务器的压力小点？

[hery2002]

按照你所描述的,如果一些计算逻辑和判断关系是在应用端处理的,
那么嵌入式SQL对数据库服务器的压力会小一些.
------------------------------------------------------
也就是说,SP是将你的计算逻辑和判断存储在数据库服务器上执行,
而嵌入式SQL是把这些计算逻辑和判断在应用端执行,
如果有多台WEB服务器,只有一台数据库服务器的话,
将一些逻辑和判断放在应用端会减小数据库服务器的压力.

[nzperfect]

只不过是自己需要找一个平衡点而已..

而且写法的不同，结果也不相同.
比如，不过是简单的判断与数据的增删改，用存储过程要比反复用t-sql高效.

但是如果把N多业务逻辑都放在存储过程里，比如字符判断/正则之类，则会造成数据的压力，造成性能变差,这样的就可以分担给WEB服务器来实现.

[nzperfect]

当然，如果你的数据库硬件相当之强劲，就随你搞了

[lsd123]

在实际项目中如何平衡使用存储过程和SQL语句？有没有什么好的建议？除了上面各位大虾所提的之外
各位在你们实际项目中是如何使用存储过程和SQL语句？都是DBA做的？

[flairsky]

web负载均衡和是否使用存储过程是八竿子打不着的关系...

同意

如果你不介意每次都费劲脑汁写一长串sql语句，那你用t-sql也无妨啊

[lsd123]

[Quote=引用 21 楼 flairsky 的回复:]
web负载均衡和是否使用存储过程是八竿子打不着的关系...

同意

如果你不介意每次都费劲脑汁写一长串sql语句，那你用t-sql也无妨啊
[/Quote]

就因为这个原因而用存储过程？

[人鱼传说]

学习了

[you_tube]

学习

[conan304]

关注

[pbsh]

对于使用存储过程和T-SQL的区别，我做了一些测试，供你参考，也请大家一起讨论。
我的观点是：对于你这种情况，存储过程和T-SQL的区别是服务器端是否会对执行计划进行缓存，即每次是否都需要对于要执行的内容重新编译，这不仅仅取决于是
存储过程还是T-SQL，还取决于你语句中对参数的使用方式。
示例如下：
1、直接使用T-SQL语句
select top 100 * from A
where month_id=200901

month_id值发生变化时，需要重新编译。

2、直接使用的常量参数
CREATE PROC P_Exec1
@month_id INT
AS
select top 100 * from A
where month_id=@month_id

--EXEC P_Exec 200902
执行计划被缓存，参数值发生变化时，不需要重新编译。

3、使用EXEC执行动态定义的T-SQL语句
DECLARE @month_id INT
DECLARE @sql VARCHAR(4000)

SET @month_id=200901
SET @sql=' select top 100 * from A
where month_id='+CAST(@month_id AS VARCHAR)
EXEC (@sql)

@month_id值发生变化时，需要重新编译。

4、使用sp_executesql执行动态定义的T-SQL语句
EXEC sp_executesql
N' select top 100 * from A
where month_id=@month_id',
N'@month_id int',@month_id=200901

执行计划被缓存，参数值发生变化时，不需要重新编译。

5、将动态定义的T-sql语句放入存储过程
CREATE PROC P_Exec
@month_id INT
AS
DECLARE @sql VARCHAR(4000)
SET @sql=' select top 100 * from A
where month_id='+CAST(@month_id AS VARCHAR)
EXEC (@sql)

--EXEC P_Exec 200901

@month_id值发生变化时，需要重新编译。

具体的测试数据请自行创建。

结论：考虑到查询计划的重用性，相同定义的存储过程优于查询语句，sp_executesql优于exec()。

一家之言，请各位高手指点。

[lsd123]

[pbsh]

补充一下，我判断是否重新编译的方法是使用 Set Statistics time 选项，在语句相对比较复杂时，表现更为明显。