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:
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.