stored procedure is execution taking long time in sql server

Question

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

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

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))

but getting error:Incorrect syntax near the keyword 'OPTION'.

Answer 1

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

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

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.

Solution: SET NOCOUNT ON

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