SqlServer Error 8623 The query processor ran out of internal resources and could not produce a query plan

[xaviergxf]

When doing the following sql with ids containing more than 16000 ids:

db.Table.Where(p=>p.ids.Contains(p.ID)).ToList();

I'm receiving the following exception:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

I was expecting Sql Server to be able to filter the results correctly.

Is it possible this query into multiple times to avoid this error?

Further technical details

EF Core version: 2.01
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 7
IDE: Visual Studio 2017 15.5
Sql Server: 2008 SP2

[ErikEJ]

SQL Server is limited to 2100 parameters https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server

[xaviergxf]

Hi @ErikEJ Can EF Core split the queries every 2100 parameters?

[ErikEJ]

Not currently, but you could!

[smitpatel]

@ErikEJ - It does not look like query has any closure variable which would be parametrized. It should be fully converted to SQL without any parameters.
@xaviergxf - What is the generated SQL? The error is coming from SqlServer itself. I believe there is nothing EF Core can do about it if the SQL we sent to server runs out of resources as we would have no way of knowing if a generated query could cause it.

[xaviergxf]

@smitpatel unfortunately i couldn't trace the query with sql server profiler, aparently it is too big, too many parameters. What is funny is that i could do the same query logic by doing a select without any where's and filtering in memory my poco list.

[smitpatel]

@xaviergxf - Can you share the linq query then? I am not able to see how the query in first post could generate a large SQL

[xaviergxf]

@smitpatel i cannot post the linq query. The query does a left join as well, that is perhaps the reason why it cannot be split into multiple queries.

[ErikEJ]

Where is the left join in the query in your original question?

[xaviergxf]

It wasn't indeed. I removed the left join and i'm still receiving this error. The contains is done on a list of int.

[ralmsdeveloper]

@xaviergxf.
Is the list of "ids" sequential?
If so, you can solve this temporarily like this:

db.Table.Where(p=> p.ID>=p.ids.First() && p.ID<=p.ids.Last()).ToList();

[ralmsdeveloper]

@smitpatel & @ErikEJ
For a better understanding of what is happening:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql#remarks

updated the link to specific section

[smitpatel]

Work-around, split the ids list manually on client side. Since documentation does not provide a upper bound of values which can be included, it could be difficult to solve this in EF Core. (i.e. if the upper bound is based on size? int/string/guid could have different sizes). Even if EF Core breaks query in multiple parts based on some number and combine results on client, it would work only simple cases. If the query is being composed further then it could cause a huge client evaluation. And the logic of splitting can get really complex.

The SQL work-around to use temporary table could be useful in this scenario but we cannot apply that for all cases.

[xaviergxf]

@ralmsdeveloper unfortunately the ids are not sequential... i have managed to solve this with an workaround. @smitpatel i agree with you that this should be handled somehow on sql server.

[ajcvickers]

Triage: Agreed that this is a SQL Server issue. Assigning to @divega to suggest some other ways to get the same data back with different kinds of queries.

[divega]

Here are a couple of ideas for workarounds:

• Separate the ids in chunks of a fixed size. I wrote some code snippets that do this long time ago to address a different problem on the original EF: https://stackoverflow.com/questions/7897630/why-does-the-contains-operator-degrade-entity-frameworks-performance-so-drama/7936350#7936350.

• As suggested by @anpete, use FromSql to do the containment check with an IN clause at the root for your LINQ query, but passing table-valued parameter containing all the ids. I haven't tried this for some time, but it used to work, and I have a code snippet for it at https://gist.github.com/divega/f0f88bf16f35641239cfd9bc534e8d7c.

[divega]

Clearing up milestone in case we want to do something more with this issue before we close it.

[ajcvickers]

Closing as nothing to do on the EF side. If any of Diego's ideas are to be productized, that would happen as part of TVP support.

[GertArnold]

EF 8.0 has native support for this now: #13617.

For lower version, see this Stack Overflow question.