Better execution plans - Githubissues

yv989c commented 8 months ago

It has caught my attention that the use of the OPENJSON function is causing performance regressions in EF8, as reported in https://github.com/dotnet/efcore/issues/32394. This seems to be caused by the opaque nature of the function to the query engine, which could result in suboptimal execution plans.

QueryableValues currently depends on both OPENJSON and XML methods for functionality, and thus, it is impacted to some degree by these issues.

Here are some potential solutions to address this problem:

For large collections, project the deserialized values to a temp table and use the temp table instead. This should enable statistics, which in turn should generate optimal execution plans. However, it may cause execution plan re-compilations and result in higher latency.
For small collections, avoid the use of deserialization functions and instead switch to a multi-parameter approach. This approach should generate optimal execution plans and maintain low latency.

These two approaches aim to provide the query engine with sufficient information to generate optimal execution plans, which is a priority. Consequently, this behavior should be enabled by default, with an option for users to opt out if desired.

nilzzzzzz commented 1 month ago

Hi @yv989c ,

thanks for building this awesome library. The described improvement would help us a lot, any plans to work on this issue in the next time?

Best

Nils

yv989c commented 4 weeks ago

Hi @yv989c ,

thanks for building this awesome library. The described improvement would help us a lot, any plans to work on this issue in the next time?

Best

Nils

Hi @nilzzzzzz,

First, thanks for the kind words.

Just an update on this: Yes, I've invested a lot of time working on this with good results, but I decided to pause to see how the EF team would address it. It seems clear now that they won't be providing the level of integration I've developed to make this work well with SQL Server. That said, if I release this, it will likely be under a different license model. Unfortunately, it’s not sustainable for me to continue putting this level of effort out there completely for free.

nilzzzzzz commented 3 weeks ago

Hi @yv989c ,

this sounds reasonable to me and thank you for your feedback. If the licence model fees work with our business model, we are happy to support you.

yv989c / BlazarTech.QueryableValues

Better execution plans #43