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