@ Ron:

I go pretty far back with SQL server too, and would never have even considered using "while not exists" over a "left join" to retrieve orphans until I skeptically read an SSC article and then proved to myself how efficient that approach is.

I also was convinced that the query engine would loop through the "not exists" query for each row in the "Left" table. But I did some performance testing and also compared estimated query plans between a good ol' left join and not exists and was amazed to see that often they had identical plans (This was probably on SQL 2000 or 2005.)

I also had to begrudgingly admit that, for me anyway, the "where not exists" syntax is more intuitive even though I wasn't used to it.

This is no substitute for testing but, as a side note, there's a quick and dirty trick you can do with query plan estimates to compare queries. If you put them in the same SSMS query pane and then display the estimated exection plan for the "batch", the plan will show you what percentage of the total estimated cost each query is "relative to the batch". Take it with a grain of salt, though! The engine may decide to use a different plan at run time.

I understand now what you meant by power of indexes. I tend to think of them as internal "tables" that are available to the query optimizer. I'm far from being an expert on query plans, but I can get enough out of poking around plans for simple test queries like these to realize that the engine is definitely using the indexes.

The next time you find yourself about to make a temp table containing just the columns you need, I suggest looking into using include columns in indexes (apologies if you already are using them.) because that's exactly what they do. Obviously indexes use disk space and can slow down writes so there are tradeoffs, but they are always available and don't have to be built "on the fly" like temp tables. If you're always pulling columns A, B, and C from a superwide table using a non-clustered index on column D, it may make sense to put A, B, and C in your column D index as includes.

I understand your reasoning that plans are influenced by which tables contain returned data, but in this case the queries are all retrieving from the same tables, so presumably any differences in response times would be caused by differences in time required to identify which rows are being returned. There are reasons why that may not always be the case though (parallelism, random fetches vs block fetches, etc), so it is a good idea to create a data "sink" for doing serious testing. I've just found that not only does it take forever to return 5M rows to your screen, there are too many other variable factors such as network traffic or competition for workstation resources that can cloud query testing results when returning data to the client.

Yes hardware most definitely matters! It's interesting to note that on your testbed, the left join is the only one that ran single threaded when pulling data. The other two used parallelism. (I'm guessing the SQL engine had at least 8 cpus availabe versus the 4 available on the testbed I used.) It's also interesting that they all used parallelism when you tested using count(*).

Don't have the time now, but it might be interesting to perform the tests using OPTION (MAXDOP 1) to see how much those other CPUs are earning their keep! It would also be interesting to see if MAXDOP 1 affects queries that pull data more drastically than it does simple count(*) queries.

I tend to visit this forum sporadically also, so I don't know if there's an easy way to find articles grouped by topic. I tend to use the site's search function or sometimes bounce in from Google. Also, there are a number of reputable contributors here who I tend to search for when looking for answers.

SSC is a great resource if you verify things for yourself and also if you consider how close your scenario is to what is being presented. Most of the smattering I know about performance tuning I learned here!