Hello all. I have inhereted an outdated DNN site... tere is a module which loads data from an SQL (2016) table that is on the larger side. This table contains inventory, so it can't really be shrunk in any way. The DB Performance is really good. Other applications (NAV) can load the same table in 3-5 seconds.
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.
My thoughts to speed up would be something like:
If anyone has a suggestion I would like to hear them! Thank you.
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
Michael TobischDNN★MVP
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:
CREATE PROCEDURE [dbo].[GetProductsByCategoryId] @CategoryId INT, @PageIndex INT, @NumRows INT, @CategoryName nvarchar(50) OUTPUT, @CategoryProductCount INT OUTPUT AS BEGIN /* The below statements enable returning the Total Product Count and friendly Name for the CategoryId as output paramsters to our SPROC. This enables us to avoid having to make a separate call to the database to retrieve them, and can help improve performance quite a bit */ SELECT @CategoryProductCount = (SELECT COUNT(*) FROM Products WHERE Products.CategoryId = @CategoryId) SELECT @CategoryName = (SELECT Name FROM Categories WHERE Categories.CategoryID = @CategoryId) /* The below statements use the new ROW_NUMBER() function in SQL 2005 and later to return only the specified rows we want to retrieve from the Products table */ DECLARE @startRowIndex int; SET @startRowIndex = (@PageIndex * @NumRows) + 1; WITH ProductEntries AS ( SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) AS Row, ProductId, CategoryId, Description, ProductImage, UnitCost FROM Products WHERE CategoryId=@CategoryId ) SELECT ProductId, CategoryId, Description, ProductImage, UnitCost FROM ProductEntries WHERE Row BETWEEN @startRowIndex AND @StartRowIndex + @NumRows-1 END
I hope this gives you the idea of how to handle it. What you need is:
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.
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.
These Forums are dedicated to the discussion of DNN Platform.
For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:
Awesome! Simply post in the forums using the link below and we'll get you started.