NFILE: advantages and disadvantages

I am sure that I have written about NFILE at some time but I can't find the reference and so I'll start from the beginning. [Edit: I found the entry which now has the NFILE label]

Let's say that one has a procedure that accepts a list of customers as its input - let's even say that it's the HTML document that I worked on yesterday. When one enters a list of customers, the linked customers table will include only those customers, obviously. When one writes * as the input, all the customers from the unlinked customer table have to be copied into the linked customers table; this takes time! 

Enter NFILE: this was introduced in version 17 IIRC. The documentation says: Select NFILE if the linked file comprises a group of records and you want the link table to remain empty when the user enters * or leaves the field empty. How does one use this? By writing code as follows

LINK CUSTOMERS TO :$.CST;  /* NFILE */
ERRMSG 1 WHERE :RETVAL <= 0;
GOTO 1 FROM CUSTOMERS WHERE CUST > 0;
UNLINK CUSTOMERS;
LABEL 1;

This saves time in copying records from the unlinked table to the linked table. Tables that benefit from this are PART, DOCUMENTS, INVOICES and CUSTOMERS. Maybe a few others.

Now back to the HTML document that I described yesterday. I realised that I could filter unwanted customers using a cursor and a series of expressions such as

GOTO 999 WHERE NOT EXISTS (SELECT 1 FROM BRANCHES
WHERE BRANCH > 0);
LINK CUSTOMERS TO :$.CST;
LINK BRANCHES TO :$.BRN;
DECLARE C15 CURSOR FOR
SELECT CUST FROM CUSTOMERS
WHERE CUST > 0;
OPEN C15;
GOTO 999 WHERE :RETVAL <= 0;
LABEL 100;
FETCH C15 INTO :CST;
GOTO 800 WHERE :RETVAL <= 0;
GOTO 200 WHERE :$.A3 <> 'Y'; /* No need to check price quotes if not wanted */
:HASDATA = 0;
SELECT 1 INTO :HASDATA
FROM CPROF, CPROFA, BRANCHES
WHERE CPROF.PROF = CPROFA.PROF
AND CPROFA.BRANCH = BRANCHES.BRANCH
AND CPROF.CUST = :CST
AND CPROF.PDATE >= :$.DAT;
LOOP 100 WHERE :HASDATA = 1;
....
LABEL 700;
/* If we are here, then the customer has failed all the tests
so delete it from the linked table */
DELETE FROM CUSTOMERS WHERE CUST = :CST;
LOOP 100;
LABEL 800;
...

This code is repeated, once for each type of record - sales opportunities, price quotes, orders and projects. The first two lines check whether any branches were passed as a parameter: there's no point in checking anything if there are no branches. I added all the necessary code, ran the program using one specific branch without designating customers: the procedure took a few minutes to sort itself out then showed the output: as designed, only customers with data from the chosen branch were shown.

I wondered how I could improve the procedure's speed - the obvious step would be to define CUSTOMERS as type NFILE instead of FILE. There would be no need to copy customers to the linked table which would save time. So I added this change, ran the program ... and then realised with a shudder that the DELETION WAS NOT FROM THE LINKED TABLE BUT FROM THE REAL TABLE!!! The first code snippet that I showed unlinks CUSTOMERS; thus the DELETE statement does not work on the linked table but rather the real table.

I reached for the power switch (figuratively) as soon as possible but it was too late. A few minutes later, someone called to ask whether customers had disappeared. My code has managed to delete about 16,000 customers out of 24,000 in a few minutes. Straightaway I phoned the network manager who took Priority off line, and then we turned to our database administrator who started restoring the damaged table.

Bottom line: be wary of NFILE! Normally it can help but sometimes it is dangerous.