>>>I think the second should be faster, so I would be surprised if you got different results in your tests.
>>
>>What's a good way to test so as to eliminate the effects of cached data and stuff? The data is on free tables on a server on our lan, and I am runnnig the app on my desktop, so the data is coming over the LAN.
>>
>>I want to be sure I do not mess up the results by having it the data cached or something.
>
>So, what were the results of your tests?
Well, I got a little side-tracked on some other work, but I have I will complete the testing. For now I am running (thanks to your help, BTW) with the Derived Tables, approach, and I do like the paradigm of it. I found have confirmed that Derived Tables in indees the proper and formal name for this, and I found a helpful link (for newbies, at least) at
http://techahead.wordpress.com/2007/10/01/sql-derived-tables/Basically, though, in the Derived Tables configuration, the query takes about 8-9 seconds.
You see, I am now querying the child records (about 4 sets of them) to calculate in real-time some totals that I used to maintain as reference numbers already sitting on the header record. So, the previous query was very fast, because I was just pulling values right off the header table. So, my users are use to a 1-2 second query, but the reference data could sometimes be out of sync with the actual child records. So setting them up for 8 second queries is not to great, but the data is always accurate.
It was generally not mission-critical reference data, so evern if it was out of sync a little, they would never actually make major decision from that data anyway. They would always get a details report to work from.
However, I like knowing how to properly get real-time data, and now I feel like I do.