Jan Doggen
New Member
Hi everyone,
we have two program parts that takes too long.
Is anyone experienced enough to point out where the bottlenecks are in the
code below (and what we could do about it)
(two notes:
- this stuff is not compiled, it is generated run-time because there is no
other way to disable the triggers dynamically
- we have no influence over the database settings - it's used by an existing
program and we can't mess with that)
=== Part 1 ===
=== Part 2 ===
===================
Thanks in advance,
Jan
we have two program parts that takes too long.
Is anyone experienced enough to point out where the bottlenecks are in the
code below (and what we could do about it)
(two notes:
- this stuff is not compiled, it is generated run-time because there is no
other way to disable the triggers dynamically
- we have no influence over the database settings - it's used by an existing
program and we can't mess with that)
=== Part 1 ===
Code:
DEFINE INPUT PARAMETER cTabelnaam AS CHARACTER NO-UNDO.
DEFINE SHARED VARIABLE M AS INTEGER EXTENT 500 NO-UNDO.
DEFINE VARIABLE table-name AS HANDLE NO-UNDO.
DEFINE VARIABLE dyn-query AS HANDLE NO-UNDO.
DEFINE VARIABLE hVeld AS HANDLE NO-UNDO.
DEFINE VARIABLE iTeller AS INTEGER NO-UNDO.
DEFINE VARIABLE iNumVeld AS INTEGER NO-UNDO.
DEFINE VARIABLE iVeldLen AS INTEGER EXTENT 500 NO-UNDO.
DEFINE VARIABLE iMaxLeng AS INTEGER NO-UNDO.
DEFINE VARIABLE lengte AS INTEGER INITIAL 0 NO-UNDO.
DEFINE VARIABLE i AS INTEGER INITIAL 0 NO-UNDO.
DEFINE VARIABLE start AS INTEGER INITIAL 1 NO-UNDO.
DEFINE VARIABLE iTbNumFields AS INTEGER INITIAL 1 NO-UNDO.
DEFINE VARIABLE iExtentField AS INTEGER INITIAL 1 NO-UNDO.
DEFINE VARIABLE iLengteField AS INTEGER INITIAL 1 NO-UNDO.
DEFINE VARIABLE LFteller AS INTEGER INITIAL 0 NO-UNDO.
DISABLE TRIGGERS FOR LOAD OF tb031_werknemer.
DISABLE TRIGGERS FOR DUMP OF tb031_werknemer.
CREATE BUFFER table-name FOR TABLE cTabelnaam.
CREATE QUERY dyn-query.
dyn-query:SET-BUFFERS(table-name).
dyn-query:QUERY-PREPARE('FOR EACH ' + cTabelnaam).
dyn-query:QUERY-OPEN.
dyn-query:GET-FIRST.
iTbNumFields = table-name:NUM-FIELDS.
REPEAT WHILE NOT dyn-query:QUERY-OFF-END:
DO iNumVeld = 1 TO iTbNumFields:
hVeld = table-name:BUFFER-FIELD(iNumVeld).
IF hVeld:DATA-TYPE = 'CHARACTER' THEN
DO:
IF hVeld:EXTENT > 0 THEN
DO:
iExtentField = hVeld:EXTENT.
iMaxLeng = 0.
DO iTeller = 1 TO iExtentField:
IF hVeld:BUFFER-VALUE[iTeller] = '' THEN
iMaxLeng = iMaxLeng + 1.
ELSE
iMaxLeng = iMaxLeng + LENGTH(hVeld:BUFFER-VALUE[iTeller]) + 1.
IF iMaxLeng > M[iNumVeld] THEN
M[iNumVeld] = iMaxLeng.
END.
END.
ELSE DO:
LFteller = 0.
start = 1.
--------- this part already replaced bij Num-entries -------
iLengteField = length(hVeld:BUFFER-VALUE).
DO lengte = 1 TO iLengteField:
i = index((hVeld:BUFFER-VALUE), chr(10), start).
DO while i > 0:
LFteller = LFteller + 1.
start = i + 1.
i = index((hVeld:BUFFER-VALUE), chr(10), start).
END.
--------- this part already replaced bij Num-entries -------
END.
iMaxLeng = LENGTH(hVeld:BUFFER-VALUE) + LFteller.
IF iMaxLeng > M[iNumVeld] THEN
M[iNumVeld] = iMaxLeng.
END.
END.
ELSE
M[iNumVeld] = 0.
END.
dyn-query:GET-NEXT.
END.
DELETE OBJECT table-name.
DELETE OBJECT dyn-query.=== Part 2 ===
Code:
DEFINE INPUT PARAMETER TabelNaam AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER velden AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER bestand AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER cIsCoded AS LOGICAL NO-UNDO.
DEFINE INPUT PARAMETER cDbNaam AS CHARACTER NO-UNDO.
DEFINE VARIABLE table-name AS HANDLE NO-UNDO.
DEFINE VARIABLE dyn-query AS HANDLE NO-UNDO.
DEFINE VARIABLE hVeld AS HANDLE NO-UNDO.
DEFINE VARIABLE iTeller AS INTEGER NO-UNDO.
DEFINE VARIABLE iArrayTeller AS INTEGER NO-UNDO.
DEFINE VARIABLE cResponseLine AS CHARACTER NO-UNDO.
DEFINE VARIABLE cFoutMessage AS CHARACTER NO-UNDO.
DEFINE VARIABLE iNumFields AS INTEGER NO-UNDO.
DEFINE VARIABLE iFields AS INTEGER NO-UNDO.
DEFINE VARIABLE iExtentField AS INTEGER NO-UNDO.
DEFINE VARIABLE iRecTeller AS INTEGER NO-UNDO.
DEFINE VARIABLE hCurrent AS HANDLE NO-UNDO.
DEFINE VARIABLE hNextProc AS HANDLE NO-UNDO.
DEFINE VARIABLE cOk AS CHARACTER INITIAL "OK" NO-UNDO.
disable triggers for dump of tb031_werknemer.
disable triggers for load of tb031_werknemer.
IF CAN-FIND (dictdb._file WHERE NOT _hidden AND _file._file-name = TabelNaam
NO-LOCK) THEN
DO:
IF velden <> '' THEN
DO:
create buffer table-name for table TabelNaam.
create query dyn-query.
dyn-query:set-buffers(table-name).
dyn-query:query-prepare('FOR EACH ' + TabelNaam + ' NO-LOCK').
dyn-query:query-open.
OUTPUT TO VALUE(bestand) APPEND.
dyn-query:GET-FIRST.
IF cIsCoded THEN
RUN Encode2.p (INPUT-OUTPUT cOk, 'Error in request number - value
%s is missing').
PUT UNFORMATTED cOk SKIP.
REPEAT WHILE NOT dyn-query:QUERY-OFF-END:
iRecTeller = iRecTeller + 1.
IF (DEC(iRecTeller) / (INT(DEC(iRecTeller) / 100))) = 100 THEN
DO:
hCurrent = SESSION:FIRST-PROCEDURE.
DO WHILE VALID-HANDLE(hCurrent):
hNextProc = hCurrent:NEXT-SIBLING.
IF hCurrent:PRIVATE-DATA = 'pstart' THEN
RUN pShowStatus IN hCurrent (INPUT iRecTeller).
hCurrent = hNextProc.
END.
END.
PROCESS EVENTS.
IF Velden = '*' THEN
DO:
iNumFields = table-name:NUM-FIELDS.
DO iTeller = 1 to iNumFields:
hVeld = table-name:BUFFER-FIELD(iTeller).
IF hVeld:EXTENT > 0 THEN
DO:
iExtentField = hVeld:EXTENT.
DO iArrayTeller = 1 TO iExtentField:
cResponseLine = cResponseLine +
hVeld:BUFFER-VALUE[iArrayTeller] + ';'.
END.
cResponseLine = REPLACE(cResponseLine,'","', chr(255) +
chr(255) + chr(255)).
PUT UNFORMATTED '"' cResponseLine '"' ','.
cResponseLine = "".
END.
ELSE DO:
cResponseLine = hVeld:BUFFER-VALUE.
cResponseLine = REPLACE(cResponseLine,'","', chr(255) +
chr(255) + chr(255)).
PUT UNFORMATTED '"' cResponseLine '"' ','.
END.
END.
END.
ELSE DO:
iFields = NUM-ENTRIES(Velden).
DO iTeller = 1 TO iFields:
hVeld = table-name:BUFFER-FIELD(ENTRY(iTeller,Velden)).
IF hVeld:EXTENT > 0 THEN
DO:
iExtentField = hVeld:EXTENT.
DO iArrayTeller = 1 TO iExtentField:
cResponseLine = cResponseLine +
hVeld:BUFFER-VALUE[iArrayTeller] + ';'.
END.
cResponseLine = REPLACE(cResponseLine,'","', chr(255) +
chr(255) + chr(255)).
PUT UNFORMATTED '"' cResponseLine '"' ','.
cResponseLine = "".
END.
ELSE DO:
cResponseLine = hVeld:BUFFER-VALUE.
cResponseLine = REPLACE(cResponseLine,'","', chr(255) +
chr(255) + chr(255)).
PUT UNFORMATTED '"' cResponseLine '"' ','.
END.
cResponseLine = "".
END.
END.
PUT UNFORMATTED SKIP.
dyn-query:GET-NEXT.
PROCESS EVENTS.
END.
hCurrent = SESSION:FIRST-PROCEDURE.
DO WHILE VALID-HANDLE(hCurrent):
hNextProc = hCurrent:NEXT-SIBLING.
IF hCurrent:PRIVATE-DATA = 'pstart' THEN
RUN pShowStatus IN hCurrent (INPUT iRecTeller).
hCurrent = hNextProc.
END.
PROCESS EVENTS.
OUTPUT CLOSE.
DELETE OBJECT table-name.
DELETE OBJECT dyn-query.
END.
ELSE DO:
OUTPUT TO VALUE(bestand) append.
IF cIsCoded then
DO:
cFoutMessage = 'ERROR: Velden niet opgegeven' + velden.
RUN Encode2.p (INPUT-OUTPUT cFoutMessage, 'Error in request number -
value %s is missing').
PUT UNFORMATTED cFoutMessage skip.
END.
ELSE
PUT UNFORMATTED 'ERROR: Velden niet opgegeven ' velden skip.
OUTPUT CLOSE.
END.
END.
ELSE DO:
OUTPUT TO VALUE(bestand) append.
IF cIsCoded then
DO:
cFoutMessage = 'ERROR: Onbekende tabel ' + TabelNaam.
RUN Encode2.p (INPUT-OUTPUT cFoutMessage, 'Error in request number -
value %s is missing').
PUT UNFORMATTED cFoutMessage skip.
END.
ELSE
PUT UNFORMATTED 'ERROR: Onbekende tabel ' TabelNaam skip.
OUTPUT CLOSE.
END.===================
Thanks in advance,
Jan
