This is kind of a follow-on to a query I posted a couple of weeks ago, with some additional info that I've gotten in the meantime. It's getting more mysterious as time goes on, so I'm very interested in your feedback. ------------- Here's what I'm trying to do: Imagine you have a 100 million record table, with two columns -- ID (word offset) and word: ID word --- ----- 1 <beginning> ... 27304281 the 27304282 old 27304283 man 27304284 saw 27304285 the 27304286 rich 27304287 man .... 100000000 <end> Suppose I want to find the 20 most frequent words preceding 'man' in the 100,000,000 word database -- from these seven words they would be 'old' and 'rich'. Here's what I'm doing: Step 1. Create a table [words] with the structure shown above, with a clustered index on [word]. Step 2. Create an identical table [wordsID] with the structure shown above, but this time with a clustered index on [ID]. Step 3. Insert into a temp table [temp1] all of the ID values where [word] = 'man'. The temp table has a clustered index on [ID]. insert into temp1(ID) select [ID] from [words] where word = 'man' ## Step 4. (Problem) Run a join to find the 20 most frequent [word] in [wordsID], where the ID value is one less than the [ID] for 'man', which are now stored as [ID] in [temp1]: select top 20 count(*),b.word from [temp1] as a, [wordsID] as b where b.ID + 1 = a.ID ' i.e. the ID is one less than the ID in [temp1] group by b.word order by count(*) desc ------------ Here's the mystery. I have identical tables and indexes on two machines -- an older machine [older] running SQL Server 2000, where the database is on a RAID 5 with 3 SCSI HDs at 15,000 rpm each. The newer machine [newer] has SQL Server 2005 with in a RAID 0 with 4 SATA HDs at 7200 rpm each. Step 4 (the problem part) runs about 3-4x as fast on my [older] machine. Fine, you say, it's the hard drives == three 15,000 SCSI drives on the older/faster machine as opposed to the four 7,200 SATA drives on the newer/slower machine. The problem is, EVERYTHING ELSE is faster on the [newer] machines -- full table scans, etc etc. And the real mystery is, the Query Execution Plan for Step 4 above on both machines is identical. It's the "Clustered Index Seek" of [ID] on the table [wordsID], which in both cases has an estimated operator cost of 99% for the whole query. Also, when I get the stats for both machines, they are quite similar: [older/2000/15,000rpm SCSI] = 3-4x faster Table 'wordsID'. Scan count 23446, logical reads 164340, physical reads 156, read-ahead reads 16035 [newer/2005/7,200rpm SATA] = slower Table 'wordsID'. Scan count 23446, logical reads 206352, physical reads 133, read-ahead reads 17492, Any ideas on what might be going on? Again, everything ELSE is faster on the [newer] machine, so I have a hard time believing it's all due to hardware differences between the two machines. I'd say it's 2000 vs. 2005 (for some reason 2005 is slower for this join), but again, the Query Execution Plan at the IO stats are the same for both machines. I have to migrate the databases to the newer / 2005 / 4 SATA RAID machine soon, but I can't afford to take this performance hit. Any insights on what might be going on would be very much appreciated. ** Alternatively, if anyone can think of an alternative architecture that would still give me the desired results, I'd be happy to go that way instead. Thanks in advance. Mark D.