Bug in SELECT IN SELECT?

Hello,

we had a performance issue with our application. The application executed a lot of SELECT IN SELECT-Statements.

Because SQL-Server was at 100% the whole time we started the profiler and had a look. A single one of these SELECT IN SELECT-Statements had a duration from 4 to 6 seconds. That looked strange, so we tried it without our application. Executing the SELECT IN SELECT-Statement directly: 4-6 seconds. Very strange about this is, that the second SELECT (the one after IN) returned only one or two items. So we splitted it: Executed the second SELECT first. Then executed the first statement with manually changed IN-clause. Both selects didn't need any noticable time. So, back in our application we did the same. The result was that the process that took about 70 minutes with the SELECT IN SELECT run through in under 3 minutes with result = SELECT; SELECT in result.

Pretty strange, hu?

Is this a known bug? Is this a bug at all?

the query plan might give some clues to the bad performance. also, you might consider converting your IN() query into an Inner join.