Query: SelectExpression Cache hit for InExpression coming from parameter

[smitpatel]

Currently when we have Enumerable.Contains in SelectExpression, the Enumerable comes in as a parameter and we generate a list of constant for InExpression. This means for each parameter we have to generate different SQL. (No second level query caching).

According to the idea from @divega,

Instead of putting a list of constant which would change every time, we can put a list of parameters in fixed number (let's say 10) and generate 1 SelectExpression. If the Enumerable has 10 elements then we just set parameter values and use SelectExpression from cache. If enumerable has lesser than 10 elements then we can copy the last value in remaining parameters just to fill in the values (it would have no effect on results). In case if the Enumerable has more than 10 elements then we can generate SelectExpression of higher number of params in InExpression or just use it without caching like we do today.

This could give us performance boost especially when SQL is large. Though at the same time plumbing of cache key would be slight complex.

[bmarder]

Would there be a practical limit of any of the thresholds? i.e. could we have caching for up to a 1000 item List<Guid> ?

[smitpatel]

@bmarder - Practical limit is number of parameters you can add in one command. (SqlServer has that limit). At that point using parameter variation is impossible and we have to write values in SQL explicitly. We can have caching upto 1000 items. This still needs some design in terms of what is the initial threshold or what should be step size to generate more SelectExpession. Using 100 parameters when list has only 2 elements is also inefficient.

[Tarig0]

Could it be possible to switch to a inner join after a threshold? Either as json,xml, or UDT? This would have to be on a per provider bases though.

[ajcvickers]

Closing this as a duplicate of #13617 since that issue has more details and votes.