title: SQL Server slow select from large table
body: <p>I have a table with about 20+ million records.</p>
  
  <p>Structure is like:</p>
  
  <pre><code>EventId UNIQUEIDENTIFIER
  SourceUserId UNIQUEIDENTIFIER
  DestinationUserId UNIQUEIDENTIFIER
  CreatedAt DATETIME
  TypeId INT
  MetaId INT
  </code></pre>
  
  <p>Table is receiving about 100k+ records each day.</p>
  
  <p>I have indexes on each column except MetaId, as it is not used in 'where' clauses</p>
  
  <p>The problem is when i want to pick up eg. latest 100 records for desired SourceUserId</p>
  
  <p>Query sometimes takes up to 4 minutes to execute, which is not acceptable.</p>
  
  <p>Eg.</p>
  
  <pre><code>SELECT TOP 100 * FROM Events WITH (NOLOCK)
  WHERE SourceUserId = '15b534b17-5a5a-415a-9fc0-7565199c3461'
  AND 
  (
   TypeId IN (2, 3, 4)
      OR 
   (TypeId = 60 AND SrcMemberId != DstMemberId)
  )
  ORDER BY CreatedAt DESC
  </code></pre>
  
  <p>I can't do partitioning etc as I am using Standard version of SQL Server and Enterprise is too expensive.</p>
  
  <p>I also think that the table is quite small to be that slow. </p>
  
  <p>I think the problem is with ORDER BY clause as db must go through much bigger set of data.</p>
  
  <p>Any ideas how to make it quicker ?</p>
  
  <p>Perhaps relational database is not a good idea for that kind of data.</p>
  
  <p>Data is always being picked up ordered by CreatedAt DESC</p>
  
  <p>Thank you for reading.</p>
  
  <p>PabloX</p>