title: stored procedure is execution taking long time in sql server
body: <p>I have a table called Transaction_tbl with more than 400 000 records in it. This is the table structure:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have a nonclustered index on the Locid, dtime column.
  I have a stored procedure like this:</p>
  
  <pre><code>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()) &lt;= 3)
     )
     AND locid = 6 AND dtime &gt;= @TodayMinus7Days
     ORDER BY  
         paydate 
         end
  </code></pre>
  
  <p>my execution plan like this:
  <img src="https://i.stack.imgur.com/Mbq8b.gif" alt="enter image description here"></p>
  
  <p>but most of the time this is taking long time to execute ..in this case how i can improve my stored procedure execution performance?<br>
  any other method i want to use..any help is very appriciable.Thanks</p>
  
  <p>the query execution plan is showing sorting is taking long time..so if i give index on <code>paydate</code> my query performance will increase?
  instead of this <code>dtime &gt;= @TodayMinus7Days</code> i given code like this:  </p>
  
  <pre><code>dtime &gt;= OPTION (optimize for (@TodayMinus7Days))
  </code></pre>
  
  <p>but getting error:<strong>Incorrect syntax near the keyword 'OPTION'.</strong></p>