Speed up writes to serial file

[John_S]

Hello,

We have a program that reads our inventory master file and writes some of the data to a tab-delimited file which is later imported into an offline SQL database.  The inventory master file has 2.6 million records in it, and the output serial file is 280mb in size.  It takes the program 45 minutes to over an hour to do all of this (depending on load), and management is asking if I can speed it up.  Since this is essentially a simple read, add data to a string, and write to a serial file, I don't really see any options.

The inventory master file is an EFF file.  I am creating the output serial file using a simple SERIAL command, and using PRINT (1)OUT$ to add data to the file.  I suspect, but don't know, that writing/printing to a serial file is kind of slow.

Any suggestions?

Redhat Linux, PxPlus 14.10 with 150 user license.

[Neal McKinney]

I've used embeddeed I/O to update an external DB - This keeps the external DB update to the minute - and no refresh cycle required.

Neal McKinney
Atlanta, GA

[John_S]

Thanks Neal. In this case the database is external (not on this server and not accessible), and controlled by a "data team". They require us to send a tab-delimited file via ftp.

[Mike King]

I would analyse the timings since to create a 250 MB serial file should not take long.  In fact I ran the following program on my Windows system

0010 SERIAL "abcdef"
0020 OPEN PURGE (1)"abcdef"
0030 LET s=TMR(0)
0040 FOR i=1 TO 2500000
0050 PRINT (1)DIM(100) ! 100 characters
0060 NEXT i
0070 PRINT TMR(0)

And it took about 3.5 seconds to create the 250 MB file. 

Given this its likely either the reading of the EFF file or the computations on the data that is consuming the time.

A few things you can try to speed up processing:

• Try using an OPEN LOAD against the EFF file.  This will eliminate any checking for records currently being updated.  A standard READ always first checks to see if the record is locked by another process which can slow it down.
• If no other process needs the file at that time try an OPEN LOCK which also speeds up processing.  If you are running on Linux and you need concurrent access you could try to make a copy at the OS level first and use OPEN LOCK on the copy.
• If the file does not have to be in any particular record sequence try read the file using the PHYSICAL read next as opposed to reading by key.  This can be done by specifying KNO=* on your read.

[John_S]

Thanks Mike.  I was opening the inventory file using OPEN INPUT, but I have changed it to OPEN LOAD at your suggestion.  I reran the program and it took 20 minutes which is a big improvement. It should be noted, however, that most of the staff has left for the day so there is not much competition for system resources at the moment.

Here is a code snippet of the read, building of the string for the serial file, and the print to the serial file.

7140 READ (M2INV,END=EOF_INVENTORY)IOL=0460; IF MID(A$,30,2)<>DOLOC$ THEN GOTO *SAME
7145 DIM M2A$[1:32]; READ (M2INVA,KEY=A$,DOM=*NEXT)M2A${ALL}
7155 LET OUT$=STP(A$(1,3),1)+QT$+STP(A$(4,26),2)+QT$+A$(30,2)+QT$+STP(B$,1)+QT$
7155:+C$+QT$+D$+QT$+E$+QT$+F$+QT$+G$+QT$+H$+QT$+I$+QT$+J$+QT$+K$+QT$+MID(L$,1,1
7155:)+QT$+MID(L$,2,1)+QT$+M$+QT$
7156 LET OUT$+=FNMDY$(N$)+QT$+FNMDY$(O$)+QT$+P$+QT$+Q$+QT$+R$+QT$+S$+QT$+T$+QT$
7156:+U$+QT$+V$+QT$+STP(X460$,2)+QT$+Z$+QT$+STP(A27$,1)+QT$+STP(A28$,1)+QT$+STP
7156:(A29$,1)+QT$
7157 LET OUT$+=STP(A30$,2)+QT$+STP(PAD(M2A$[8],12),2)+QT$+STP(MID(PAD(M2A$[8],2
7157:4),13,12),2)+QT$+STP(M2A$[2],1)+QT$+STP(M2A$[3],1)+QT$+STP(M2A$[26],1)+QT$
7157:+STP(M2A$[20],1)
7540 PRINT (1)OUT$
7550 GOTO 7140

The files M2INV and M2INVA both have an embedded IO program, but only for pre_write.  I have both IO programs added to memory via ADDR.

[Mike King]

Its not so much that having others on the system accessing the file will slow down the system, its the fact that using a OPEN LOAD or OPEN LOCK eliminates the check for other users.  That can improve throughput quite a bit.

[John_S]

Thanks Mike, that is good to know!