Adding user selectable newer AdoNet.Persistence features

[enewnham]

Background

Databases can be optimized for the storage requirements of Orleans. However the land of SQL databases is not a one-sized fits all solution.The current implementation is good however, it is optimized for the least common denominator for the most use-cases.

Here's a few things I've tested that make an improvement in AdoNet performance.

• Stored procedures
• Fixing parameter sizes
• Compression
• Hash Bucket indexes
• In-Memory optimized OLTP

All those stated points have tradeoffs and require certain licenses based on what version you have installed... Joy...

The Question

How would the Orleans team perfer a developer who is bootstraping/performance tweaking to learn/discover/choose the optimized AdoNet SQL plan for them?

1. One-sized fits all is fine for this repository. If you want an optimized storage, Use OrleansContrib
2. Have multiple bootstrap scripts... Such as AdoNet/Orleans.Persistence.AdoNet/SQLServer-Persistence-methodA.sql or AdoNet/Orleans.Persistence.AdoNet/SQLServer-Persistence-methodB.sql
3. Have a fancy one that determines the supported features of your SQLServer and does it for you. (That might make poor decisions based on your specific workload)

See https://github.com/dotnet/orleans/pull/7010 and https://github.com/enewnham/Orleans.Providers.MSSQLDapper

[veikkoeeva]

Thanks for bringing this up, @enewnham and going throught the trouble (also thanks to the DBA if there was one involved, taken from Gitter).

I would say multiple bootstrap scripts if it's possible to come up witha common scenario. I think many use Azure SQL Server and something like this would be of broad interest. This would show it is possible and even advisble given one's circumstances and deployment characteristics

A contribution repository works also, so I don't see that as a big hurdle. There is just more distance. The OneBoxDeployment does some small modifications also by introducing schemas and filegroups and using dacpacs for deployment, just to some it as for an example.

Fancy scripts that could determine the deployment in this case probably do not work as intended. I could imagine also having a table like that but then another as an in-memory one with very limited column lengths (maybe with binary payload) if it is known the lenghts do not exceed some limit. This could be a caching situation.

In the fancy scripts category, or better as in alternative deployments category, could imagine various table splitting scenarios would be useful. I think as of this writing both MySQL and PostgreSQL (Citues) intorduced new and greatly enhanced table splitting scenarios.

These are in general important considerations since they also affect disaster recovery and business continuity scenarios. Not every come to think of them immediatelly and we could be more explicit about them, somehow, and offer alternative scenarios, somehow.

So I would put effort on 2., I think 1. works also. Then 3. looks like is not possible in practice, because reality dictates too alternative scenarios.

[amccool]

What edition/version of MSSQL would you be targeting? We could definitely use faster write commits. When doing batch operations over aggregates we have to give up on consistency to get acceptable performance (which generates support calls).

As it were we are on-premise with all customers using MSSQL enterprise edition. However it appears that your schema would support standard edition just fine.

[ghost]

We are marking this issue as stale due to the lack of activity in the past six months. If there is no further activity within two weeks, this issue will be closed. You can always create a new issue based on the guidelines provided in our pinned announcement.

[ghost]

This issue has been marked stale for the past 30 and is being closed due to lack of activity.