OK gentlemen, I restarted my workstation fresh and did some more testing on this issue of EXISTS vs IN. I took the first 2 queries of my code using EXISTS and the same queries using IN and alternated between running each of them. I ran all four a total of 5 times and calculated the following averages using only this batch of test runs:
Using EXISTS:
1st Query = avg 225.67 seconds
2nd Query = avg 297.89 seconds
Using IN:
1st Query = avg 266.40 seconds
2nd Query = avg 326.15 seconds
So I'm seeing approx. 30 seconds difference here, with EXISTS being faster. (and seriously doubting I could prove 2 Fox Gods wrong this easy) If I have time tomorrow I might try this exercise again on the server, to eliminate any question of untimely network traffic.
So what's your opinion now? Still think IN is faster/better performance? People always say my environment is abnormal so I wont be insulted if you tell me this example doesnt change your opinion :D) Also, do you think that since I'm using 2.6 tables as free tables in VFP has an effect on these results? Someday I'll have all my tables in a VFP dbc, and thats when I'll really put what I learned with this thread to use. TIA for your all your help and valuable opinions.
Roxanne M. Seibert
Independent Consultant, VFP MCP
Code Monkey Like Fritos