I have reviewed the other post on this, and found my scenario to be much more un workable, to the extent that I am unsure if it is the way I am writing the query that is the issue. Surely it must be as this is ridiculously slow.
Example provided will show the speed of execution with Where IN OPENJSON, against the Speed of querying this manually, vs working with 1 record at a time and adding to a list.
I would like to work with the IQueryable rather than populate a variable with each record in a loop lol.
Code Example as I would want it to be written (converts to WHERE IN OpenJSON)
This example took 25 seconds and has a couple screenshots showing the breakpoint before entering the query and breakpoint after finishing the area of concern work.
// this is just a List of GUIDs
var relatedWorkflowIds = await dbContext.Database.SqlQuery<Guid>($"EXEC GetChildIdsFromParentWorkflowInstance {request.Id}").ToListAsync();
var query =
dbContext.WorkflowInstanceStepHistories.AsNoTracking()
.Where(wish => relatedWorkflowIds.Contains(wish.WorkflowInstanceId.Value));
var queryWithIncludedData = await query
.Include(wish => wish.WorkflowStepOutcome).AsNoTracking()
.Include(wish => wish.WorkflowInstanceStep).ThenInclude(wis => wis.WorkflowStep).ThenInclude(ws => ws.Workflow).AsNoTracking()
.Include(wish => wish.WorkflowInstanceStep).ThenInclude(wis => wis.WorkflowStep).ThenInclude(ws => ws.WorkflowStepType).AsNoTracking()
.ToListAsync();
Note again that the list of IDs is just a List of Guid
SQL Output of the code
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (25,016ms) [Parameters=[@__relatedWorkflowIds_0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT [w].[Id], [w].[CreatedOnUtc], [w].[LastUpdatedBy], [w].[ModifiedOnUtc], [w].[NewRecipient], [w].[Notes], [w].[Recipient], [w].[Reference], [w].[WorkflowInstanceId], [w].[WorkflowInstanceStepId], [w].[WorkflowStepOutcomeId], [w].[WorkflowStepStatus], [w0].[Id], [w0].[CreatedOnUtc], [w0].[IsUserSelectable], [w0].[LastUpdatedBy], [w0].[ModifiedOnUtc], [w0].[WorkflowStepOutcome], [w1].[Id], [w1].[CreatedOnUtc], [w1].[LastUpdatedBy], [w1].[ModifiedOnUtc], [w1].[Recipient], [w1].[WorkflowInstanceId], [w1].[WorkflowStepId], [w2].[Id], [w2].[CreatedOnUtc], [w2].[Description], [w2].[HelpId], [w2].[LastUpdatedBy], [w2].[ModifiedOnUtc], [w2].[Name], [w2].[Recipient], [w2].[WorkflowId], [w2].[WorkflowStepTypeId], [w3].[Id], [w3].[CreatedOnUtc], [w3].[Description], [w3].[EntryId], [w3].[IsActive], [w3].[LastUpdatedBy], [w3].[ModifiedOnUtc], [w3].[Name], [w3].[School], [w3].[WorkflowUsageId], [w4].[Id], [w4].[CreatedOnUtc], [w4].[LastUpdatedBy], [w4].[ModifiedOnUtc], [w4].[WorkflowStepType]
FROM [WorkflowInstanceStepHistories] AS [w]
LEFT JOIN [WorkflowStepOutcomes] AS [w0] ON [w].[WorkflowStepOutcomeId] = [w0].[Id]
LEFT JOIN [WorkflowInstanceSteps] AS [w1] ON [w].[WorkflowInstanceStepId] = [w1].[Id]
LEFT JOIN [WorkflowSteps] AS [w2] ON [w1].[WorkflowStepId] = [w2].[Id]
LEFT JOIN [Workflows] AS [w3] ON [w2].[WorkflowId] = [w3].[Id]
LEFT JOIN [WorkflowStepTypes] AS [w4] ON [w2].[WorkflowStepTypeId] = [w4].[Id]
WHERE [w].[WorkflowInstanceId] IN (
SELECT [r].[value]
FROM OPENJSON(@__relatedWorkflowIds_0) WITH ([value] uniqueidentifier '$') AS [r]
)
I changed this to use a collection not open JSON and it ran instantly:
Code Example using a variable to store each record to within loop of IDs. (not what I wanted to do to avoid the OpenJSON issue)
Thanks for your perspectives and assistance.
AsNoTracking()
everywhere. I think oneAsNoTracking()
before theToListAsync()
should be enough. 3.) Have you tried using temporal tables for your GUIDs?OPENJSON
has problem with parameter sniffing.