Kbase P185179: How can I improve Oracle Dataserver performance?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
01/04/2011 |
|
Status: Unverified
GOAL:
How can I improve Oracle Dataserver performance?
GOAL:
Can I run a stored procedure in Oracle Dataserver?
GOAL:
How to set up a stored procedure in Oracle Dataserver.
GOAL:
How does the Oracle DataServer has the capability of batching up inserts to the server to reduce round trips while providing ABL control over record formation
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
FIX:
The DataServer products are usually more disadvantaged when comparing transactional processing. There may be subtle issues affecting the comparison as others have suggested but a fairer comparison, and my recommendation, would be to pack up the SQLPlus routine into a stored procedure and just call it from the ABL app. directly against the server with RUN STORED-PROC.
Another alternative ... the Oracle DataServer has the capability of batching up inserts to the server to reduce round trips while providing ABL control over record formation.. Something like this would batch inserts into 100 record increments:
run stored-proc send-sql-statement h1 = proc-handle("--Bulk-insert Start 100").
close stored-proc send-sql-statement r1 = proc-status where proc-handle = h1.
DEF VAR cnt AS INT NO-UNDO.
DEF VAR stime AS INT NO-UNDO.
stime = TIME.
REPEAT cnt = 1 TO 100000:
CREATE tr_hist.
ASSIGN prrowid = string(cnt).
IF cnt MOD 10000 = 0 THEN DO:
PAUSE 0.
DISP cnt STRING(TIME - stime,"HH:MM:SS").
Stime = time.
END.
END.
run stored-proc send-sql-statement h2 = proc-handle ("--Bulk-insert End").
close stored-proc send-sql-statement r2 = proc-status where proc-handle = h2.