Hi Thomas,
I was mainly curious about the speed differences between the different ways of doing this kind of lookup, but for the most part, you're right that an application shouldn't be shuttling vast amounts of data over the wire anyway. <g> But, in order to get any kind of significant numbers in the benchmarking, I felt I needed large numbers and that's why I compared with 100, 1000 and 10000 rows. But, in the real world, I wouldn't have such large numbers.
However, that said, I can see where this type of benchmarking might be useful to know in situations where one might be doing some batch-processing of large amounts of data, maybe something that's not done on a day-to-day basis in your app, but perhaps a once-in-awhile kind of thing, or maybe a month-end report or some such stuff. You'd probably want that optimized. So, you're right that tnis might require some additional benchmark tests ... particularly with setting a PK and seeing how fast the .Find is. But, I'll leave that to others for now, I've already wasted several hours on this benchmarking. Wanna do it? <g>
BTW, the RAM on my laptop isn't bad, 512, but it isn't exactly a lot either. Another interesting benchmark (if anyone were interested in trying it) would be to try the same amount of data on different computers with differing RAM to see how much RAM plays into it.
~~Bonnie