title: How to reduce query running time in SQL server?
body: <p>I have written below query to retrieve distinct RegNo from two different tables. But below query takes nearly 25 seconds to retrieve results. In Inventory table more than 1.5 million records are there.</p>
  
  <pre><code>Select F.PKID, F.RegNo
  From
  (
    Select E.PKID, E.RegNo 
    Row_Number() Over(Order By E.RegNo Asc) RowNo
    From
    (
      Select C.PKID, C.RegNo
      From
      (
        Select Pk_Id PKID, LTrim(RTrim(A.Reg_No)) RegNo, 
        Row_Number() Over(Partition By LTrim(RTrim(A.Reg_No)) 
        Order By (Select Null)) RegRowNo
        From dbo.KeyreferenceDetails A (NoLock)
        Where A.KeyreferenceStatus = 'L' 
        And A.Reg_No Like @Value And IsNull(Reg_No, '') &lt;&gt; '' And Not Exists
        (
          Select 1 From dbo.INVENTORY B (NoLock) 
          Where A.Reg_No = B.Inv_H_Reg_No
        ) 
      ) C
      Where C.RegRowNo = 1 And IsNull(C.RegNo, '') &lt;&gt; '-'
      Union
      Select D.PKID, D.RegNo
      From 
      (
        Select Pk_ID PKID, LTrim(LTrim(Txt_RegNo)) RegNo, 
        Row_Number() Over(Partition By LTrim(LTrim(A.Txt_RegNo)) 
        Order By (Select Null)) RegRowNo
        From dbo.MobileMessageDetails A (Nolock)  
        Left Join dbo.PLACE P (Nolock) On P.Place_Shrt_Code  = A.Txt_YarddCode 
        And P.[Status] = 'L'
        Left Join dbo.INVENTORY B (Nolock) On A.Txt_RegNo = B.Inv_H_Reg_No            
        Where A.Txt_INOUT In('IN', 'MOBILE') And IsNull(A.Txt_RegNo, '') &lt;&gt; '' And B.Inv_H_Pk_Id Is Null 
        And A.[Status] = 'L' And Txt_RegNo Like @Value 
      ) D
      Where D.RegRowNo = 1 And IsNull(D.RegNo, '') &lt;&gt; '-'
   ) E
  ) F
  Where F.RowNo &gt; 0 And F.RowNo &lt;= 20
  </code></pre>
  
  <p><strong>Query Plan:</strong></p>
  
  <p><a href="https://i.stack.imgur.com/0SwlW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0SwlW.png" alt="enter image description here"></a></p>
  
  <p><strong>Available Indexes:</strong></p>
  
  <p><strong><code>KeyreferenceDetails table:</code></strong></p>
  
  <pre><code> Index Name ---------------+ Column Name ----------------- + Index Type
   IX_KeyreferenceDetails_I  |  Reg_No                       | NONCLUSTERED   
   IX_KeyreferenceDetails_II |  KeyreferenceStatus           | NONCLUSTERED
  </code></pre>
  
  <p><strong><code>Inventory table:</code></strong></p>
  
  <pre><code> Index Name ---------------+ Column Name ----------------- + Index Type
   IX_Inventory_I            | Inv_H_Reg_No                  | NONCLUSTERED   
  </code></pre>
  
  <p><strong><code>MobileMessageDetails table:</code></strong></p>
  
  <pre><code> Index Name --------------- + Column Name ----------------- + Index Type
   IX_MobileMessageDetails_I  | Txt_RegNo                     | NONCLUSTERED
   IX_MobileMessageDetails_II | Txt_INOUT                     | NONCLUSTERED
  </code></pre>
  
  <p><strong><code>Place table:</code></strong></p>
  
  <pre><code> Index Name ---------------+ Column Name ----------------- + Index Type
   IX_Place_I                | Place_Shrt_Code               | NONCLUSTERED  
   IX_Place_I                | Status                        | NONCLUSTERED
  </code></pre>
  
  <p>I have created required indexes for all the used tables in above query. But query cost is high. How to reduce query running time in SQL server? </p>
  
  <p><strong><code>Statistics Output:</code></strong></p>
  
  <pre><code>SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
  Table 'INVENTORY'. Scan count 6, logical reads 382, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'KeyreferenceDetails'. Scan count 15, logical reads 9062, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Mobile_MessageDetails'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table '#TempItemsCount_____________________________________________________________________________________________________0000000118A9'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  
  SQL Server Execution Times:
  CPU time = 20733 ms,  elapsed time = 7844 ms.
  Table 'INVENTORY'. Scan count 6, logical reads 382, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'KeyreferenceDetails'. Scan count 14, logical reads 9062, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Mobile_MessageDetails'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table '#VehicleRegDetails__________________________________________________________________________________________________0000000118AB'. Scan count 0, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  
  SQL Server Execution Times:
  CPU time = 21139 ms,  elapsed time = 8146 ms.
  Table '#TABLE_SCHEMA_______________________________________________________________________________________________________0000000118AA'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  </code></pre>
  
  <p><strong><code>Update:</code></strong></p>
  
  <pre><code>Insert Into #TempItemsCount(TotalCount)
  Select Count(E.PKID)
  From
   (
     Select E.PKID, E.RegNo 
        Row_Number() Over(Order By E.RegNo Asc) RowNo
        From
        (
          Select C.PKID, C.RegNo
          From
          (
            Select Pk_Id PKID, LTrim(RTrim(A.Reg_No)) RegNo, 
            Row_Number() Over(Partition By LTrim(RTrim(A.Reg_No)) 
            Order By (Select Null)) RegRowNo
            From dbo.KeyreferenceDetails A (NoLock)
            Where A.KeyreferenceStatus = 'L' 
            And A.Reg_No Like @Value And IsNull(Reg_No, '') &lt;&gt; '' And Not Exists
            (
              Select 1 From dbo.INVENTORY B (NoLock) 
              Where A.Reg_No = B.Inv_H_Reg_No
            ) 
          ) C
          Where C.RegRowNo = 1 And IsNull(C.RegNo, '') &lt;&gt; '-'
          Union
          Select D.PKID, D.RegNo
          From 
          (
            Select Pk_ID PKID, LTrim(LTrim(Txt_RegNo)) RegNo, 
            Row_Number() Over(Partition By LTrim(LTrim(A.Txt_RegNo)) 
            Order By (Select Null)) RegRowNo
            From dbo.MobileMessageDetails A (Nolock)  
            Left Join dbo.PLACE P (Nolock) On P.Place_Shrt_Code  = A.Txt_YarddCode 
            And P.[Status] = 'L'
            Left Join dbo.INVENTORY B (Nolock) On A.Txt_RegNo = B.Inv_H_Reg_No            
            Where A.Txt_INOUT In('IN', 'MOBILE') And IsNull(A.Txt_RegNo, '') &lt;&gt; '' And B.Inv_H_Pk_Id Is Null 
            And A.[Status] = 'L' And Txt_RegNo Like @Value 
          ) D
          Where D.RegRowNo = 1 And IsNull(D.RegNo, '') &lt;&gt; '-'
      ) E
   )
  </code></pre>