Whenever we are designing a new modification, we not only need to design to address the defined needs, but also to provide a solution that processes efficiently. An inefficient solution carries unnecessary ongoing costs. Many of the things that we can do to design an efficient solution are relatively simple. Some of the areas to address include:
The slowest thing in any computer system is the disk I/O. Disk I/O almost always takes more time than any other system processing activity. When we begin concentrating our design efforts on efficiency, focus first on minimizing disk I/O.
The most critical elements are the design of the keys, the number of keys, the design of the SIFT fields, the number of SIFT fields, the design of the filters, and the frequency of accesses of data (especially FlowFields). If our system is going to have five or ten users processing a few thousand order lines per day, and if our system is not heavily modified, we probably won't have much trouble. But if we are installing a system with one or more of the following attributes, any of which can have a significant effect on the amount of disk I/O, we will need to be very careful with our design and implementation:
- Large concurrent user count
- High transaction volumes, especially in data being Posted
- Large stored data volumes, especially that resulting from customizations or setup option choices
- Significant modifications
- Very complex business rules
One important aspect of the design of an integrated system such as NAV, that is often overlooked until it rears its ugly head after the system goes into production, is the issue of "Locking". Locking occurs when one process has control of a data element, record, or group of records (in other words, part or all of a table) for the purpose of updating the data within the range of the locked data and, at the same time, another process requests the use of some portion of that data but finds it to be locked by the first process.
In the worst case, which is a "deadlock", there is a design flaw; each process has data locked that the other process needs and neither process can proceed. As developers or implementers, one of our jobs is to minimize the locking problems and eliminate any deadlocks.
Locking interference between processes in an asynchronous processing environment is inevitable. There are always going to be points in the system where one process instance locks out another one momentarily. The secret to success is to minimize the frequency of these and the time length of each lock. Locking becomes a problem when the locks are held too long and the other locked-out processes are unreasonably delayed.
One might ask What is an unreasonable delay? For most of the part, a delay becomes unreasonable when the users can tell that it's happening. If the users see stopped processes or experience counter-intuitive processing time lengths (that is, a process that seems like it should take 10 seconds actually takes two minutes), then the delays will seem unreasonable. Of course, the ultimate unreasonable delay is the one that does not allow the required work to get done in the available time.
The obvious question is how to avoid locking problems. The best solution is to simply speed up the processing. This will reduce the number of lock conflicts that arise. Important recommendations for speed include the following:
- Restrict the number of active keys, especially on the SQL Server
- Restrict the number of active SIFT fields, eliminating them when feasible
- Carefully review the keys, not necessarily using the "factory default" options
- Make sure that all the disk access code is SQL Server optimized
Following are some additional steps that can be taken to minimize locking problems:
- Always process tables in the same relative order
- When a common set of tables will be accessed and updated, lock a "standard" master table first (for example, when working on Orders, always lock the Order Header table first)
- Shift long-running processes to off-hours or even separate databases
In special cases, the following techniques can be used (if done very, very carefully):
- Process data in small quantities (for example, process 10 records or one order, then
COMMIT, which releases the lock). This approach should be very cautiously. - In long process loops, process a
SLEEPcommand in combination with an appropriateCOMMITcommand to allow other processes to gain control (see the preceding caution).
Refer to the documentation with the system distribution and in the NAV forums.