您的位置:新葡亰496net > 网络数据库 > SQL语句优化,NOCOUNT优化存储过程

SQL语句优化,NOCOUNT优化存储过程

发布时间:2019-07-28 07:40编辑:网络数据库浏览(64)

    已经有快2个月没有更新博客了,实在是因为最近发生了太多的事情,辞了工作,在湘雅医院待了一个多月,然后又新换了工作......

      问题是这样的,在VB中执行存储过程,想讀取存儲過程返回的結果集,存儲過程用到臨時表,在VB中調用存儲過程的時候就出現3704的錯誤,提示“對象關閉時,不允許操作”,在網上找了很多信息,都不能解決問題,無疑間在百度知道上看到一個人提的問題,有人回答,有一條很符合我的現象,果然解決了哈哈,轉載主要原因有一下:
      1、数据库对象连接被关闭了,这肯定会报错的,当然也不能输出任何结果的,这时只需注意“先使用、后关闭”就行了,解决这样的问题就是暂时先不要关闭数据库连接就行了。

    1、在查询中不要使用“select *”  检索不必要的列会带来额外的系统开销,有句话叫做“该省的则省”;

    转自:

    在平时的工作中,Sqlserver中许多知识点是经常用到的,但是有时候我们往往忽略了它们,在过去的一年里,一直使用的是Mysql,现在又开始接触Sqlserver了,所以就把一些常用又容易忽视的Sqlserver知识点总结一点,以便备忘之用。

      2、返回记录为空时,也会报错。如下面的代码

    2、在select清单中避免不必要的列,在连接条件中避免不必要的表;

    每次我们在使用查询分析器调试SQL语句的时候,通常会看到一些信息,提醒我们当前有多少个行受到了影响,这是些什么信息?在我们调用的时候这些信息有用吗?是否可以关闭呢?

    所有的操作都将基于Northwind数据库来进行操作。

    新葡亰496net 1新葡亰496net 2代码

    3、不要在子查询中使用count()求和执行存在性检查

    答案是这些信息在我们的客户端的应用程序中是没有用的,这些信息是存储过程中的每个语句的DONE_IN_PROC 信息。

    注意:当采用附加Northwind数据库的方式进行数据库迁移的时候,可能会出现错误:“Access is denied”。解决方案: right-click the SQL Server  Management Studio icon and select  Run as administrator。

    1     sql = select * from dbcn where 状态='0'
    2     Set rs = conn.Execute(sql)
    3     If Not rs.EOF Then
    4         For m = 1 To rs.RecordCount
    5             List_status.AddItem rs(1) & ( & rs(2) & ): & rs(4)
    6             rs.MoveNext
    7         Next m
    8     End If

    4、避免使用两个不同类型的列进行表的连接

    我们可以利用SET NOCOUNT 来控制这些信息,以达到提高程序性能的目的。

    数据在线Mock地址:

     

    5、避免死锁

    MSDN中帮助如下:
     
    SET NOCOUNT
    使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。

    SET NOCOUNT ON 介绍

    在存储过程中,经常用到SET NOCOUNT ON;

    作用:阻止在结果集中返回显示受T-SQL语句或则usp影响的行计数信息。
    当SET NOCOUNT ON 时候,不返回计数,当SET NOCOUNT OFF时候,返回计数。

    当SET NOCOUNT ON 时候,会更新@@RowCount,但是不向客户端发送存储过程每个语句的DONE_IN_proc消息。

    如果存储过程中包含一些并不返回实际数据的语句,使用SET NOCOUNT ON时,网络通信流量便会大量减少,可以显著提高应用程序性能。

    SET NOCOUNT 指定的设置只在执行或运行时候生效,分析时候不生效。

    示例:

    USE Northwind
    GO
    SET NOCOUNT OFF;
    SELECT TOP 5 OrderDate FROM Orders
    GO
    

    执行结果如下:

    新葡亰496net 3新葡亰496net 4

    USE Northwind
    GO
    SET NOCOUNT ON;
    SELECT TOP 5 OrderDate FROM Orders
    GO
    

    执行结果如下:

    新葡亰496net 5新葡亰496net 6

    如果没有数据的话,这里也会提示这样的错误,将查询代码放到SQL查询分析器里运行一下,果然没有数据,呵呵。当然这样的错误可以采用一些方法排除或过滤的。

    6、避免使用count(*)获得表的记录数

    语法
    SET NOCOUNT { ON | OFF }

    Go 介绍

    如果只是执行一条语句,有没有GO都一样。

    如果多条语句之间用GO分隔开就不一样了。

    每个被GO分隔的语句都是一个单独的事务,一个语句执行失败不会影响其它语句执行。

    GO 不是 Transact-SQL 语句;而是可为  SQL Server 查询分析器识别的命令。

    如果你的SQL过长的时候,就要写GO,或者有一些语句,它只能是第一句操作的,在之前你也得写 GO ,GO的意思是分批处理语句,有加这个 GO ,就执行GO 行的代码,执行后再执行接下来的代码。

    像以下这种情况下就要用到GO ,以达到分批处理数据的目的,否则将会报错。

    IF EXISTS(SELECT 1 FROM sys.views WHERE name='View_OrderInfo')
    DROP VIEW View_OrderInfo
    create view View_OrderInfo
    as
    select c.ContactName,c.CompanyName,o.OrderDate,o.ShipName,o.OrderID,o.Freight from [Orders] o inner join Customers c on o.CustomerID=c.CustomerID
    

    会报错

    新葡亰496net 7

    必须是:

    IF EXISTS(SELECT 1 FROM sys.views WHERE name='View_OrderInfo')
    DROP VIEW View_OrderInfo
    GO
    create view View_OrderInfo
    as
    select c.ContactName,c.CompanyName,o.OrderDate,o.ShipName,o.OrderID,o.Freight from [Orders] o inner join Customers c on o.CustomerID=c.CustomerID
    

      3、这就是我这次所遇到的问题的中心了,我们将目光转向存储过程。的确,在SQL查询分析器里,存储过程是正常运行,而且不会报错,也有数据。但仔细想想,因为在存储过程可能包含了有关SQL语句影响行数的信息,这样一来也就可能会导致这样的情况。
      打开存储过程,在begin后面加上一句代码:set  nocount  on,屏蔽掉这些信息。OK,VB中的程序巳经能够正常运行了,而且数据也是正常的。

    (1)为了获得表中的记录数,我们通常使用下面的SQL 语句: SELECT COUNT(*) FROM dbo.orders 这条语句会执行全表扫描才能获得行数。 (2)但下面的SQL 语句不会执行全表扫描一样可以获得行数: SELECT rows FROM sysindexes

    注释
    当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。当 SET NOCOUNT 为 OFF 时,返回计数。

    select count(*)  count(1) count(2) count('a') 之间的区别

    count(*SQL语句优化,NOCOUNT优化存储过程。):找表中最短的列进行统计行数

    count(1) count(2) count('a'):对常数列进行统计行数。它们的执行方式是一样的,没有任何区别。

    很显然采用count(1) count(2) count('a')的方式,效率会更高,因为count(*)会先去算出最短的列,然后再去统计。虽然现在的Sqlserver查询分析器自动会帮我们做一些优化,但是我们必须知道它们的实现原理。

      顺便解释一下吧,打开查询分析器中的帮助,查找一下有关set nocount on的信息,我们会找到这些东西:
    ====================================
    新葡亰496net,  SET NOCOUNT
      使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。
      语法
      SET NOCOUNT { ON | OFF }
      注释
      当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。

    WHERE id = OBJECT_ID('dbo.Orders') AND indid < 2

    即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。

    WITH (NOLOCK)

    缺点:

      1.会产生脏读

      2.只适用于select查询语句

    优点:

      1.有些文章说,加了WITH (NOLOCK)的SQL查询效率可以增加33%。

      2.可以用于inner join 或者left join等语句

    脏读: 一个用户对一个资源做了修改,此时另外一个用户正好读取了这条被修改的记录,然后,第一个用户放弃修改,数据回到修改之前,这两个不同的结果就是脏读。

    详细内容:

      要提升SQL的查询效能,一般来说大家首先会考虑建立索引(index)。其实除了index的建立之外,当我们在下SQL Command时,在语法中加一段WITH (NOLOCK)可以改善在线大量查询的环境中数据集被LOCK的现象藉此改善查询的效能。

    不过有一点千万要注意的就是,WITH (NOLOCK)的SQL SELECT有可能会造成Dirty Read(脏读)。

    例如:

    SELECT o.OrderID,o.OrderDate,o.Freight,d.Quantity,d.UnitPrice
    FROM [dbo].[Orders] o WITH (NOLOCK)
    JOIN [dbo].[Order Details] d WITH (NOLOCK)
    ON o.OrderID=d.OrderID
    

    DELETE、INSERT、UPDATE这些需要transaction的指令就不能使用WITH (NOLOCK)。

    加了WITH (NOLOCK)即告诉SQL Server,我们的这段SELECT指令无需去考虑目前table的transaction lock状态,因此效能上会有明显的提升,而且数据库系统的Lock现象会有明显的减少(包含Dead Lock)。

    当使用NoLock时,它允许阅读那些已经修改但是还没有交易完成的数据。因此如果有需要考虑transaction事务数据的实时完整性时,使用WITH (NOLOCK)就要好好考虑一下。
    如果不需考虑transaction,WITH (NOLOCK)或许是个好用的参考。

      当 SET NOCOUNT 为 OFF 时,返回计数。
      即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。
      当 SET NOCOUNT 为 ON 时,将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用microsoft® SQL Server™ 提供的实用工具执行查询时,在 Transact-SQL 语句(如 SELECT、INSERT、UPDATE 和 DELETE)结束时将不会在查询结果中显示nn rows affected。
      如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。
    SET NOCOUNT 设置是在执行或运行时设置,而不是在分析时设置。
    权限
      SET NOCOUNT 权限默认授予所有用户。
      示例
      下例在 osql 实用工具或 SQL Server 查询分析器中执行时,可防止显示有关受影响的行数的信息。

    7、避免使用动态SQL。(1)动态SQL 难以调试和故障诊断;       (2)如果用户向动态SQL 提供了输入,那么可能存在SQL 注入风险。

    当 SET NOCOUNT 为 ON 时,将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft SQL Server 提供的实用工具执行查询时,在 Transact-SQL 语句(如 SELECT、INSERT、UPDATE 和 DELETE)结束时将不会在查询结果中显示"nn rows affected"。

    Sqlserver高效分页

    在SQLserver2012之前,分页我们一般是使用ROW_NUMBER()。以获取第11条到第50条数据为例:

    ROW_NUMBER()的实现方式:

    select * from (
        select *, ROW_NUMBER() OVER(Order by a.OrderID DESC ) AS RowNumber from Orders as a
      ) as b
      where RowNumber BETWEEN 11 and 50 
    

    SQLserver2012中OFFSET & FETCH实现方式:

    SELECT o.* FROM dbo.Orders o
    ORDER BY o.OrderID DESC OFFSET 10 ROWS FETCH NEXT 40 ROWS ONLY
    

    OFFSET & FETCH实现方式 更加精简更加高效。

    使用 OFFSET-FETCH 中的限制

    • ORDER BY 是使用 OFFSET 和 FETCH 子句所必需的。
    • OFFSET 子句必须与 FETCH 一起使用。永远不能使用 ORDER BY … FETCH。
    • TOP 不能在同一个查询表达式中与 OFFSET 和 FETCH 一起使用。
    • OFFSET/FETCH 行计数表达式可以是将返回整数值的任何算术、常量或参数表达式。该行计数表达式不支持标量子查询新葡亰496net 8新葡亰496net 9

      ALTER PROCEDURE getLightningSendOverTimeOrder --闪电送到期存储过程
      (@OverTimeMinutes INT,--超时时间阈值
      @GetLasterDay int --取最近多少天的订单
      )
      As
      DECLARE @sql NVARCHAR(MAX);
      BEGIN
      set nocount on;
      set @Sql=
      N'WITH    OrderInfo
                --查询近一天的,处理超时的闪电送订单
                AS ( SELECT   ID ,
                              CommunityId ,
                              SubOrderNumber
                     FROM     dbo.[Order] WITH ( NOLOCK )
                     WHERE    DATEDIFF(day, CreatedOn, GETDATE()) <= @GetLasterDay
                              AND DATEDIFF(mi, CreatedOn, ModifiedOn) > @OverTimeMinutes
                              AND LightningSend = 1
                              AND IsDeleted = 0
       --待付款(商品订单),待确认(商品订单),待发货(商品订单),待收货(商品订单)
                              AND OrderStatusId IN (
                              ''C2EE784F-F29B-4E18-8D73-761264339005'',
                              ''FA1A31DF-3855-41BB-9F5C-A09F3AB4C408'',
                              ''ACF190B2-0CA2-43A0-8E6B-70DF8521F4C5'',
                              ''15F06407-C82C-4CA8-984E-37FB3BD9963C'' )
                   ),
             SmsOrder
                --没有发送过信息的订单
                AS ( SELECT   o.ID ,
                              o.CommunityId ,
                              o.SubOrderNumber
                     FROM     OrderInfo o WITH ( NOLOCK )
                              where not EXISTS(SELECT 1 FROM  [dbo].[Sms_LightningSendOrderOverTime] lso
                              WITH ( NOLOCK ) WHERE o.SubOrderNumber = lso.SubOrderNumber)                      
                   ),
              SmsOrderInfo
                AS ( SELECT   
                              so.CommunityId ,
                              so.SubOrderNumber ,
                              p.PublisherId ,
                              c.ConvenienceStoreID ,
                              oi.ProductId ,
                              p.CategoryId ,
                              p.ProcessType
                     FROM     SmsOrder AS so WITH ( NOLOCK )
                              INNER JOIN Community c WITH ( NOLOCK ) ON so.CommunityId = c.ID
                              INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID = oi.OrderId
                              INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId = p.Id
                                                                    AND p.LightningSend = 1
                                                                     AND p.ProcessType != 1 -- 便利店
                   ),
                  MerchantSmsOrderInfo
                AS ( SELECT   
                              so.CommunityId ,
                              so.SubOrderNumber ,
                              p.PublisherId ,
                              c.ConvenienceStoreID ,
                              oi.ProductId ,
                              p.CategoryId ,
                              p.ProcessType
                     FROM     SmsOrder AS so WITH ( NOLOCK )
                              INNER JOIN Community c WITH ( NOLOCK ) ON so.CommunityId = c.ID
                              INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID = oi.OrderId
                              INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId = p.Id
                                                                    AND p.LightningSend = 1
                                                                    AND p.ProcessType = 1 -- 商家
                   ),
              ResultInfo
              AS ( 
          SELECT   usr.Id AS UserId ,
                              soi.SubOrderNumber ,
                              usr.PhoneNumber
                     FROM     SmsOrderInfo AS soi
                              INNER JOIN dbo.[User] AS usr WITH ( NOLOCK ) ON soi.ConvenienceStoreID = usr.OrgId
                              INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId = org.Id
                                                                    AND org.Levels = 2 --便利店
                              INNER JOIN OrderDistributaryCategoryRelation AS odcr
                              WITH ( NOLOCK ) ON usr.Id = odcr.MerchantUserId
                                                 AND odcr.CategoryType = 1
                                                 AND odcr.SellerId = soi.ConvenienceStoreID
                                                 AND odcr.CategoryId = soi.CategoryId
                              INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id = odb.MerchantUserId
                                                                    AND odb.SellerId = soi.ConvenienceStoreID
                              INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON usr.Id = odbr.MerchantUserId
                                                                    AND odbr.MerchantId = soi.ConvenienceStoreID
                     UNION ALL
                     SELECT   u.Id AS UserId ,
                              soi.SubOrderNumber ,
                              u.PhoneNumber
                     FROM     dbo.[User] u WITH ( NOLOCK )
                              INNER JOIN OrderDistributary odb WITH ( NOLOCK ) ON u.Id = odb.MerchantUserId
                                                                  AND odb.IsDefault = 1
                              INNER JOIN SmsOrderInfo soi WITH ( NOLOCK ) ON odb.SellerId = soi.ConvenienceStoreID
                   ------------------------------商家-----------------------------
                     UNION ALL
                     SELECT   usr.Id AS UserId ,
                              soi.SubOrderNumber ,
                              usr.PhoneNumber
                     FROM     MerchantSmsOrderInfo AS soi
                              INNER JOIN dbo.[User] AS usr WITH ( NOLOCK ) ON soi.ConvenienceStoreID = usr.OrgId
                              INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId = org.Id
                                                                    AND org.Levels = 1 --商家
                              INNER JOIN OrderDistributaryCategoryRelation AS odcr
                              WITH ( NOLOCK ) ON usr.Id = odcr.MerchantUserId
                                                 AND odcr.CategoryType = 1
                                                 AND odcr.CategoryId = soi.CategoryId
                              INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id = odb.MerchantUserId
                                                                    AND odb.SellerId = soi.PublisherId
                              INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON odcr.MerchantUserId = odbr.MerchantUserId
                                                                    AND odbr.MerchantId = soi.ConvenienceStoreID
                     UNION ALL
                     SELECT   u.Id AS UserId ,
                              soi.SubOrderNumber ,
                              u.PhoneNumber
                     FROM     MerchantSmsOrderInfo soi WITH ( NOLOCK )
                              INNER JOIN OrderDistributary odb WITH ( NOLOCK ) ON odb.SellerId = soi.PublisherId
                                                                  AND odb.IsDefault = 1
                                                                  AND odb.IsDeleted = 0
                              INNER JOIN dbo.[User] u WITH ( NOLOCK ) ON u.Id = odb.MerchantUserId
                   )
                  SELECT  r.SubOrderNumber ,
                  r.UserId,
                  r.PhoneNumber
          FROM    ResultInfo r '
      exec sp_executesql @Sql,N'@OverTimeMinutes int,@GetLasterDay int',@OverTimeMinutes,@GetLasterDay;
      SET NOCOUNT OFF;
      END
      

      View Code

    自己写的一个存储过程例子

    新葡亰496net 10新葡亰496net 11

    --------------------------------------创建闪电送存储过程-----------------------------------
    IF EXISTS(Select Name From Sysobjects Where Name='getLightningSendOverTimeOrder' And Xtype='P')
    DROP PROCEDURE [dbo].getLightningSendOverTimeOrder
    GO
    CREATE PROCEDURE getLightningSendOverTimeOrder --闪电送到期存储过程
    (@OverTimeMinutes INT,--超时时间阈值
    @GetLasterDay int --取最近多少天的订单
    )
    As
    DECLARE @sql NVARCHAR(MAX);
    BEGIN
    set nocount on;
    set @Sql=
    N'WITH    OrderInfo
              --查询近一天的,处理超时的闪电送订单
              AS ( SELECT   ID ,
                            CommunityId ,
                            SubOrderNumber
                   FROM     dbo.[Order] WITH ( NOLOCK )
                   WHERE    DATEDIFF(day, CreatedOn, GETDATE()) <= @GetLasterDay
                            AND DATEDIFF(mi, CreatedOn, ModifiedOn) > @OverTimeMinutes
                            AND LightningSend = 1
                            AND IsDeleted = 0
     --待付款(商品订单),待确认(商品订单),待发货(商品订单),待收货(商品订单)
                            AND OrderStatusId IN (
                            ''C2EE784F-F29B-4E18-8D73-761264339005'',
                            ''FA1A31DF-3855-41BB-9F5C-A09F3AB4C408'',
                            ''ACF190B2-0CA2-43A0-8E6B-70DF8521F4C5'',
                            ''15F06407-C82C-4CA8-984E-37FB3BD9963C'' )
                UNION ALL
    --状态一直是待支付状态的
    SELECT ID ,
                            CommunityId ,
                            SubOrderNumber
                   FROM     dbo.[Order] WITH ( NOLOCK )
                   WHERE    DATEDIFF(day, CreatedOn, GETDATE()) <=@GetLasterDay
                            AND LightningSend = 1
                            AND IsDeleted = 0
                            AND (OrderStatusId =''ACF190B2-0CA2-43A0-8E6B-70DF8521F4C5'' AND DATEDIFF(mi, CreatedOn, GETDATE()) >@OverTimeMinutes)
                 ),
           SmsOrder
              --需要发送过信息的订单
              AS ( SELECT   o.ID ,
                            o.CommunityId ,
                            o.SubOrderNumber
                   FROM     OrderInfo o WITH ( NOLOCK )
                            where not EXISTS(SELECT 1 FROM  [dbo].[Sms_LightningSendOrderOverTime] lso
                            WITH ( NOLOCK ) WHERE o.SubOrderNumber = lso.SubOrderNumber)                      
                 ),
            SmsOrderInfo
              AS ( SELECT   
                            so.CommunityId ,
                            so.SubOrderNumber ,
                            p.PublisherId ,
                            c.ConvenienceStoreID ,
                            oi.ProductId ,
                            p.CategoryId ,
                            p.ProcessType
                   FROM     SmsOrder AS so WITH ( NOLOCK )
                            INNER JOIN Community c WITH ( NOLOCK ) ON so.CommunityId = c.ID
                            INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID = oi.OrderId
                            INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId = p.Id
                                                                  AND p.LightningSend = 1
                                                                   AND p.ProcessType != 1 -- 便利店
                 ),
                MerchantSmsOrderInfo
              AS ( SELECT   
                            so.CommunityId ,
                            so.SubOrderNumber ,
                            p.PublisherId ,
                            c.ConvenienceStoreID ,
                            oi.ProductId ,
                            p.CategoryId ,
                            p.ProcessType
                   FROM     SmsOrder AS so WITH ( NOLOCK )
                            INNER JOIN Community c WITH ( NOLOCK ) ON so.CommunityId = c.ID
                            INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID = oi.OrderId
                            INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId = p.Id
                                                                  AND p.LightningSend = 1
                                                                  AND p.ProcessType = 1 -- 商家
                 ),
            ResultInfo
            AS ( 
        SELECT   usr.Id AS UserId ,
                            soi.SubOrderNumber ,
                            usr.PhoneNumber
                   FROM     SmsOrderInfo AS soi
                            INNER JOIN dbo.[User] AS usr WITH ( NOLOCK ) ON soi.ConvenienceStoreID = usr.OrgId
                            INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId = org.Id
                                                                  AND org.Levels = 2 --便利店
                            INNER JOIN OrderDistributaryCategoryRelation AS odcr
                            WITH ( NOLOCK ) ON usr.Id = odcr.MerchantUserId
                                               AND odcr.CategoryType = 1
                                               AND odcr.SellerId = soi.ConvenienceStoreID
                                               AND odcr.CategoryId = soi.CategoryId
                            INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id = odb.MerchantUserId
                                                                  AND odb.SellerId = soi.ConvenienceStoreID
                            INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON usr.Id = odbr.MerchantUserId
                                                                  AND odbr.MerchantId = soi.ConvenienceStoreID
                   UNION ALL
                   SELECT   u.Id AS UserId ,
                            soi.SubOrderNumber ,
                            u.PhoneNumber
                   FROM     dbo.[User] u WITH ( NOLOCK )
                            INNER JOIN OrderDistributary odb WITH ( NOLOCK ) ON u.Id = odb.MerchantUserId
                                                                AND odb.IsDefault = 1
                            INNER JOIN SmsOrderInfo soi WITH ( NOLOCK ) ON odb.SellerId = soi.ConvenienceStoreID
                 ------------------------------商家-----------------------------
                   UNION ALL
                   SELECT   usr.Id AS UserId ,
                            soi.SubOrderNumber ,
                            usr.PhoneNumber
                   FROM     MerchantSmsOrderInfo AS soi
                            INNER JOIN dbo.[User] AS usr WITH ( NOLOCK ) ON soi.ConvenienceStoreID = usr.OrgId
                            INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId = org.Id
                                                                  AND org.Levels = 1 --商家
                            INNER JOIN OrderDistributaryCategoryRelation AS odcr
                            WITH ( NOLOCK ) ON usr.Id = odcr.MerchantUserId
                                               AND odcr.CategoryType = 1
                                               AND odcr.CategoryId = soi.CategoryId
                            INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id = odb.MerchantUserId
                                                                  AND odb.SellerId = soi.PublisherId
                            INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON odcr.MerchantUserId = odbr.MerchantUserId
                                                                  AND odbr.MerchantId = soi.ConvenienceStoreID
                   UNION ALL
                   SELECT   u.Id AS UserId ,
                            soi.SubOrderNumber ,
                            u.PhoneNumber
                   FROM     MerchantSmsOrderInfo soi WITH ( NOLOCK )
                            INNER JOIN OrderDistributary odb WITH ( NOLOCK ) ON odb.SellerId = soi.PublisherId
                                                                AND odb.IsDefault = 1
                                                                AND odb.IsDeleted = 0
                            INNER JOIN dbo.[User] u WITH ( NOLOCK ) ON u.Id = odb.MerchantUserId
                 )
                SELECT  r.SubOrderNumber ,
                r.UserId,
                r.PhoneNumber
        FROM    ResultInfo r '
    exec sp_executesql @Sql,N'@OverTimeMinutes int,@GetLasterDay int',@OverTimeMinutes,@GetLasterDay;
    SET NOCOUNT OFF;
    END
    
    
    GO
    

    View Code

    demo2:分页存储过程

    新葡亰496net 12新葡亰496net 13

    ------------------------------------------创建我的优惠券存储过程 created by zouqj-2017-3-1-----------------------------------
    IF EXISTS(Select Name From Sysobjects Where Name='usp_GetMyCouponsList' And Xtype='P')
    DROP PROCEDURE usp_GetMyCouponsList
    GO
    CREATE PROC usp_GetMyCouponsList
    (
    @PageIndex int, --页码
    @PageSize int, --
    @hostName nvarchar(100),--服务器地址
    @MemberID UNIQUEIDENTIFIER, --会员ID
    @IsGetTotalInfo BIT,  --是否得到统计信息,1为得到统计信息,0为不得到统计信息,返回记录集
    @HasStatus BIT, -- 是否有状态
    @CouponsStatus INT --优惠券状态
    )
    AS
    ----------------------------------------定义参数--------------------------------------
    declare @strSql NVARCHAR(max);
    DECLARE @IsUseCount INT
    DECLARE @ExpiredCount INT
    DECLARE @NoUseCount INT
    DECLARE @where NVARCHAR(100);
    set nocount on;
    ----------------------------------------SQL开始--------------------------------------
    IF @HasStatus=1
    BEGIN
    SET @where='m.[State]=@CouponsStatus';
    END
    ELSE
    BEGIN
    SET @where='1=1'
    END
    IF @IsGetTotalInfo=0
    BEGIN
    SET @strSql=N'SELECT DISTINCT t.* FROM (SELECT m.[State] AS [STATUS],
    m.CouponCode AS CouponsCode,
    m.CampaignID AS CampaignID,
    c.Code as CampaignCode,
    c.Name AS CampaignName,
    b.ValidityStartTime AS CampaignStartTime,
    b.ValidityEndTime AS CampaignEndTime,
    c.Price as CouponsAmount,
    (CASE WHEN c.IsLimited =1 THEN 1 ELSE 2 END  ) as IsLimited,
    c.FullAmount as MinAmount,
    e.[Type] AS CouponsType,
    (CASE WHEN b.IconUrl IS NULL THEN N'''' WHEN b.IconUrl=N'''' THEN N'''' ELSE @hostName b.IconUrl END) AS IconUrl
    
    FROM  Loy_MemberCoupon m WITH ( NOLOCK ) INNER JOIN 
     [dbo].[MK_Campaign] AS a WITH ( NOLOCK ) ON m.CampaignID=a.ID AND m.MemberID=@MemberID AND ' @where '
            LEFT OUTER JOIN [dbo].[MK_CouponConfig] AS b  WITH ( NOLOCK ) ON a.[ID] = b.[CampaignID] 
            LEFT OUTER JOIN [dbo].[MK_Coupon] AS c WITH ( NOLOCK ) ON b.[CouponID] = c.[ID]
            LEFT OUTER JOIN [dbo].[MK_CouponRestriction] AS d  WITH ( NOLOCK ) ON c.[ID] = d.[CompainID]
            LEFT OUTER JOIN [dbo].[MK_CouponRestrictCategory] AS e WITH ( NOLOCK ) ON d.[ID] = e.[CouponRestrictionID]
            ) t
            ORDER BY t.CampaignEndTime
    offset (@PageIndex-1)*@PageSize  ROWS  FETCH NEXT @PageIndex*@PageSize ROWS ONLY'
    exec sp_executesql @strSql,N'@PageIndex int,@PageSize int,@hostName nvarchar(100),@MemberID UNIQUEIDENTIFIER,@where NVARCHAR(100),@CouponsStatus int',@PageIndex=@PageIndex,@PageSize=@PageSize,@hostName=@hostName,@MemberID=@MemberID,@where=@where,@CouponsStatus=@CouponsStatus
    END
    ELSE
    BEGIN
    -------------------------------------------------------------
    SELECT @IsUseCount=COUNT(1) FROM (SELECT DISTINCT m.CouponCode
    FROM  Loy_MemberCoupon m WITH ( NOLOCK ) WHERE m.MemberID=@MemberID AND m.[State]=2
            ) t
    SELECT @ExpiredCount=COUNT(1) FROM (SELECT DISTINCT m.CouponCode
    FROM  Loy_MemberCoupon m WITH ( NOLOCK ) WHERE m.MemberID=@MemberID AND m.[State]=3
            ) t
    SELECT @NoUseCount=COUNT(1) FROM (SELECT DISTINCT m.CouponCode
    FROM  Loy_MemberCoupon m WITH ( NOLOCK ) WHERE m.MemberID=@MemberID AND m.[State]=1
            ) t
    SELECT @IsUseCount AS IsUseCount,@ExpiredCount AS ExpiredCount,@NoUseCount AS NoUseCount
    END
    ----------------------------------------SQL结束--------------------------------------
    set nocount off;
    

    View Code

    新葡亰496net 14新葡亰496net 15代码

    8、在写存储过程中,在开始写set  nocount on  ,结束写set nocount off

    如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。

     1 USE pubs
     2 GO
     3 -- Display the count message.
     4 SELECT au_lname
     5 FROM authors
     6 GO
     7 USE pubs
     8 GO
     9 -- SET NOCOUNT to ON and no longer display the count message.
    10 SET NOCOUNT ON
    11 GO
    12 SELECT au_lname
    13 FROM authors
    14 GO
    15 -- Reset SET NOCOUNT to OFF.
    16 SET NOCOUNT OFF
    17 GO

    (当 SET NOCOUNT 为 ON 时,不返回计数(表示受Transact-SQL 语句影响的行数)。 当 SET NOCOUNT 为 OFF 时,返回计数(默认为OFF)。

    SET NOCOUNT 设置是在执行或运行时设置,而不是在分析时设置。

     

    即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。

    权限
    SET NOCOUNT 权限默认授予所有用户。

     

    当 SET NOCOUNT 为 ON 时,将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft® SQL Server™ 提供的实用工具执行查询时,在 Transact-SQL 语句(如 SELECT、INSERT、UPDATE 和 DELETE)结束时将不会在查询结果中显示"nn 行受影响"。

    结论:我们应该在存储过程的头部加上SET NOCOUNT ON 这样的话,在退出存储过程的时候加上 SET NOCOUNT OFF这样的话,以达到优化存储过程的目的。

    如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。

    多说两句:

    SET NOCOUNT 设置是在执行或运行时设置,而不是在分析时设置。

    1:在查看SqlServer的帮助的时候,要注意“权限”这一节,因为某些语句是需要一定的权限的,而我们往往忽略。

    注释当SET NOCOUNT 为ON 时,不返回计数(表示受Transact-SQL 语句影响的行数)。当SET NOCOUNT 为OFF 时,返回计数。 ... 结论:我们应该在存储过程的头部加上SET NOCOUNT ON 这样的话,在退出存储过程的时候加上SET NOCOUNT OFF这样的话,以达到优化)

    2:@@ROWCOUNT是返回受上一语句影响的行数,包括找到记录的数目、删除的行数、更新的记录数等,不要认为只是返回查找的记录数目,而且@@ROWCOUNT要紧跟需要判断语句,否则@@ROWCOUNT将返回0。

    3:如果使用表变量,在条件表达式中要使用别名来替代表名,否则系统会报错。

    4:在CUD类的操作中一定要有事务处理。

    5:使用错误处理程序,用来检查 @@ERROR 系统函数的 T-SQL 语句 (IF) 实际上在进程中清除了 @@ERROR 值,无法再捕获除零之外的任何值,必须使用 SET 或 SELECT 立即捕获错误代码。

    本文由新葡亰496net发布于网络数据库,转载请注明出处:SQL语句优化,NOCOUNT优化存储过程

    关键词: