Thank you to everyone who responded.
Is the proposed import doing any updates of the parent records or just adding\updating child records?
The import will not create parent records. It also won't edit existing records, only create new child records in one particular table.
If the expected parent record is not found, all of the to-be-created child records for that parent will be skipped.
I think most of the time will go to very extensive validation to get a list (cursor or array) of records that will actually be inserted. As noted, the validation will have to include queries to find the parent records. I will also have to do queries to see if the child records have already been added (I can envision someone trying to import the same sheet more than once). I don't think those queries should pose much of a problem since they are read, not write, operations. Those would all be done prior to beginning the insertion process.
I'm going to require that the spreadsheet be saved in csv format to eliminate vagaries associated with xlsx and xlsx converted to xls. I'm told that the spreadsheet wizards around here are pretty familiar with that requirement.
So I feel pretty confident that this isn't going to disrupt anything seriously.
Thanks again.
Ken www.stic-cil.org
Normally (i.e. in vanilla VFP) unless the logged-in users have exclusively opened or otherwise locked the parent records (which you say they won't) then I would be in broad terms:
- Pre-validating the import data to report on exceptions and arrive at a
clean import set of records.
- Upserting the child records wrapped in a transaction.
- Repeat if applicable for parent records.
You say your application doesn't explicitly lock any tables (rlock(), flock() or automatic locks via BEGIN TRANSACTION) and if the OS does indeed do any sort of ultra-brief locking on file updates it's never caused me a problem.
So I would say the worst that will happen is that other users might see a 'record not available - please wait' message if they try and save a parentr record when you're still inside a transaction on whatever machine the import is running on. But assuming there aren't millions of records and you keep the transaction as quick as possible I doubt they'd ever even see that.
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
[excessive quoting removed by server]