Databases
StackOverflow2013
Postsdbo
POEXTREMELY Poor LINQ Query Performance When Using Skip/Take for Paging
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEXTREMELY Poor LINQ Query Performance When Using Skip/Take for Paging
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAmi Schreiber
    1. USAmi Schreiber
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POEXTREMELY Poor LINQ Query Performance When Using Skip/Take for Paging
      1. This table or related slice is empty.
      1. VTDownMod
    1. COWhat is the SQL that is generated?
      singulars
      1. POEXTREMELY Poor LINQ Query Performance When Using Skip/Take for Paging
      1. USRomoku
    2. COShow the LINQ query you wrote.
      singulars
      1. POEXTREMELY Poor LINQ Query Performance When Using Skip/Take for Paging
      1. USBeemerGuy
    3. COWas the query ever treated as `IEnumerable<T>` ? Or is it `IQueryable<T>` throughout?
      singulars
      1. POEXTREMELY Poor LINQ Query Performance When Using Skip/Take for Paging
      1. USMarc Gravell
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload