This seems so fundamental that I almost think I shouldn't mention it but I will anyway.

When designing a database, its indexes, and the queries that access it, do everything you can to:

1. not use the database at all (I've known programmers who used it for logging when a flat file would have been much more efficient.),

2. avoid physical disk writes (they are significantly slower than disk reads),

3. avoid physical disk reads.

Your hard drives are the slowest components on your system by several orders of magnitude.

Do everything you can to design for the use of the system and database caches. This will speed up everything. This means lots of memory dedicated to SQL Server and correct indexing of all search fields.

And no correlated sub-queries - period.

LC