18

Query:

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

Message:

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?

buhtz
  • 10,774
  • 18
  • 76
  • 149
Softlion
  • 12,281
  • 11
  • 58
  • 88
  • I had a very similar problem. For me it was a simple fix: Try adding AsParallel() to the Query, just prior to the ToList() or Count(). This option splits the query up and executes them separately, joining the result. Who know's you may get a performance improvement too. – Rob L Jun 23 '17 at 03:47

2 Answers2

23

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

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

See also related connect item

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Well you can not insert values into a #temp table, it fails with the same error. – Softlion Oct 20 '11 at 09:01
  • SQL Server Management Studio crashed when the syntax analysis scans the text (too much lines ?). So i used visual studio, cut the list in 10 groups of 10k lines each, and run the query 10 times ... – Softlion Oct 20 '11 at 09:02
  • @Softion - What fails? The insert to a `#temp` table or using the `#temp` table inside the `in`? – Martin Smith Oct 20 '11 at 09:03
  • The insert fails with the same error. Or i missed something ? – Softlion Oct 24 '11 at 16:36
  • 2
    Put the 50000 values in a CSV file and use BULK INSERT to insert the data into a table. Then you can work with it. – Alexander Sep 08 '20 at 09:50
0

If you find this error in SQL Server 2016 or later then try to change the database compatibility level to a lower version 120 or 110, etc. (link)

1001Soft Company
  • 31
  • 3