After executing a query with many values inside
of an “IN” clause in a SQL Server database, we can receive the errors 8623 or
8632, described below:
Error 8623:
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.
Error 8632:
Internal error: An expression services limit
has been reached. Please look for potentially complex expressions in your
query, and try to simplify them.
These errors can occur when an extremely large
number of values are used inside of an “IN” clause. This may consume
excessive resources by the server, and generate performance problems, causing one
of the errors above.
However, we must pay attention to deal with
cases like this because often these values can be written automatically by an
application, which can make the diagnosis of the problem harder.
To workaround this problem, we must rewrite the
query, reducing the number of values inside of the “IN” clause. Or, if we
need to use several values inside of an “IN” clause, we must replace them
with values in a table. This will minimize the performance impact on the server,
avoiding the errors above.
More information in the article below:
- IN (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms177682.aspx
No comments:
Post a Comment