sql-server - 存储过程在sql server中执行时间很长

Question

我有一个名为 Transaction_tbl 的表，其中包含超过 400 000 条记录。这是表结构:

CREATE TABLE [dbo].[Transaction_tbl](
    [transactID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [TBarcode] [varchar](20) NULL,
    [cmpid] [int] NULL,
    [Locid] [int] NULL,
    [PSID] [int] NULL,
    [PCID] [int] NULL,
    [PCdID] [int] NULL,
    [PlateNo] [varchar](20) NULL,
    [vtid] [int] NULL,
    [Compl] [bit] NULL,
    [self] [bit] NULL,
    [LstTic] [bit] NULL,
    [Gticket] [int] NULL,
    [Cticket] [int] NULL,
    [Ecode] [varchar](50) NULL,
    [dtime] [datetime] NULL,
    [LICID] [int] NULL,
    [PAICID] [int] NULL,
    [Plot] [varchar](50) NULL,
    [mkid] [int] NULL,
    [mdlid] [int] NULL,
    [Colid] [int] NULL,
    [Comments] [varchar](100) NULL,
    [Kticket] [int] NULL,
    [PAmount] [numeric](18, 2) NULL,
    [Payid] [int] NULL,
    [Paid] [bit] NULL,
    [Paydate] [datetime] NULL,
    [POICID] [int] NULL,
    [DelDate] [datetime] NULL,
    [DelEcode] [nvarchar](50) NULL,
    [PAICdate] [datetime] NULL,
    [KeyRoomDate] [datetime] NULL,
    [Status] [int] NULL,
 CONSTRAINT [PK_Transaction_tbl] PRIMARY KEY CLUSTERED 
(
    [transactID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

我在 Locid, dtime 列上有一个非聚集索引。我有一个像这样的存储过程:

ALTER procedure [dbo].[IBS_fetchreleasedinpodiumgridnew]
   @locid INTEGER = NULL
AS BEGIN
   SET NOCOUNT ON
  DECLARE @TodayMinus7Days DATETIME
Declare @krrt integer
Declare @DT integer
   SET @TodayMinus7Days = getdate()-1



SELECT  
       t.TBarcode, t.PlateNo, t.DelEcode,cast(t.Paydate as Time) [REQ],
       datediff(MINUTE, t.PayDate,
             CASE t.Status
                WHEN 3 THEN GETDATE()
                WHEN 4 THEN t.KeyRoomDate
                When 5 THEN  t.KeyRoomDate
                End) as KRRT,

             datediff(MINUTE,t.PayDate,
             CASE t.Status
              WHEN 3 THEN GETDATE()
              WHEN 4 THEN GETDATE()
             WHEN 5 THEN t.DelDate
             END) as DT

   FROM    
       dbo.Transaction_tbl t

   WHERE   

   ( 
       ([status] IN (3,4))
       OR 
       ([status] = 5 AND DATEDIFF(n, DelDate, GETDATE()) <= 3)
   )
   AND locid = 6 AND dtime >= @TodayMinus7Days
   ORDER BY  
       paydate 
       end

我的执行计划是这样的:

但大多数时候这需要很长时间才能执行..在这种情况下我如何提高存储过程的执行性能？
我想使用的任何其他方法..任何帮助都非常有用。谢谢

查询执行计划显示排序需要很长时间..因此，如果我在paydate上给出索引，我的查询性能会提高吗？我给出的代码不是这样的 dtime >= @TodayMinus7Days :

dtime >= OPTION (optimize for (@TodayMinus7Days))

但出现错误:关键字“OPTION”附近的语法不正确。

Answer 1

除了优化查询之外，我还可以立即建议一些措施来提高存储过程的性能。

参数嗅探:当存储过程传递参数时，它会分析数据集以找出最有效的索引。这很有用，尽管计划被缓存，并且会过时，导致存储过程在低效的执行计划上运行。

解决方案:重新声明参数或针对未知参数值优化存储过程

抑制行计数:要提高存储过程的性能，最简单的操作之一就是 SET NOCOUNT ON。这将防止 SQL Server 在执行每个语句后将消息发送回客户端，而存储过程不需要这样做。这看起来是一个很小的改进，但结果是显而易见的。

解决方案:设置 NOCOUNT ON

下面的代码片段提供了它们在存储过程中的位置的示例。请注意，如果您重新声明参数，则无需针对未知进行优化，反之亦然。

CREATE PROCEDURE dbo.example_proc   
(   
    @USER_PARAM VARCHAR(200)
)
AS
BEGIN

    -- suppress  the number of rows returned
    SET NOCOUNT ON;

    -- Re-declaring the variable will prevent paramater sniffing
    DECLARE @LOCAL_USER_PARAM VARCHAR(200) = @USER_PARAM

    SELECT
        *
    FROM some_table st
    WHERE st.some_column = @LOCAL_USER_PARAM

    -- If you don't re-declare params, you can add this line
    OPTION (OPTIMIZE FOR (@USER_PARAM UNKNOWN))
    --

END 
GO