Can we take it a step further, i.e., an article that explains what to do with the results obtained - from both a query rewrite and a hardware POV?

I ran a sample query that linked two tables, each with 200million rows, looking to return 1001 records using where 'id' between. I don't think 4.6 seconds is bad, but I always look for better.

I'm heavily indexed on both these tables - and have just recently completed a rewrite of the entire process from our front end web search tool to the redesign of the database and all the associated stored proc's. The data gets a update of about 700k records a week, indexing is dropped, records inserted, and indexs reapplied (and statistics re-computed). It works, seems like it generates fast responses - but it takes 6-8 hours to process.

Like the others above I found that for short data sets table var's are better, and for temp tables are better for larger returns. My SAN's are normalized (set up to be read/write neutral as opposed to one being faster than the other).

My overall questions(based on the results below) are

How do I reduce logical reads, and what does that mean.

How do I reduce physical reads.

What's a read-ahead - and why did it need 916 when it was 1001 records (it was queried on the primary clustered key - should it not have 'known' via the indexing, statistics and bookmarking and not needed to read ahead).

The primary table is a 3rd normal ID only table in it's own database, the secondary table is a loose 2nd normal (lots of text, some id's) in it's own database consisting of one year's data (there are 7 secondary db's).

Hope that was enough info to go on.

my results:

Table 'primary'. Scan count 1001, logical reads 4859, physical reads 6, read-ahead reads 7.

Table 'secondary'. Scan count 1, logical reads 1007, physical reads 0, read-ahead reads 916.

SQL Server Execution Times:

CPU time = 1734 ms, elapsed time = 4675 ms.