What version of SQL server and compatibility mode of the database are you running?

BR Nicolai

Running SQL server 12 with compatibility mode SQL Server 2005.

Changed the compatibility mode to SQL Server 2012, but the backoffice doesn't seem to load. It just spins.

Okay it seems our test server was overheating a bit, which caused the spining. It seemed to work with the compatibility mode.

Hi

Just want to add my experience with the exact same problem but on v9.6.5. Changed the compatibility mode to "SQL Server 2014 (120)", but that didn't fix the problem. Changed again to compatibility mode "SQL Server 2012 (110)" and that fixed it 

Dear Nicolai,

We are starting to see this issue as well. The solution has +100.000 records in AccessUser and +13.000 paragraphs, and it looks like a timeout issue due to the very big join.

Can this sql statement somehow be optimized?

SELECT Paragraph.*, AccessUserCreated.AccessUserName AS AccessUserNameCreated,
                        AccessUserEdit.AccessUserName AS AccessUserNameEdit,
                        IIF(globalParagraph.Paragraphid is null, 0, 1) AS IsUsedAsGlobalParagraph FROM AccessUser AS AccessUserCreated RIGHT OUTER JOIN
                         AccessUser AS AccessUserEdit RIGHT OUTER JOIN
                         Paragraph AS Paragraph ON AccessUserEdit.AccessUserID = Paragraph.ParagraphUserEdit ON
                         AccessUserCreated.AccessUserID = Paragraph.ParagraphUserCreate
                         LEFT JOIN Paragraph AS globalParagraph ON globalParagraph.ParagraphGlobalID = Paragraph.Paragraphid

Best regards,

Anders

Dear Nicolai,

We have localized the issue to be the amount of text in this field: ParagraphText, which really slows down the query. The issue is from a solution with approx. 40 websites.

How can that issue be solved?

Best regards, Anders

Hi Anders

I have made an alternative way of loading. Attached a pirate dll you can try.

What field type is you ParagraphText? Is it nvachar(max) or something else?

Dear Nicolai,

The column is nvarchar(max) and we are somewhat getting nearly the same error (just with an extra step in the stacktrace):

Server Error in '/' Application.

Sql Exception occurred In ParagraphRepository.GetParagraphs

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ApplicationException: Sql Exception occurred In ParagraphRepository.GetParagraphs

Source Error:
 

Stack Trace:
 

[ApplicationException: Sql Exception occurred In ParagraphRepository.GetParagraphs]
   Dynamicweb.Content.Data.ParagraphRepository.GetParagraphs(CommandBuilder commandBuilder) +417
   Dynamicweb.Content.Data.ParagraphRepository.GetParagraphs() +176
   Dynamicweb.Content.ParagraphService.InitializeCache() +129
   Dynamicweb.Caching.DictionaryCache`2.InitializeCacheDictionary() +155
   System.Lazy`1.CreateValue() +734
   System.Lazy`1.LazyInitValue() +450
   Dynamicweb.Caching.DictionaryCache`2.GetAllValuesInternal() +27
   Dynamicweb.Caching.ServiceCache`2.GetAllValues() +106
   Dynamicweb.Content.ParagraphService.GetParagraphs() +64
   Dynamicweb.Content.ParagraphService.GetParagraphsByPageId(Int32 pageId, ParagraphSort sortBy) +200
   Dynamicweb.Frontend.Content.GetParagraphs(Int32 pageId) +404
   Dynamicweb.Frontend.Content.CreateContent(Int32 contentId) +184
   Dynamicweb.Frontend.PageView.GetContent() +94
   Dynamicweb.Frontend.ContentViewModelFactory.CreatePageViewModel(PageView pageView) +487
   Dynamicweb.Frontend.PageView.SetPageTemplateValues() +164
   Dynamicweb.Frontend.PageView.Output() +2234
   Dynamicweb.AspNet.DynamicwebHttpHandler.ProcessRequest(HttpContext context) +77
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +542
   System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +75
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +93

I think maybe the dll you got has errors in it. It did not pass our QA and code review...!

We have made another change completely removing parts of this. But we think it might be something else - maybe indexes on the accessuser or paragraph table.

Can you share which indexes you have on these tables. Is the database in our hosting so we can get hold of it?

BR Nicolai

Dear Nicolai,

The database is not hosted in your environment. But from what I can see the exception is caused by a timeout due to the amount of data in this field: ParagraphText.

The query is fast enough without the field, but very slow when the field is a part of the query.

Best regards,

Anders

ok - we need a copy of the database pls. Can you make that available - I'll have DW Care install a copy for us to look at.

Thanks, Nicolai

Hi 

There has been an upgrade on how paragraphs are loaded 
This feature has been implemented in Dynamicweb version 9.13.10

Get this version from the download section https://doc.dynamicweb.dk/downloads/dynamicweb-9

Kind Regards
Care Support
Kristian Kirkholt