問題描述
查詢:
update mytable
set mycol = null
where id in (
583048,
583049,
... (50000 more)
)
留言:
查詢處理器耗盡了內(nèi)部資源，無法生成查詢計(jì)劃.這是一個(gè)罕見的事件，只預(yù)期極其復(fù)雜的查詢或引用非常大的查詢表或分區(qū)的數(shù)量.請(qǐng)簡(jiǎn)化查詢.如果你認(rèn)為您錯(cuò)誤地收到了此消息，請(qǐng)聯(lián)系客戶支持服務(wù)以獲取更多信息.
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.
我的查詢很簡(jiǎn)單，我應(yīng)該如何編寫它才能正常工作?
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.
如解釋在這個(gè)答案中，大量的 IN 值會(huì)導(dǎo)致它在擴(kuò)展為 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
另見相關(guān)連接項(xiàng)
這篇關(guān)于SQL Server 查詢處理器耗盡內(nèi)部資源的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！