Umbraco-CMS: SQL Timeouts from TrackedReferencesRepository.GetPagedRelationsForItem
Which exact Umbraco version are you using? For example: 9.0.1 - don’t just write v9
9.4.1
Bug summary
On a site with 12k rows in umbracoNode and 22k rows in umbracoRelation, I see SQL timeouts when the Info content app tries to load references to the current content or media.
Specifics
The query generated in TrackedReferencesRepository.GetPagedRelationsForItem to retrieve pagedResult.TotalItems takes around 90 seconds to run. The issue appears to be this join to find both parent to child and bidirectional relations:
INNER JOIN [umbracoNode] [cn]
ON (([umbracoRelationType].[dual] = 0 AND ([r].[parentId] = [cn].[id]))
OR ([umbracoRelationType].[dual] = 1 AND (([r].[childId] = [cn].[id]) OR ([r].[parentId] = [cn].[id]))))
After modifying the query to get just one type of relation at a time with the joins below, each runs in less than 1 second.
INNER JOIN [umbracoNode] [cn]
ON (([umbracoRelationType].[dual] = 0 AND ([r].[parentId] = [cn].[id])))
INNER JOIN [umbracoNode] [cn]
ON (([umbracoRelationType].[dual] = 1 AND (([r].[childId] = [cn].[id]) OR ([r].[parentId] = [cn].[id]))))
An example stack trace
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at NPoco.Database.ExecuteScalarHelper(DbCommand cmd)
at NPoco.Database.ExecuteScalar[T](String sql, CommandType commandType, Object[] args)
at NPoco.Database.ExecuteScalar[T](Sql Sql)
at NPoco.Database.ExecuteScalar[T](String sql, Object[] args)
at NPoco.Database.PageImp[T,TRet](Int64 page, Int64 itemsPerPage, String sql, Object[] args, Func`3 executeQueryFunc)
at NPoco.Database.Page[T](Int64 page, Int64 itemsPerPage, String sql, Object[] args)
at NPoco.Database.Page[T](Int64 page, Int64 itemsPerPage, Sql sql)
at Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.TrackedReferencesRepository.GetPagedRelationsForItem(Int32 id, Int64 pageIndex, Int32 pageSize, Boolean filterMustBeIsDependency, Int64& totalRecords)
at Umbraco.Cms.Core.Services.Implement.TrackedReferencesService.GetPagedRelationsForItem(Int32 id, Int64 pageIndex, Int32 pageSize, Boolean filterMustBeIsDependency)
at Umbraco.Cms.Web.BackOffice.Controllers.TrackedReferencesController.GetPagedReferences(Int32 id, Int32 pageNumber, Int32 pageSize, Boolean filterMustBeIsDependency)
at lambda_method954(Closure , Object , Object[] )
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
Steps to reproduce
Open the Info content app on a site with a large number of nodes and relations.
Expected result / actual result
No response
This item has been added to our backlog AB#18647
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Reactions: 2
- Comments: 15 (15 by maintainers)
Noted. Thanks for sharing! *** It worked 😃
Hi @bergmania It seems like @thomashdk is experiencing the same issue as I raised in https://github.com/umbraco/Umbraco-CMS/issues/12308 We’ve implemented a workaround by hijacking the repository and implementing our own version with the slight change in the SQL queried as shown in the issue.
As of today we’re also experiencing this issue in deleting media items, that uses the GetPagedItemsWithRelations call. I had hoped perhaps this query was improved in 9.5, but it was not, so this issue exists in both 9.4.3 and 9.5
Hi @thomashdk,
Let me just stipulate before sharing our workaround that @patrickdemooij9 has pointed out that the query produced through our workaround does NOT produce the desired outcome of the repository. It does however at least allow BackOffice users to remove documents and media whilst this query is unoptimized. So if you decide to use this workaround make sure that you remove it as soon as it’s fixed in the Umbraco framework.
Add this class to your solution: https://gist.github.com/beliskner/86f8dd0ac77119129479d529b243786d
Afterwards include this in your composers:
// TODO: When this issue is resolved in Umbraco stop hijacking the repository and remove this composer + class builder.Services.AddUnique<ITrackedReferencesRepository, HijackedTrackedReferencesRepository>();Yes, testing a nightly build would be no problem.