SQL server query processor ran out of internal resources

Question

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?

Bonitabonito

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. – Navicert

Answer 1

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

Alpine

Well you can not insert values into a #temp table, it fails with the same error. – Bonitabonito

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 ... – Bonitabonito

@Softion - What fails? The insert to a #temp table or using the #temp table inside the in? – Alpine

The insert fails with the same error. Or i missed something ? – Bonitabonito

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. – Crazed

Answer 2

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)

Limy

Answer 3

There is an easy workarround, split your predicates Replace this

SELECT * FROM TABLE
WHERE ID like (1,2,3, ... 10000)

By

SELECT * FROM TABLE
WHERE ID like (1,2,3, 999) OR 
ID like (1000, .. 1999) OR 
... OR 
ID like (9000, .. 10000)

I encountered this error in EF and I fixed it using linqkit 1.2.4 package. I replaced

query.Table.Where(t => ids.contains(id))

by

var dividedIds = DivideArray(ids.ToArray());
foreach (var batchPortalIds in dividedPortalIds)
{
     predicate.Or(s => batchPortalIds.Contains(s.PortalId.Value));
}

Where DivideArray is a function transforming single array to list of array

    private List<int[]> DivideArray(int[] originalArray)
        {
            int batchSize = 1000;
    
            List<int[]> dividedArrays = new List<int[]>();
            int currentIndex = 0;
    
            while (currentIndex < originalArray.Length)
            {
                int batchSizeRemaining = Math.Min(batchSize, originalArray.Length - currentIndex);
                int[] batch = new int[batchSizeRemaining];
    
                Array.Copy(originalArray, currentIndex, batch, 0, batchSizeRemaining);
                dividedArrays.Add(batch);
    
                currentIndex += batchSizeRemaining;
            }
    
            return dividedArrays;
        }

Ranking

Answer 4

I know this is a really old question but I would put load the data into a table variable and then use a join.

something like:

declare @t as table (ID int primary key)

insert into @t
'however you want to do this'

update mt
    set mycol = null
from MyTable mt
    join @t t on t.ID=mt.ID

Izzard

Answer 5

Not sure how you're generating the list of values for your IN statement, but I have had good experience getting around this issue using a little select statement trick. The statement needs to end up looking like this:

update mytable 
    set mycol = null
    where id in (
        select value from (values (583048), (583049), . . . ) as temp (value)
)

Challenge is that you have to get those values into the right format for your query, which may require you to go outside of sql.

example from R with sprintf (but most scripting languages will have something similar):

sprintf("select value from (values %s) as temp (value)", toString(paste("(", table$value, ")", sep = "")))

You can also get around the "INSERT" limitation this way, eg:

create table #tempvalues (value numeric)
insert into #tempvalues (value) select value from (values (583048), (583049), . . . ) as temp (value)

Dobruja