CODEWITHSUNDEEP
sql-servert-sql
Softlion
Softlion

Reputation: 12595

SQL server query processor ran out of internal resources

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?

Upvotes: 21

Views: 55905

Answers (5)

Ben H Min
Ben H Min

Reputation: 1

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)

Upvotes: 0

KeithL
KeithL

Reputation: 5594

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

Upvotes: 0

abmptit
abmptit

Reputation: 15

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;
        }

Upvotes: 0

softwarer
softwarer

Reputation: 49

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)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453287

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

Upvotes: 25

Related Questions