SQL Server 查询处理器耗尽内部资源

问题描述

查询:

update mytable 
    set mycol = null
    where id in (
        583048,
        583049,
        ... (50000 more)
)

留言:

查询处理器耗尽了内部资源，无法生成查询计划.这是一个罕见的事件，只预期极其复杂的查询或引用非常大的查询表或分区的数量.请简化查询.如果你认为您错误地收到了此消息，请联系客户支持服务以获取更多信息.

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.

我的查询很简单，我应该如何编写它才能正常工作?

My query is very simple, how should I write it so it works ok?

推荐答案

将值列表插入到 #temp 表中，然后在其上使用 in.

Insert the list of values into a #temp table then use in on that.

如解释在这个答案中，大量的 IN 值会导致它在扩展为 OR

As explained in this answer a large number of IN values can cause it to run out of stack as they get expanded to OR

另见相关连接项

这篇关于SQL Server 查询处理器耗尽内部资源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！