We are often asked if there is a limit to the number of records that can be updated or created per batch run with Data Loaders. Typically, it is a several thousand, but the exact number will vary from installation to installation. The maximum number of records that can be uploaded or downloaded between Excel and QAD using Data Loaders is based on two factors.
First of all, your QAD server settings, particularly the -L progress server start up parameter, determine the number of locks. The larger the number, the more records can be uploaded per batch. Increasing the -L value will allow the user to upload more records, but it will also consume more of your QAD server’s resources. This can be a good solution for some. For others it will not be viable. Please coordinate with your DBA.
Secondly, the number of locks does not necessarily correspond to the number of records in the Excel spreadsheet. In most applications, one record that a user sees in Excel may update many records in the QAD database. For example, the 1.10.1.1 QAD standard program updates many records in a quite a few different tables for one price list record that the user sees in Excel.
What happens when Data Loaders hits the limit of the number of records that can be loaded to QAD? It is a completely safe process and harmless to the QAD application. When the limit is reached, the QAD server disconnects the Excel user and the progress session is terminated. The user will receive a “time out” message and none of the records in the batch will be updated to QAD.
How can you determine, roughly, the maximum number of records that can be uploaded? By trial and error. Here is a real life example: you are able to update 10,000 records from Excel to QAD, but when trying to update 12,000 records, the program delivers a “Time Out” message. When you check to see if records were updated, you find that they have not been. This will mean that somewhere between 10,000 and 12,000 records is the maximum your QAD server can handle per batch run.
Here are some things you can do:
Exclude records from the batch which do not require an update.
You can use “Download” filters to bring less records to the spreadsheet from QAD and only process those you need. Or, after downloading records to Excel, you use Excel filtering or sorting capabilities to select only the records to be updated. Delete the rest of the records from the spreadsheet and proceed.
Workaround, using “chunks”
If you do exceed the update record limit in your Excel file, you can insert a blank row in the Excel file at the point where you’ve reached the record limit. For example, you have 30,000 records to update, but your limit is 10,000. Insert a blank row after you 10,000th record and click upload. Excel Loaders will only upload the first 10,000 records to QAD. The upload session will stop at blank row. Once the upload is complete, delete first 10,000 records from the Excel spreadsheet and insert another blank row to upload the next 10,000 records. Continue this process until all records have been successfully loaded.
General recommendation
As general recommendation, we suggest that you run massive updates to QAD in hours when fewer users are logged in to QAD and less batch processes are running. This will allow more server resources to be allocated to excel updates.
