fleshyorgans

Maybe someone will find this post and explain to me why SQL Server behaves like this. My question isn’t “how should I rewrite this query”, but “why does SQL Server act like this”. The behavior I see is that for some reason it’s easier to do TOP bignumber than TOP smallnumber. In my instance, TOP 3 takes up to 2 minutes to return results, whereas TOP 5 is instantaneous.

Situation, selecting the first 3 Announcements for particular User Roles at a particular Organization.

There is an announcement table and a meta table containing org/role join information.

The (somewhat crappy) query is like so:
SELECT TOP 3 b.announcement_title FROM announcements b
WHERE b.anouncement_id IN (
SELECT
  DISTINCT a.announcement_id
FROM
  announcements b, announcement_role_org aro
WHERE
  aro.announcement_id = b.announcement_id
   AND
  aro.role_id = $role_id
   AND
  aro.org_id = $org_id
)


Yes, I know it should have been written as a join.

This is the problem:

• When the outer select says TOP 3, the server chugs forever and doesn’t return a result (at least, doesn’t return a result within 2 minutes, I killed the select after that time limit).
• When the outer select says TOP 5 or TOP 6, it returns results fairly instantly

WTF?
For the record, the inner select for the $role and $org I specified only ever return 6 results.

Oh, lastly… if I say NOT IN (returns 210 results), it doesn’t seem to matter what TOP value I have.

WHY???