I have a little function I call AGING() which looks for debits and credits on a single account that are within certain date boundaries, e.g. 0-30 days old, 31-60 days old, etc.
In testing this routine I would call it repeatedly, verifying that the amounts returned added up and that I wasn't missing any items because of boundary issues. I wrote a loop that started with the first account (lowest primary key) and worked upward. On each account I would perform about ten select calls to Aging(); each call performs one SQL select. The average select would gather about 10 records but some would get 100-200.
I noticed that it started out reasonably well but then got slower and slower. I printed out some times and they looked like this:
Accounts Seconds
001-100 ---- 026
101-200 ---- 036
201-300 ---- 048
301-400 ---- 060
401-500 ---- 072
501-600 ---- 087
601-700 ---- 104
701-800 ---- 120
801-900 ---- 134
XX-1000 ---- 154
XX-1100 ---- 171
(stopped here)
Note that the oldest accounts (lowest keys) tend to have more items, so on this basis one would expect the execution times to go down slightly.
If I stop the test and start it up again, the times pick up where they left off:
001-100 ---- 187 seconds
If I CLOSE DATA ALL and restart the test it is sill slow.
BUT, if I exit VFP and start the test again, the speed returns to the fastest level and then degrades again. The times printed out are repeatable within about 5% all the way down the line.
My computer has 64MB (nothing else running). I tried calling SYS(3050) as suggested in a thread last month about things slowing down.
With SYS(3050,1,24000000)
001-100 ---- 028
101-200 ---- 038
201-300 ---- 051
With SYS(3050,1,12000000)
001-100 ---- 027
101-200 ---- 036
201-300 ---- 049
301-400 ---- 061
401-500 ---- 074
I would judge that difference to be non-significant.
There was a thread about this phenomenon on the Compuserve FoxPro forum some 2-3 years ago and there was a concrete solution provided. I wish I could remember what is was.
Peter
Peter Robinson ** Rodes Design ** Virginia