'WorkflowDefinitionVersion' can be separated?

[jxnkwlp]

In persistence , 'WorkflowDefinitionVersion' contain workflow definition and all versions , can it split by definition and versions ?

consider reason: if there has many items in 'WorkflowDefinitionVersion' table , it may be list slow when frontend show .

[sfmskywalker]

It's possible, and we may still consider doing so if it turns out to be a real problem for Elsa 2. We'll keep an eye on that.

[DavidPx]

If you're using one of the EntityFramework providers adding an index to the definition table would help quite a bit - there are no indexes on any of those tables right now!

@sfmskywalker , is this something you'd like to see? Here is a proof of concept.

My index goes: Status, DefinitionId, Version. In my use case I plan on showing my users their active/completed work in an area that will contain several different workflows. I included Version so that I can exclude old, outdated tasks if needed. I put Status in front because it should help the performance of the ContinueRunningWorkflows startup task.

[sfmskywalker]

Yep, we definitely need to add indexes for the fields on which we query 👍

[DavidPx]

I'm about to add those indicies to the EF Core configuration classes but could use some guidance on the Boookmark entity - I don't understand the use cases on that table. A summary of what I plan on doing - please correct as is needed!

Workflow Instance:

• Status, DefinitionId, Version

Workflow Definition:

• DefinitionId, Version

Bookmark:

• ActivityType, TenantId, Hash

[sfmskywalker]

Looking good. I would go a bit further even and add indices for the following:

Workflow Definition

• TenantId
• Version
• Name
• IsLatest
• IsPublished

Workflow Instance

• TenantId
• DefinitionId
• WorkflowStatus
• CorrelationId
• ContextType
• ContextId
• Name
• CreatedAt
• LastExecutedAt
• FinishedAt
• CancelledAt
• FaultedAt

Bookmark

• TenantId
• Hash
• ActivityType
• ActivityId
• WorkflowInstanceId

Most of the above fields are being queried or ordered by, and some of them I suspect will eventually be used in a query.

[sfmskywalker]

Yes exactly 👍

[DavidPx]

I hit a snag while creating the migration for SQL Server. SQL Server doesn't allow indicies on nvarchar(max) fields so the EF changed the datatypes of the affected string fields to nvarchar(450). While I'm using SQL Server 2019, that appears to be a compatibility selection to pre-2016 versions which capped the index size to 900 bytes.

Here is the relevant migration: DavidPx@a527990

What are your thoughts on this?

[sfmskywalker]

Which field does this affect? I’m pretty sure we don’t need nvarchar(max) for any of the fields to be indexed, so would be fine to update the entity configurations as necessary.

[DavidPx]

See the commit I linked - essentially all of the string fields that you listed in your earlier response.

I would agree that 450 characters is plenty for things that we're indexing.

[sfmskywalker]

Ah I see. Yeah I don’t see an issue with this. Of course strictly speaking of the persistence provider imposes a max length on fields, one might argue that we should enforce some limit in the domain itself, but doesn’t sound worth the trouble to me.