Consider option to turn off/on bucketization

[cincuranet]

In EF 10 we introduced support for "bucketization" (#36364). This issue is a place to gather feedback whether option to turn it off/on makes sense to implement. Please provide examples and numbers, not just wishes.

[ErikEJ]

@cincuranet Is there some overall design document for in IN changes for EF Core 10?

Is this on by default for all providers?

[cincuranet]

Is there some overall design document for in IN changes for EF Core 10?

Not yet.

Is this on by default for all providers?

Yes.

[IanKemp]

My suggestion would be a new UseParameterBucketization(Func<int, RelationalTypeMapping, int>? determineSize = null) method on the RelationalDbContextOptionsBuilder-derived providers that intend to support bucketisation (i.e. this feature should be on a per-provider level).

If this method is not called, parameter bucketisation is disabled entirely. Default behaviour.
If this method is called and determineSize is specified as null, then bucketisation is enabled, using the provider's defaults.
If this method is called and determineSize is not null, bucketisation is enabled using the provided delegate, which accepts the number of parameters and return the bucket size to "pad" them to.

For the MSSQL provider, the default implementation will be src/EFCore.SqlServer/Query/Internal/SqlServerSqlNullabilityProcessor.cs#CalculateParameterBucketSize.

[roji]

@IanKemp what's the exact reason for these suggestions? Different providers can already override the bucketization strategy to suit their needs (and the SQL Server provider already does that), but in which cases do you think users would actually want to tinker with the bucketization strategy? Of course, it's always possible to ask for more knobs and options (and we can always add some in the future), but tweaking the bucketization thresholds is something that - in my opinion - is well beyond the level of most users.

More importantly, why do you believe that disabling bucketization is the better default? The original issue that bucketization attempts to mitigate - different query SQLs for different cardinalities resulting in different plans and cache bloat - is something that affects a great deal of users, and I don't believe users should need to go through advanced performance docs in order to know that bucketization exists. EF should be fast by default - out of the box - without the need for tweaking/tuning.

Finally, for the advanced user wanting to tinker with this, there's always the possibility of overriding the service that implements this and overriding the method responsible for bucketization. Yes, this is advanced and somewhat obscure, but that's generally how it should be for niche tinkering optimizations. We generally only add knobs on DbContextOptionsBuilder when we believe they'd be useful for the average user, and that doesn't seem to be the case here.

[IanKemp]

@IanKemp what's the exact reason for these suggestions? Different providers can already override the bucketization strategy to suit their needs (and the SQL Server provider already does that), but in which cases do you think users would actually want to tinker with the bucketization strategy? Of course, it's always possible to ask for more knobs and options (and we can always add some in the future), but tweaking the bucketization thresholds is something that - in my opinion - is well beyond the level of most users.

More importantly, why do you believe that disabling bucketization is the better default? The original issue that bucketization attempts to mitigate - different query SQLs for different cardinalities resulting in different plans and cache bloat - is something that affects a great deal of users, and I don't believe users should need to go through advanced performance docs in order to know that bucketization exists. EF should be fast by default - out of the box - without the need for tweaking/tuning.

Finally, for the advanced user wanting to tinker with this, there's always the possibility of overriding the service that implements this and overriding the method responsible for bucketization. Yes, this is advanced and somewhat obscure, but that's generally how it should be for niche tinkering optimizations. We generally only add knobs on DbContextOptionsBuilder when we believe they'd be useful for the average user, and that doesn't seem to be the case here.

Then when you announce bucketisation-by-default as an EF Core 10 SQL Server provider feature, you need to specifically document this advanced and obscure way to control (including disable) that feature in those very same release notes, so that users who want or need to do so are able to.

[roji]

Thanks for the suggestion - we'll see what we include in those release notes. I don't believe it's generally a good idea to overload release notes and docs meant for beginners/the general public with very advanced/obscure things. There are many corners and edges of EF which are unfortunately not 100% documented, I'd be interested in seeing how many users actually want to do this. I'm again curious why you have strong feelings about it.