John,

first of all I'd switch testing around, always running the fast one first. That way I am CERTAIN that the fast version does not get a boost via some cached data I do not expect (might be the OS or even a HW cache on physical disk, not neccessarily vfp).

Then use sys(3054)/sys(3092) to be certain everything is happening as expected, throwing in some timed logging lines tracing dbf(), cdx(), order(), isexclusive(), isflock and so on. [I don't expect you goofed there from the way you describe your steps, but it does not hurt to cover all bases explicitly]

Try to run your timing with set exclusive ON, to find out the hard way if some process has the tables open in the live data directory you did ***not*** expect.


>Set exclusive off
>close databases all
>open database X:\data\inmatetrustfund.dbc
>select * from inmatetrustfund!bill_code_hist inner join inmatetrustfund!bill_codes on bill_code_hist.cbill_codes_id=bill_codes.cid into cursor lcresult
>-- takes 101.48 seconds --
>close databases all
>open database Y:\data\inmatetrustfund.dbc
>select * from inmatetrustfund!bill_code_hist inner join inmatetrustfund!bill_codes on bill_code_hist.cbill_codes_id=bill_codes.cid into cursor lcresult
>-- takes 5.34 seconds --
>

>When using the FoxPro command window for testing from the workstation, I am not setting TMPFILES. It seems with the test as described below, that can not be effecting me. Again, the test is:

thinking about some dbc-event code or similar - I do not have your knowledge about the system, just to verify unspoken assumptions.

There MIGHT be explicit pathing in the dbc causing the second SQL to use cached data - again, from the tone of your posts I think you checked that, but it does not hurt to verify.

>
>X = Live
>Y = Test (a copy of live)
>
>From a workstation using foxpro command window:
>Set exclusive off
>open database X:\data\inmatetrustfund.dbc
>select * from inmatetrustfund!bill_code_hist inner join inmatetrustfund!bill_codes on bill_code_hist.cbill_codes_id=bill_codes.cid into cursor lcresult
>-- takes 101.48 seconds --
>close databases all
>open database Y:\data\inmatetrustfund.dbc
>select * from inmatetrustfund!bill_code_hist inner join inmatetrustfund!bill_codes on bill_code_hist.cbill_codes_id=bill_codes.cid into cursor lcresult
>-- takes 5.34 seconds --
>
>We did turn OpLocks off on the server yesterday but it had no effect.
>

You might try with oplocks on, testing Y: when another WS also has the tables open. "No effect" is really surprizing.
Then you decide to either sniff the network to compare the amounts of data transfered or play around with the systems date and/or restore points you have of the OS from the times execution was fast. Beware, you can spend lots of time shooting in the dark on such issues (speaking from expirience: sometimes followed by a load DUH!)


regards

thomas