danielgerlag / danielgerlag/workflow-core
Nonoptimal database query
- Dominant language
- C#
- Stars
- 5.9k
- Forks
- 1.3k
- Avg merge
- 1d 1h
- Merged PRs (30d)
- 5
Description
**Describe the bug**
EntityFrameworkPersistenceProvider class methods for retrieving WorkflowInstance do not generate optimal database queries.
As an example, this [code](https://github.com/danielgerlag/workflow-core/blob/e7219e5d3025b144c08e6f8ab74c0863373e289e/src/providers/WorkflowCore.Persistence.EntityFramework/Services/EntityFrameworkPersistenceProvider.cs#L99-L115) generates a query, which leads to duplicate data from Workflow table.
Code:
```csharp
public async Task GetWorkflowInstance(string Id, CancellationToken cancellationToken = default)
{
using (var db = ConstructDbContext())
{
var uid = new Guid(Id);
var raw = await db.Set()
.Where(x => x.InstanceId == uid)
.Include(wf => wf.ExecutionPointers)
.ThenInclude(ep => ep.ExtensionAttributes)
.Include(wf => wf.ExecutionPointers)
.FirstAsync(cancellationToken);
if (raw == null)
return null;
return raw.ToWorkflowInstance();
}
}
```
Query:
```sql
SELECT
[t].[PersistenceId],
[t].[CompleteTime],
[t].[CreateTime],
[t].[Data], -- this is the cause of large dataflow (duplication)
[t].[Description],
[t].[InstanceId],
[t].[NextExecution],
[t].[Reference],
[t].[Status],
[t].[Version],
[t].[WorkflowDefinitionId],
[t0].[PersistenceId],
[t0].[Active],
[t0].[Children],
[t0].[ContextItem],
[t0].[EndTime],
[t0].[EventData],
[t0].[EventKey],
[t0].[EventName],
[t0].[EventPublished],
[t0].[Id],
[t0].[Outcome],
[t0].[PersistenceData],
[t0].[PredecessorId],
[t0].[RetryCount],
[t0].[Scope],
[t0].[SleepUntil],
[t0].[StartTime],
[t0].[Status],
[t0].[StepId],
[t0].[StepName],
[t0].[WorkflowId],
[t0].[PersistenceId0],
[t0].[AttributeKey],
[t0].[AttributeValue],
[t0].[ExecutionPointerId]
FROM (
SELECT TOP(1)
[w].[PersistenceId],
[w].[CompleteTime],
[w].[CreateTime],
[w].[Data],
[w].[Description],
[w].[InstanceId],
[w].[NextExecution],
[w].[Reference],
[w].[Status],
[w].[Version],
[w].[WorkflowDefinitionId]
FROM [wfc].[Workflow] AS [w]
WHERE [w].[InstanceId] = {uid}
) AS [t]
LEFT JOIN (
SELECT
[e].[PersistenceId],
[e].[Active],
[e].[Children],
[e].[ContextItem],
[e].[EndTime],
[e].[EventData],
[e].[EventKey],
[e].[EventName],
[e].[EventPublished],
[e].[Id],
[e].[Outcome],
[e].[PersistenceData],
[e].[PredecessorId],
[e].[RetryCount],
[e].[Scope],
[e].[SleepUntil],
[e].[StartTime],
[e].[Status],
[e].[StepId],
[e].[StepName],
[e].[WorkflowId],
[e0].[PersistenceId] AS [PersistenceId0],
[e0].[AttributeKey],
[e0].[AttributeValue],
[e0].[ExecutionPointerId]
FROM [wfc].[ExecutionPointer] AS [e]
LEFT JOIN [wfc].[ExtensionAttribute] AS [e0] ON [e].[PersistenceId] = [e0].[ExecutionPointerId]
) AS [t0] ON [t].[PersistenceId] = [t0].[WorkflowId]
ORDER BY [t].[PersistenceId], [t0].[PersistenceId], [t0].[PersistenceId0];
```
Our case contains a large amount of information in the Data column. It leads to performance degradation due to the large amount of duplicate data when the query is executed.
To avoid this side effect, you can split the query.
Code:
```csharp
var raw = await db.Set()
.Where(x => x.InstanceId == uid)
.Include(wf => wf.ExecutionPointers)
.ThenInclude(ep => ep.ExtensionAttributes)
.Include(wf => wf.ExecutionPointers)
.AsSplitQuery() // new line
.FirstOrDefaultAsync(cancellationToken);
```
Queries:
```sql
SELECT TOP(1)
[w].[PersistenceId],
[w].[CompleteTime],
[w].[CreateTime],
[w].[Data],
[w].[Description],
[w].[InstanceId],
[w].[NextExecution],
[w].[Reference],
[w].[Status],
[w].[Version],
[w].[WorkflowDefinitionId]
FROM [wfc].[Workflow] AS [w]
WHERE [w].[InstanceId] = {uid}
ORDER BY [w].[PersistenceId]
```
```sql
SELECT
[e0].[PersistenceId],
[e0].[AttributeKey],
[e0].[AttributeValue],
[e0].[ExecutionPointerId],
[t].[PersistenceId],
[e].[PersistenceId]
FROM (
SELECT TOP(1) [w].[PersistenceId]
FROM [wfc].[Workflow] AS [w]
WHERE [w].[InstanceId] = {uid}
) AS [t]
INNER JOIN [wfc].[ExecutionPointer] AS [e] ON [t].[PersistenceId] = [e].[WorkflowId]
INNER JOIN [wfc].[ExtensionAttribute] AS [e0] ON [e].[PersistenceId] = [e0].[ExecutionPointerId]
ORDER BY [t].[PersistenceId], [e].[PersistenceId]
```
```sql
SELECT
[e].[PersistenceId],
[e].[Active],
[e].[Children],
[e].[ContextItem],
[e].[EndTime],
[e].[EventData],
[e].[EventKey],
[e].[EventName],
[e].[EventPublished],
[e].[Id],
[e].[Outcome],
[e].[PersistenceData],
[e].[PredecessorId],
[e].[RetryCount],
[e].[Scope],
[e].[SleepUntil],
[e].[StartTime],
[e].[Status],
[e].[StepId],
[e].[StepName],
[e].[WorkflowId],
[t].[PersistenceId]
FROM (
SELECT TOP(1) [w].[PersistenceId]
FROM [wfc].[Workflow] AS [w]
WHERE [w].[InstanceId] = {uid}
) AS [t]
INNER JOIN [wfc].[ExecutionPointer] AS [e] ON [t].[PersistenceId] = [e].[WorkflowId]
ORDER BY [t].[PersistenceId], [e].[PersistenceId]
```
**Test**
***From SSMS***
[Data] size = 507kB;
Execution pointer count = 1000;
| Impl | [Data] total size | Execution time |
|---------------------|-------------------|----------------|
| left join (current) | 507 000 kB | ~ 6m36s520ms |
| split queries | 507kB | ~ 1s300ms |
***From external source***
Count - number of execution pointers;
DataSize - the size of the information in the Data column;
SingleQuery - single query (current impl);
SplitQuery - split queries;
``` ini
BenchmarkDotNet=v0.13.5, OS=Windows 10 (10.0.19045.2965/22H2/2022Update)
AMD Ryzen 7 4800HS with Radeon Graphics, 1 CPU, 16 logical and 8 physical cores
.NET SDK=7.0.100
[Host] : .NET 6.0.11 (6.0.1122.52304), X64 RyuJIT AVX2
Job-DFXIRM : .NET 6.0.11 (6.0.1122.52304), X64 RyuJIT AVX2
IterationCount=3 LaunchCount=1 WarmupCount=1
```
| Method | Id | Count | DataSize | Mean | Error | StdDev | Ratio | Gen0 | Gen1 | Gen2 | Allocated | Alloc Ratio |
|------------ |--------------------- |------ |---------- |-------------:|-------------:|------------:|------:|-------------:|-------------:|-------------:|------------:|------------:|
| **SingleQuery** | **6A8F3(...)8476E [36]** | **108** | **7.71 kB** | **2,571.3 ms** | **735.28 ms** | **40.30 ms** | **1.00** | **7000.0000** | **-** | **-** | **14.83 MB** | **1.00** |
| SplitQuery | 6A8F3(...)8476E [36] | 108 | 7.71 kB | 269.0 ms | 4.84 ms | 0.27 ms | 0.10 | 500.0000 | - | - | 2.25 MB | 0.15 |
| | | | | | | | | | | | | |
| **SingleQuery** | **C0EDE(...)29FBD [36]** | **210** | **14.81 kB** | **9,141.0 ms** | **579.62 ms** | **31.77 ms** | **1.00** | **34000.0000** | **2000.0000** | **-** | **72.47 MB** | **1.00** |
| SplitQuery | C0EDE(...)29FBD [36] | 210 | 14.81 kB | 408.3 ms | 34.98 ms | 1.92 ms | 0.04 | 1000.0000 | - | - | 4.42 MB | 0.06 |
| | | | | | | | | | | | | |
| **SingleQuery** | **C10E8(...)F4DBE [36]** | **333** | **23.38 kB** | **22,677.3 ms** | **5,308.82 ms** | **290.99 ms** | **1.00** | **69000.0000** | **68000.0000** | **67000.0000** | **242.66 MB** | **1.00** |
| SplitQuery | C10E8(...)F4DBE [36] | 333 | 23.38 kB | 633.7 ms | 34.22 ms | 1.88 ms | 0.03 | 1000.0000 | - | - | 7.12 MB | 0.03 |
| | | | | | | | | | | | | |
| **SingleQuery** | **4B444(...)B85E5 [36]** | **503** | **211.38 kB** | **308,729.0 ms** | **67,444.26 ms** | **3,696.85 ms** | **1.00** | **4583000.0000** | **4528000.0000** | **4453000.0000** | **22366.06 MB** | **1.000** |
| SplitQuery | 4B444(...)B85E5 [36] | 503 | 211.38 kB | 4,273.6 ms | 480.21 ms | 26.32 ms | 0.01 | 19000.0000 | 15000.0000 | 9000.0000 | 69.83 MB | 0.003 |
**Expected behavior**
Fetch data without unnecessary degradation.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.