Databases
StackOverflow2013
Postsdbo
POHow to improve ASP.Net Dynamic Data pagination performance
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to improve ASP.Net Dynamic Data pagination performance
    primarykey
    data
    text
    <p>I'm using ASP.Net Dynnamic Data (.Net Framework version 4.0) and SQL Server 2008 to display data from a set of tables. In the database there is a certain master table containing 83 columns with 854581 rows of data with is still expected to grow.</p> <p>I did little modification to the default templates, and most operations are snappy enough for a business website, including filtering, foreign key displaying, etc. Yet the pager proved to be a major performance problem. </p> <p>Before I did any optimization using the Database Tuning Advisor, the master table list page won't even display as a simple count query times out. After all optimizations, the first few hundred pages will display without a problem, but anything beyond 1000 pages is unbearably slow and the last page times out. </p> <p>Note that I've optimized the query for the last page using Database Tuning Advisor, which gave me no advice at all.</p> <p>I tried to run a very simple query using <code>row_number()</code> to see the last page, similar to the query below:</p> <pre><code>SELECT TOP 10* FROM (SELECT *, row_number() OVER (ORDER BY [Primary Key Column] ASC) AS [rn] FROM [Master Table]) AS TB1 WHERE TB1.rn &gt; 854580 </code></pre> <p>The above query took about 20 seconds to complete the first time it was executed, while the SQL Server service <code>sqlservr.exe</code> ate up 1,700 KiB of memory (I'm using 32-bit windows with no special kernel switches, therefore, every process has at most 2 GiB of address space).</p> <p>So, my question is, is there a more efficient way than using <code>row_number()</code> to do pagination in ASP.Net Dynamic data and how to implement it.</p> <p>There are two possibilities that I came up with:</p> <ol> <li>There is some magical T-SQL language construct with better performance, for instance, something like <code>LIMIT &lt;From row number&gt;, &lt;To row number&gt;</code> in MySQL.</li> <li>I add a sequence number column in the table, and page according to that number when there is no filtering or sorting</li> </ol> <p>Perhaps the greater problem is how to implement this custom paging in ASP.Net Dynamic Data. Any help is appreciated, even pagination samples not intended to improve performance, just to give me an idea.</p> <p><strong>Note:</strong> I can't change the database engine I use, so don't recommend MySQL or PostgreSQL or anything like that.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USInterarticle
    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
    1. VO
      singulars
      1. POHow to improve ASP.Net Dynamic Data pagination performance
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to improve ASP.Net Dynamic Data pagination performance
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODon't worry! We'll always try to find the solution that works for **you**, in your situation. "X sucks, use Y instead!" is never a good answer here.
      singulars
      1. POHow to improve ASP.Net Dynamic Data pagination performance
      1. USrlb.usa
    2. COThanks. That last note was a bad habit carried from some old forum, perhaps not here.
      singulars
      1. POHow to improve ASP.Net Dynamic Data pagination performance
      1. USInterarticle
 

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