>>>I'm testing a process for someone which takes days to run on a 466 celeron with 128 ram. Plenty of diskspace, VFP 6.0/Win98. This process at some point in time will error out with a "File is in use" on a SQL-SELECT statement. The error is not consistent in where it occurs. That error msg supposedly indicates that this process is trying to execute a USE, RENAME or DELETE on a file already opened. I guess a SELECT statement issues an implicit USE, but I see NO reason why this error message should come up. Examination in the datasession window show only a few tables/cursors open. If the process is restarted, which can be started with the record that originally caused the error, it goes through the process just fine but of course, everything has been cleared from the crash. This implies that VFP is having some sort of trouble in memory (or elsewhere) in tracking the many, many cursors, tables that are created/opened and then closed. Doing a CLOSE TABLE ALL is out as the process
>>>SCANS thru a table and processes each record individually through a vast series of other operations. Any ideas on what is happening here? It seems that VFP has run out of some resource for tracking the opening and closing of cursors/tables and then generating the error at some point.
>>>
>>>Thanks,
>>>Steve
>>
>>

---

Steve,
>>There are many reasons that you can get "file is in use". If I get it with an SQL statement I would suspect if cursor I'm trying to select into is already used as an alias somewhere.
>>My point is really something else. Your computer seems to be a faster one than mine (AMD K6-2 333, 128 Mb). I was never patient enough to wait something to finish in a couple of hours. OTOH I never worked with many tables each with several millions of records so might be processes that take so long. Anyway seeing that "plenty of space" and assuming your harddisk shouldn't be bigger than 20Gb (is it ?:) I find "days" very long for Fox. I suggest you to revise code first. I will demonstrate what I mean with a very simple code :
>>

* Create a test cursor
>>create cursor myTestCursor (dummy c(10))
>>for ix = 1 to 10
>> insert into myTestCursor values ( "Dummy"+ltrim(str(ix)) )
>>endfor
>>
>>* Test by indiviual selects
>>lnStart = seconds()
>>for ix = 1 to 1000
>>  select * from myTestCursor into cursor ("My"+padl(ix,4,"0"))
>>endfor
>>? seconds() - start
>>
>>* Test by indiviual selects but closing the ones that are not needed anymore
>>lnStart = seconds()
>>for ix = 1 to 1000
>>  select * from myTestCursor into cursor ("My"+padl(ix,4,"0"))
>>  use in ("My"+padl(ix,4,"0"))
>>endfor
>>? seconds() - start

Of course this only demonstrates how could temp dir file count could impact performance. A simple "use mytable in 0 again .." is not much different on performance and get the system on its knees. Revising the code just for performance might cease all of your problems (I hope).
>>Cetin
>
>Cetin,
>
>Thanks for taking a stab at answering the question. Actually, there is only 7,000 records...but each one goes through a very, very lengthy process of calculations and updates are made to other tables in the database based on results. It just simply takes around 90 - 120 secs for each record to do what it needs to do. Now that's fairly quick considering what it has to go through. If you do the math at 90 secs....it comes to about 7.29 days.
>
>So, back to the original question. BTW, in the real world, it wouldn't take this long because once some of the calcs are done, it doesn't have to repeat them on a regular basis so the time is much faster. We figure 15 - 20 hours tops on a "normal" run. For this test however, everything that gets calculated is either blanked out or set to zero forcing a complete (and thorough) test of all processes. That's why it is taking this long.
>
>Now, with that out of the way.....I need to resolve the "File is in use" error message. SQL-SELECT, being a read process, should never have a problem in gaining access to a table to read. Exclusive is NOT used here at all.
>
>Steve

---

Steve,
I insist on a revise. This is like something what we discuss here at our institute. We have models to test with heavy math. Scientist were originally using Fortran to do it. They were in thought of Fortran would do it fastest (and really it might be). The original code was doing one sequence in about 12 hours. Pascal version did the same in less than 40 mins. Here I don't mean that it's sole power of Pascal. It might be code logic or Pascal (and I propose the idea to do it with Fox:). Original math portion really execute in a few secs but for a model it's repeated n times intervals. Doing math for a particular record might still take 90-120 secs. But that doesn't necessarily mean total would take 90*7000 secs under every circumstances. Anyway code is yours, if you say there is no way to speed it up then what could I say :)
Even with SQL I faced situations that SQL tries to get exclusive use whatever I do but it was with ODBC. I don't think you're using ODBC.
Well, to find it out I would do this :
-Get a replication of directory to test
-Replace lengthy math routine with just a rand() return value (or something more appropriate that would return values for different actions like null, <0, 0 and >0)
-Cut down the original reccount() to a very small value
This could represent a debuggable model (error should pop and retested in mins rather than days). Hope works for you and you can narrow down the error.
BTW SQL select into table rather than cursor would be main concern IMHO (on system someone else blocking the table ?).
Cetin