MS SQL 2K Bug: Query with Large IN Clause Results in Stack Overflow

Posted by Dan on Nov 17, 2005 @ 3:12 PM

On a site I'm managing, there's a query that was written by another developer that I've been trying to speed up. The query searches over a text column in a MS SQL 2k database. The table this column is in contains several million records and the query performance isn't up to par.

Anyway, I've been trying anything I can think of to increase performance over this query. Oddly enough, in gentle testing using SQL Query Analyzer it was indicating I might get better performance from this operation by spitting thing into two seperate queries. This lead me to find this bug:

BUG: Query with Large IN Clause Results in Stack Overflow

I found this bug by passing in a large list of primary keys from the first query into an IN statement with the second query. In ColdFusionMX I kept getting the error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Internal Query Processor Error: The query processor ran out of stack space during query optimization.

In SQL Server I was seeing the message:

Invalid buffer received from client.. Error: 17805, Severity: 20, State: 3

I suspect the problem was that I was passing in too long of an primary key list, but that Microsoft TechNote confirmed my suspicions. Anyway, that's a good thing to keep in mind when you're developing applications. It's normally never a good practice to split queries up to begin with and this kind of problem is just icing on the cake.

Categories: SQL, HTML/ColdFusion, Java

4 Comments

  • Ken Smith's Gravatar
    Ken Smith
    You could populate a temp table with the PKey values and then do a join against the temp table instead of using an IN clause.
  • venkat ram reddy's Gravatar
    venkat ram reddy
    Large in clauses has to validate various check conditions at the database server. The logical operation is getting performed at the database. Please check the buffer pool size and shared pool size apart from the CPU utilisation and memory utilisation.
  • David Levin's Gravatar
    David Levin
    You could also run your query without the large IN statement and then run a query of queries (QofQ) that will select all of the records from your original query as well as filter out the results based on your large IN statement.
  • Dan G. Switzer, II's Gravatar
    Dan G. Switzer, II
    @David:

    QoQ's generally shouldn't be used for filtering data--especially in a case like this. This query would pull back thousands of records with lots of data. Not only do you have to retrieve all data from the database server (and this case pull it over the LAN) it can also cause memory problems on the server with lots of load.

Comments for this entry have been disabled.