PxPlus User Forum

Main Board => Discussions => Language => Topic started by: John_S on November 08, 2019, 02:39:58 PM

Title: Speed up writes to serial file
Post by: John_S on November 08, 2019, 02:39:58 PM
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.
Title: Re: Speed up writes to serial file
Post by: Neal McKinney on November 08, 2019, 03:12:24 PM
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
Title: Re: Speed up writes to serial file
Post by: John_S on November 08, 2019, 03:16:05 PM
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.
Title: Re: Speed up writes to serial file
Post by: Mike King on November 08, 2019, 04:54:18 PM
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.  ;D

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:


Title: Re: Speed up writes to serial file
Post by: John_S on November 08, 2019, 05:35:34 PM
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.

Title: Re: Speed up writes to serial file
Post by: Mike King on November 08, 2019, 05:55:21 PM
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.
Title: Re: Speed up writes to serial file
Post by: John_S on November 08, 2019, 06:00:15 PM
Thanks Mike, that is good to know!