SQL Server Error 8632 due to over 100,000 entries in WHERE clause

Question

My problem (or at least the error message) is very similar to query processor ran out of internal resources - extremely long sql query.

My customer is working with an SQL select-query, containing a where-clause with exactly 100,000 entries.

The query is failing with error 8632 and error message

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.)

I find it very peculiar that this error message is thrown, exactly at 100,000 entries, so I wonder if this is a configurable value. Is this the case and in case yes, how can I increase this value to a higher one?

On MSDN, there is the proposal to re-rewrite the query, but I'd like to avoid this.

Meanwhile I've found out that the list of entries I'm talking about contains natural numbers, quite some of them seem to be sequential (something like (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20).

This makes the SQL where-clause something like:

where entry in (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20)

I could transform this into:

where (entry between 1 and 3) OR
      (entry between 6 and 10) OR
      (entry between 12 and 13) OR
      (entry between 15 and 20)

Can this be shortened by:

where entry in (1,...,3,6,...,10,12,13,15,...,20)

...or something similar? (I know it's a long shot, but it would make software updates easier and more readable)

For your information: the data in the where-clause are the result of a calculation, done on another table: first the entries of that table are read and filtered at the beginning, then some extra processing is done (which is impossible to do using SQL), the result of that extra processing is more filtering and the result of that is used in the where-clause. As it was impossible to write the complete filtering in SQL, the mentioned method has been used. Obviously the content of the where-clause might change at every processing, hence the need of a dynamic solution.

Answer 1

To search for more than 100,000 values, put them in a temp table instead, one row per value that you're searching for. Then, join your query to that temp table for filtering.

Something with more than 100,000 values isn't a parameter - it's a table. Rather than thinking about raising the limit, consider Swart's Ten Percent Rule: if you're approaching 10% of a SQL Server limit, you're probably going to have a bad time.

Answer 2

If you're going to change the app anyway, consider either

(a) using a TVP for the entire set of values - you would create a DataTable in C# and pass it into a stored procedure using StructuredType, as I demonstrate here. (Hopefully 100,000 entries is not normal, since scalability might be an issue no matter what approach you use.)

CREATE TYPE dbo.optionA AS TABLE(value int PRIMARY KEY);
GO

CREATE PROCEDURE dbo.procedure_optionA
  @t dbo.optionA READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT <cols> FROM dbo.<table> AS t
    INNER JOIN @t AS tvp
    ON t.entry = tvp.value;
END
GO

or

(b) using a TVP to pass in the upper and lower bounds of the ranges, and writing a slightly different join (thanks @ypercube).

  CREATE TYPE dbo.optionB AS TABLE
  (
    LowerBound int,
    UpperBound int,
    PRIMARY KEY (LowerBound, UpperBound)
  );
  GO

  CREATE PROCEDURE dbo.procedure_optionB
    @t dbo.OptionB READONLY
  AS
  BEGIN
    SET NOCOUNT ON;
    SELECT <cols> FROM dbo.<table> AS t
      INNER JOIN @t AS tvp
      ON  t.entry >= tvp.LowerBound 
      AND t.entry <= tvp.UpperBound;
  END
  GO

Answer 3

No it is not configurable and you cannot increase this to a higher one.

There are workaround suggested in the MSDN article you mentioned and other articles. I mentioned two here but you can search for more.

• Error message when you run a query in SQL Server 2005: "Internal error: An expression services limit has been reached" by Microsoft.
• “IN” clause limitation in Sql Server

Answer 4

Just my 2¢ regarding making the query condition shorter:-

If you're able to determine all the possible values of entry in advance, would it feasible if you take the complement of your query?

Before

where entry in (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20)

After

where entry not in (4,5,11,14)

Answer 5

It's hard to figure out what you're trying to accomplish without actually being able to see the query, but here we go, assuming that your query only needs two tables, one with the data, one with the values you want to avoid:

• Using where entry in (<list of 100.000 values>) is outrageously awful, both from efficiency and maintenance point of view.
• Using where entry in (select whatever from table) is barely as atrociously awful as the one before. Yet, depending on both tables idiosyncrasy and the SQL engine, it might perform OK despite the cornea cancer. (Might be OK in Oracle, will never be in MySQL, can't remember about MSSQL).
• Using PLSQL to achieve this is simply horrendous.

• In my opinion (without knowing the query at all), you should rewrite the query as follows:

  • If those 100.000 values are always the same, not relying on the rest of the query, you should upload those values to a table (table_fixed_values), and use

    SELECT * -- whatever you might need
    FROM
       table_a A
       LEFT JOIN table_fixed_values ON A.entry=B.entry
    WHERE B.entry IS NOT NULL
    

  • If those 100.000 values aren't the same, there must be some kind of logic to pick those 100.000 values up, logic that you should embed in the ON of the previous query.