存储过程在sql server中执行需要很长时间 [英] stored procedure is execution taking long time in sql server

问题描述

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

I have a table called Transaction_tbl with more than 400 000 records in it. This is the table structure:

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 列上有一个非聚集索引.我有一个这样的存储过程:

I have a nonclustered index on the Locid, dtime column. I have a stored procedure like this:

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

我的执行计划是这样的:

my execution plan like this:

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

but most of the time this is taking long time to execute ..in this case how i can improve my stored procedure execution performance?
any other method i want to use..any help is very appriciable.Thanks

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

the query execution plan is showing sorting is taking long time..so if i give index on paydate my query performance will increase? instead of this dtime >= @TodayMinus7Days i given code like this:

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

但出现错误:关键字OPTION"附近的语法不正确.

推荐答案

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

Apart from optimizing the query, there are a couple things I can immediately suggest to improve the performance of a stored procedure.

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

Parameter sniffing: When the store procedure is passed a parameter, it analyses the dataset to figure out what would be the most efficient indexes. This is useful, though the plan is cached, and will get out of date, causing the stored proc to run on an inefficient execution plan.

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

Solution: Re-declare parameters or optimize the stored proc for unknown parameter values

抑制行计数:可以提高存储过程性能的最简单的方法之一是 SET NOCOUNT ON.这将阻止 SQL Server 在执行每个语句后将消息发送回客户端，这对于存储过程来说是不需要的.这似乎是一个小小的改进，但结果是显而易见的.

Suppress the row count: One of the most simple things you can do to increase the performance of a stored procedure is SET NOCOUNT ON. This will prevent SQL Server from sending messages back to the client after the execution of each statement, which is not required for a stored proc. It seems like a small improvement, but the results are noticeable.

解决方案:设置无计数

下面的代码片段有一个示例，说明它们在您的存储过程中的位置.请注意，如果您是重新声明参数，则不需要针对未知进行优化，反之亦然.

The snippet below has an example of where they go in your stored proc. Note that if you are re-declaring parameters, you don't need to optimize for unknown, and vice versa.

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

这篇关于存储过程在sql server中执行需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！