Generated SQL creates a large number of cached query plans in SQL Server

[IanPattersonMuo]

Akka Version: 1.3.15
Akka Component: Akka.Persistence.Sql.Common & Akka.Persistence.SQLServer
Platform: Linux Containers

SQL generated by Akka for accessing the EventJournal and the SnapshotStore create parameters with different sizes. This has an impact on SQL Server specifically, but other backends may also be affected. SQL Server Generates a query plan based upon the parameters passed, so when they are different in size then another query plan is generated rather than using an existing plan. In our production environment the insert/update statement for SnapshotStore created 15,523 different query plans in the plan cache. This makes repeated execution of the statements more expensive as it must search the cache first, before generating a new one should it not find a match. The main difference between the plans was the the size of the @Payload parameter varbinary field being passed. The EventJournal queries have a similar problem.

The AddParameter method within the Akka.Persistence.Sql.Common/Snapshot/QueryExecutor.cs and the Akka.Persistence.Sql.Common/Journal/QueryExecutor.cs does not take a size field so it is using the size of the data passed which causes the problem for MS SQL Server, it would be better to take a size field that would be set to the same size as the column. However this could have a detrimental effect on implementations for other Databases that do not have query plan caches.

It looks like it would be possible to override the methods in the Akka.Persistence.SQLServer project but this would mean copying large amounts of code to change a small amount of code. Does anyone else have an opinion on the best way to resolve this ?

[Aaronontheweb]

Thanks for reporting this @IanPattersonMuo - that's obviously not desirable behavior on the part of the Akka.Persistence plugin. We can implement different queries for different SQL implementations as that's plugin-specific behavior. For SQL Server, what I would recommend:

1. Allow end-users to pass in settings for the varbinary size on those fields - I'm imaging all sorts of scenarios where we specify a setting within the project and it ends up being too small for a large number of users who have really large binary snapshots, so we're probably better off "staying out of it" or if we pick a really large byte size value for varbinary (gigabytes or hundreds of megabytes) and it doesn't have an adverse effect on other stages of query execution, maybe that would be a good out of the box solution.
2. Document this as a potential performance concern inside the Akka.NET documentation and the SQL repository specifically.

[ghost]

@IanPattersonMuo - My PR on Akka.Persistence.SqlServer as referenced above should resolve the issue.

[Aaronontheweb]

That fix looks good to me

[Aaronontheweb]

closed via akkadotnet/Akka.Persistence.SqlServer#132 - going into Akka.Persistence.SqlServer v1.3.17.