My first thought when I read this was that I would create temporary cursors (CREATE CURSOR) to import the data initially and then add the new records to the production tables from the temporary cursors. I love using temporary cursors for importing data!
This would give you plenty of time to perform error checking during the import process and then it will allow you to minimize the time it takes to post the imported data to the production tables. You could even control the post process to push one parent-child data set at a time to the production tables as opposed to posting all parents first and then posting all the child records. Paul H. Tarver Tarver Program Consultants, Inc. Email: [email protected] -----Original Message----- From: ProfoxTech [mailto:[email protected]] On Behalf Of Ken Dibble Sent: Thursday, June 22, 2017 9:49 AM To: [email protected] Subject: Bulk Imports While Multi-User System is in Use Hi folks, Having never attempted this before, I am seeking any guidance you can offer. I'm using VFP tables across a LAN, with VFP 9 SP 1. The tables are on a virtualized Windows 2012 file server that has gargantuan memory and HDD resources available. The workstations are Windows 7 Ultimate 32-bit with processors in the 3.5 GHz range and 4 GB of RAM. My framework does not use the RAD features of VFP; no data environments, no control sources, no buffering (I actually wrote my own "buffering"/conflict resolution system), no auto-incrementing columns. The only time I manually lock anything is when my integer surrogate key-creation program is generating a new key--and I only lock the table that stores the last-used key value. I do use a database container so that the default value methods of the PK fields of my tables can call the key generation program. When users create new records, I use APPEND, not INSERT. The system was deliberately designed to divorce database access from business rules and GUI (because initially, long ago, I thought I might actually embark on writing my own database server). I understand that there is brief automatic locking, however, whenever VFP writes to a table (or, rather, when the server OS, in its infinite wisdom, deigns to carry out VFP's request to write to the table). I've been given the task of importing data from a spreadsheet into my multi-user system. I have not done any testing on this but it is possible that the import process, run every week or every two weeks, will create thousands of records that will be "children" of a few hundred "parent" records. The system is in near constant use by somewhere around a half-dozen people, and much larger numbers use it occasionally. Some of the half-dozen intensive users are quite likely to be accessing the parent records at the time the bulk import process is creating the child records. I am concerned about creating massive access conflicts leading to race conditions, freezing of the system, and/or data corruption. Should I just block everybody from saving any data while the import process runs? (I don't know how long it will take, but I do envision a lot of error-checking and logging on each row of the spreadsheet to prevent bad data from getting into the system.) Or are there simple things I can do to prevent issues ("simple" means, no major changes to the framework or the database)? Or is this something I don't really need to worry about? Thanks very much for any suggestions. Ken Dibble www.stic-cil.org [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
