存储过程要怎么写才性能高

[最爱午夜]

以下是存储过程是一个示例：
create Procedure [dbo].[Test]
@OrderInfoID AS INT=-1,
@GroupInfoID as int=-1,
@AccountId AS INT =-1,
@BuyerName NVARCHAR(255)='',
@BuyerAccount NVARCHAR(255)='',
@Address nvarchar(500)='',
@eBayItemNumber NVARCHAR(20)='',
@eBayItemName nvarchar(500)='',
@Sum money=-1,
@SalesRecordNo nvarchar(20)='',
@IsPaid AS CHAR(1)='',
@PaypalTransactionID NVARCHAR(255)='',
@IsSendOff as char(1)='',
@IsMaintainPostage as money=1,
@eBayOrderId nvarchar(20)='',
@PostOrderDateFrom datetime='1900-1-1',
@PostOrderDateTo datetime='1900-1-1'
AS
SELECT oi.*, ao.DifferPrice
FROM table1 oi WITH(NOLOCK)
LEFT JOIN dbo.table2 ao ON ao.OrderGUID = oi.GUID
WHERE (@OrderInfoID = -1 OR OrderInfoID = @OrderInfoID) AND
(@GroupInfoID=-1 OR oi.GroupInfoID=@GroupInfoID) AND
(@AccountId = - 1 OR AccountId = @AccountId) AND
(@IsPaid = '' OR IsPaid = @IsPaid) AND
(@PaypalTransactionID = '' OR PaypalTransactionID = @PaypalTransactionID) AND
(@Sum=-1 OR TotalPrice=@Sum) AND
(@BuyerName='' OR BuyerName like '%'+@BuyerName+'%') AND
(@BuyerAccount='' OR BuyerAccount like '%'+@BuyerAccount+'%') AND
(@Address='' OR Address like '%'+@Address+'%') AND
(@eBayItemNumber='' OR eBayItemNumber like '%'+@eBayItemNumber+'%') AND
(@eBayOrderId='' OR eBayOrderId like '%'+@eBayItemNumber+'%') AND
(@eBayItemName='' OR eBayItemName like '%'+@eBayItemName+'%') AND
(@SalesRecordNo='' OR SalesRecordNo=@SalesRecordNo) AND
(@IsSendOff='' OR IsSendOff=@IsSendOff) AND
(@IsMaintainPostage=1 OR (Postage<=0.1 AND SalePrice<=1)) AND
(@PostOrderDateFrom='1900-1-1' or (PostOrderDate>=@PostOrderDateFrom and PostOrderDate<=@PostOrderDateTo))
ORDER BY PostOrderDate desc
仅仅讨论一下参数带默认值而使用OR的这方面，这样的存储过程性能能高到哪里去？
如果采用拼接字符串的方式，存储过程又很难维护，怎么办？

[zhk383501372]

没有用存储过程，系统经常要调整。数据库查询方面的要求： 1，限制查询条件 2，不管什么查询条件，SQL查询时只抓每页的数据量到客户端，一般10~20笔。

[smile_java]

优化查询SQL语句，存储过程性能也就差不多上去了

[jenly10]

引用 33 楼 smile_java 的回复:

优化查询SQL语句，存储过程性能也就差不多上去了

[--小F--]

换做我写的话 肯定是一大堆动态拼接 一堆IF ELSE去过滤条件了

[Tiger_Zhao]

这种随机查询讲优化就是扯淡，优化出的索引比数据还大，还要不要更新数据了！

[凨行者]

随机查询感觉很无奈 多建几个索引又那么耗资源

[最爱午夜]

引用 44 楼 Tiger_Zhao 的回复:

[quote=引用 42 楼 jack11430 的回复:] [quote=引用 18 楼 luckyrandom 的回复:] 设计问题，不是简单地改善某个SQL的问题 就像你到路边吃个炒粉，花了五块钱，然后就大骂这炒粉不卫生、环境不好，还没美女端菜送饮料。。。 系统开发也是如此，随便请个开发者(通常就是价格便宜)，完成任务了事。。 之后性能、规模等问题出来的，骂声出现。。。

现在抓也不迟啊，怎么也比查不出来强吧，普通的查询就费个半分钟，谁也不想这么差啊。[/quote] 说不定开发人员的怒槽点爆了，故意的——“你敢随意做需求，我就敢让用户体跳脚！”[/quote] 哈哈，服务器宕机怎么办？ 随便点个按钮，半个小时才出来，用户照样跺脚

[专注or全面]

我觉得现在主要是解决性能问题，性能问题应该是写法造成，如果确认是存储过程中的写法造成的，就去优化这种写法，如果不是写法造成的，那就是放在应用程序中写sql也解决不了，我个人感觉不管是哪种方式造成的问题，能不能解决，怎么解决，都应该反馈出来，领导是干嘛的？就是解决问题做决定的，如果领导都做不了主，那你就随波逐流地混吧，多混一天是一天，这种领导跟着也没啥意义 如果你自己私下吭哧吭哧累个半死，解决了还好，解决不了还被黑锅

[Tiger_Zhao]

引用 42 楼 jack11430 的回复:

[quote=引用 18 楼 luckyrandom 的回复:] 设计问题，不是简单地改善某个SQL的问题 就像你到路边吃个炒粉，花了五块钱，然后就大骂这炒粉不卫生、环境不好，还没美女端菜送饮料。。。 系统开发也是如此，随便请个开发者(通常就是价格便宜)，完成任务了事。。 之后性能、规模等问题出来的，骂声出现。。。

现在抓也不迟啊，怎么也比查不出来强吧，普通的查询就费个半分钟，谁也不想这么差啊。[/quote] 说不定开发人员的怒槽点爆了，故意的——“你敢随意做需求，我就敢让用户体跳脚！”

[alexander0729]

引用 4 楼 jack11430 的回复:

这个是我们公司的系统中的存储过程，一开始由于需求人员不严格控制需求，导致用户需要什么就做什么，直接导致数据量百万级，数据就顶不住了，经常死锁阻塞。 我曾经跟开发人员讨论过，这样做程序是不行的，我们这个应用程序不是报表一类的程序，要求的就是效率，可惜无人理会， 我对开发人员说：你见过除报表类的应用程序，有几个选项超过10个的，一般5个左右就行了。同时要求客户必选几个参数用来限制查询的数据量，程序效率立马就上来了。 现在我们的存储过程，超过5s的一大堆，明明不需要并行查询，结果按这种方式写的存储过程，大多数都走并行查询。导致CPU居高不下，CXPACKET，SOS_SCHEDULER_YIELD等待占前两位。 拼SQL又会导致脚本维护困难，不知道怎么办？ 还有就是优化存储过程，我建议他们从逻辑上去优化，这样提升的空间最大，可惜支持少。郁闷啊

首先， 拼装SQL的办法是不可取的， 如果是小系统那就无所谓了， 大系统的话， 绝对不要这么做， 第一是安全性，第二是在保证数据结果正确的前提下，sql语句的优化很难进行， 总不能优化一次就重新编译一下代码吧。 想要提高数据库的效率， 这是一个多方面的话题。 首先：设计层次。 第二：运维的时候。 设计层次就不多说了， 分表，分区，索引，分库，等等吧， 太多了，自己慢慢总结吧 运维： 这个时候，存储过程的好处就可以显示出来了， 在保证输出结果的结构跟以前是同构的，且数据正确的时候，现在就可以在存储过程里优化了。 如果都是通过存储过程来操作数据的， 那么就算你把表结构改了， 或者做了分区表，那又有何不可呢？

[alexander0729]

我们的系统运行了10年了， 存储过程不计其数， 随随便便一个就是上千行的。 现在在维护的时候， 没人敢在代码里面拼装sql 表的旧数据备份， 分区， 分表， 分库等， 都是不错的手段， 你可以自己多看看相关的东西， 这些东西往往没什么共性， 只能在你充分的了解了你现在系统的逻辑的基础上， 自己来动手

[最爱午夜]

引用 46 楼 x_wy46 的回复:

我觉得现在主要是解决性能问题，性能问题应该是写法造成，如果确认是存储过程中的写法造成的，就去优化这种写法，如果不是写法造成的，那就是放在应用程序中写sql也解决不了，我个人感觉不管是哪种方式造成的问题，能不能解决，怎么解决，都应该反馈出来，领导是干嘛的？就是解决问题做决定的，如果领导都做不了主，那你就随波逐流地混吧，多混一天是一天，这种领导跟着也没啥意义 如果你自己私下吭哧吭哧累个半死，解决了还好，解决不了还被黑锅

已经背了黑锅了，现在老的存储过程和SQL语句我不管了， 写新的全部要审核。 但是老的存储过程和SQL引发一个巨大的问题： 并行查询过多。 即使限制了并行度，也没有多大用处，没过几天，CPU占用率又因为并行查询又提升了。

[demonss001]

版大，这是执行计划的截图

[mvsqr2]

同意stephen1315的看法，随机查询尽量少用like，这相当消耗资源 另外，数据库的结构设计也是性能的影响因素，另一方面，硬件平台也要跟上啊。 我是搞syteline报表开发的，原版的SP根本看不到用OR这种写法。人家这么大个系统为什么不用？ 也许是系统设计的时候，MSSQL还不能对OR的并行查询进行优化吧。现在呢？也许可以了，也许只有某些可以。。。 那syteline中是如何做的呢？以字符串为例，如果参数是空或者Null，那么它会用一个叫做LowString的函数，给这个参数赋值。 LowString调用返回N个char(0) 【N是多少要看当前参数类型的长度】 这个是最小值，还有一个最大值，则调用HighString函数，工作原理类似 这样就形成一个Starting和一个Ending字符串变量，于是 WHERE ... AND colum_x BETWEEN @Starting AND @Ending 这样就把OR的问题转为AND的问题，并且避免了拼接SQL 楼主的SP的参数显然大多没有Starting和Ending，但是只要Ending = Starting就行了。希望对楼主有所启发吧。

[---涛声依旧---]

具体问题具体分析了，不管怎么样，如果出现死锁了，不管是DBA、开发人员还是主管都得解决问题才对； 至于放在前台程序去处理还是放在存储过程里，公司应该有规范才对吧 另外，存储过程里不要select * ，须要什么字段就列出什么字段出来，越少越好 单纯就这个存储过程而言，感觉没有什么逻辑业务要处理，纯属程序员为了通用查询而偷懒的行为，个人不建议这样用

[魔者侠心之明星级程序员]

1，很多人纠结这个sp的“or”上面。 2，目测这个sp在真正执行的时候根本就没有“or”，所以“or”不是问题。 3，目测这段逻辑用上面sp的写法跟字符串拼接在性能上没有本质区别，如果拼接字符串不能重用执行计划，那那个sp也同样不行。

[xiaoxiangqing]

语句里一定要有一个限制条件，并且需要过滤大部分数据,这样速度才快

[半睡半醒半人梦]

个人觉得 多用IF ELSE 去做过滤，对性能还是有显著提高的

[最爱午夜]

引用 5 楼 lion_kangaxx 的回复:

LZ不至于那么小看数据库引擎吧，像是@OrderInfoID = -1这种语句写成过程后，在实际运行时，当然是在CPU资源里处理掉的，不会占用数据总线上的时间的。 所以至少楼主你不必为了这句话的效率担忧，你还是从代码结构方面考虑就好了。

引用 12 楼 fredrickhu 的回复:

存储过程的性能也是体现在索引的设计上面。只要索引设置得对，百万条数据的查询也还是能秒杀的。 而且在写语句的过程中，不要害怕IF ELSE这些语句。恰恰就是这些控制语句可以过滤结果集。让结果集尽可能的小才是王道。

引用 19 楼 sz_haitao 的回复:

这种写法，如果没传入参数，对应的条件会被sql引擎优化掉吧 所以 无须担心 影响性能 不过，最好能实际验证一下

如果参数多了，参数不传值，执行计划千奇百怪，本来1S不到的存储过程，给你来个并行查询，然后一堆的执行步骤，最后变成了5S以上，能不坑爹吗？ 现在我也不改了，要么改需求，要么建索引。反正代码不改了。

[outcountry]

分解为多个存储过程怎么样