Hi,

these articles are a bit old, but they should give you the idea of how to load only the records needed to display on one page (which makes the thing more efficient and faster, as not all records are returned from SQL Server):

ScottGu's Blog - Efficient Data Paging and Sorting with ASP.NET 2.0 and SQL 2005

Happy DNNing!
Michael

loading 6500 rows should not take 30 secs, unless each row is rather large (> 11 KB) or it takes time for the database to fetch the rows. did you monitor execution of the query using SQL Server Profiler or Activity Monitor?

Michael, Thank you but the article pertaining to SQL is dead. 

Sabastian, All of the monitoring seems to be withing reason. There's tons or RAM and resources available. As I mentioned before, it loads right up to a Microsoft NAV client, but seems to take forever to post to IIS.

Hi again,

sorry about this... the basic idea is to create a stored procedure and pass the page number and the number of rows to get only the data that will be actually displayed on the page.

Here is an example:

<code>CREATE PROCEDURE &#91;dbo&#93;.&#91;GetProductsByCategoryId&#93;<br />    @CategoryId INT,<br />    @PageIndex INT,<br />    @NumRows INT,<br />    @CategoryName nvarchar(50) OUTPUT,<br />    @CategoryProductCount INT OUTPUT<br /> AS<br /> BEGIN<br />    /*<br />       The below statements enable returning the Total Product Count and friendly Name for the CategoryId<br />       as output paramsters to our SPROC. This enables us to avoid having to make a separate call to the<br />       database to retrieve them, and can help improve performance quite a bit<br />     */<br /> <br />    SELECT @CategoryProductCount = (SELECT COUNT(*) FROM Products WHERE Products.CategoryId = @CategoryId)<br />    SELECT @CategoryName = (SELECT Name FROM Categories WHERE Categories.CategoryID = @CategoryId)<br /> <br />    /*<br />       The below statements use the new ROW_NUMBER() function in SQL 2005 and later to return only<br />       the specified rows we want to retrieve from the Products table<br />    */<br /> <br />    DECLARE @startRowIndex int;<br />    SET @startRowIndex = (@PageIndex * @NumRows) + 1;<br /> <br />    WITH ProductEntries AS (<br />       SELECT<br />          ROW_NUMBER() OVER (ORDER BY ProductId ASC) AS Row,<br />          ProductId,<br />          CategoryId,<br />          Description,<br />          ProductImage,<br />          UnitCost<br />       FROM Products<br />       WHERE CategoryId=@CategoryId<br />    )<br /> <br />    SELECT ProductId, CategoryId, Description, ProductImage, UnitCost<br />    FROM ProductEntries<br />    WHERE Row BETWEEN @startRowIndex AND @StartRowIndex + @NumRows-1<br /> <br /> END</code>

I hope this gives you the idea of how to handle it. What you need is:

• current page number
• total number of records
• number of records per page

Happy DNNing!
Michael

Posted By JKeller on 17 Feb 2022 08:39 AM

..

For instance: The page displays the first 10 results of around 6500 records, so there's a total of 650 pages. This takes 30+ seconds to load and makes users grumpy and they like to whine. 

this sounds more like a timeout than a performance problem of SQL or IIS server. 

 

Right, I'm juggleing two differernt problems. People complaining it takes too long, and when it does take over 30 seconds it times out.

I have searched high and low to find the reason for a hard 30s timeout but I can't find anything. I've tried a 100 different things so far!

w3wp.exe seems to be the processes that throws the error.

a timeout happens, if the database doesn't return expected result or really has performance issues. But if you don't experience this issue using the same query from a different application there must be a problem in the code of your module.

I second Sebastians thoughts - perhaps other applications using 'this' statement are missing a specific column? Perhaps an index have been created for 'this' select statement - but you have included an extra column which is not part of the index? If this is the case the performance will take a big hit since the SQL server will have to extract the data from the table instead of the index.

This can usually be determined by monitoring the SQL server while DNN is extracting the data (using the activity monitor in SSMS - see Active Expensive Queries or Recent Expensive Queries). Catch the executed select statement and try executing it in SSMS with the execution plan option enabled (Ctrl+M). This will give you a detailed view of where the performance bottleneck is - and whether the SQL have any impact on it!

If the SQL is not the bottleneck it is most likely the architecture/technology of the module you need to look into.. but that will require having access to the source code.