title: EXTREMELY Poor LINQ Query Performance When Using Skip/Take for Paging
body: <p>I need to query records from a DB2 database using LINQ. I have entities that have been generated from the DB schema and am attempting to perform a LINQ query using Skip and Take. The underlying table has like 25 columns and maybe a million records. When I execute the query without the "Skip()" it takes approximately .508 milliseconds to complete. When I include Skip() it takes close to 30 seconds. Big difference.</p>
  
  <p>Can anyone tell me why this is happening?</p>
  
  <p><strong>UPDATE:</strong> Here is the LINQ query I am using.</p>
  
  <pre><code>var x = 30;
  
  var results = context.ASSET_T
  .OrderBy(c =&gt; c.ASSET_ID)
  .Skip(x)
  .Take(x)
  .ToList();
  </code></pre>
  
  <p><strong>UPDATE:</strong> So I just tried updating the query so that I only return a single column, ASSET_ID. When I only return that one column the query WITH the Skip() only takes .256 milliseconds.</p>
  
  <pre><code>var x = 30;
  
  var results = context.ASSET_T
  .OrderBy(c =&gt; c.ASSET_ID)
  .Skip(x)
  .Take(x)
  .Select(c =&gt; c.ASSET_ID)
  .ToList();
  </code></pre>
  
  <p>If I include any additional columns then the query execution time increases <strong><em>DRAMATICALLY</em></strong>.</p>
  
  <p>The query below for example takes 10 seconds to execute.</p>
  
  <pre><code>var x = 30;
  
  var results = context.ASSET_T
  .OrderBy(c =&gt; c.ASSET_ID)
  .Skip(x)
  .Take(x)
  .Select(c =&gt; new {
                   ASSET_ID = c.ASSET_ID,
                   ASSET_TYP = c.ASSET_TYP
                   ASSET_DESC = c.ASSET_DESC
                   })
  .ToList();
  </code></pre>
  
  <p><strong>UPDATE:</strong> I have now discovered that there are issues (perhaps index-related) with the columns in the table I am trying to query. As I mentioned above, when I execute a query that only returns the ASSET_ID column it only takes .256 milliseconds. If I try to execute a query that <strong><em>ONLY</em></strong> returns ASSET_DESC or a query that <strong><em>ONLY</em></strong> returns ASSET_TYP then the query execution time jumps to around 9 seconds. </p>
  
  <p>Would this indicate that those other columns are not currently being indexed?</p>
  
  <p><strong>UPDATE:</strong> I have added the SQL output from the above LINQ query.</p>
  
  <pre><code>SELECT 
  Project1.C1 AS C1, 
  Project1.ASSET_ID AS ASSET_ID, 
  Project1.ASSET_TYP AS ASSET_TYP, 
  Project1.ASSET_DESC AS ASSET_DESC
  FROM ( SELECT Project1.ASSET_ID AS ASSET_ID, Project1.ASSET_TYP AS ASSET_TYP, Project1.ASSET_DESC AS ASSET_DESC, Project1.C1 AS C1, row_number() OVER (ORDER BY Project1.ASSET_ID ASC, Project1.ASSET_TYP ASC, Project1.ASSET_DESC ASC) AS row_number
    FROM ( SELECT 
      Extent1.ASSET_ID AS ASSET_ID, 
      Extent1.ASSET_TYP AS ASSET_TYP, 
      Extent1.ASSET_DESC AS ASSET_DESC, 
      CAST(1 AS int) AS C1
      FROM MYDB.ASSET_T AS Extent1
    )  AS Project1
  )  AS Project1
  WHERE Project1.row_number &gt; 1
  ORDER BY Project1.ASSET_ID ASC, Project1.ASSET_TYP ASC, Project1.ASSET_DESC ASC FETCH  FIRST 31 ROWS ONLY 
  </code></pre>