RLA to Embedded SQL Benchmarks

[worksofliam]

This is part one in a series of blog posts which include benchmarks after using RLAConverter to change RLA operations into Embedded SQL. In the next post, I hope to have a larger example.

In this example we will take a look at SETGT and REAPE. Here is a diff off the code before and after the conversion.

ctl-opt dftactgrp(*no);

//dcl-f productsp keyed usropn;
Dcl-Ds PRODUCTSP EXTNAME('PRODUCTSP');
End-Ds;

dcl-s start timestamp;
dcl-s end timestamp;
dcl-s data varchar(50);

start = %timestamp();

//open productsp;
EXEC SQL DECLARE c_PRODUCTSP SCROLL CURSOR FOR
  SELECT * FROM PRODUCTSP;
EXEC SQL OPEN c_PRODUCTSP;

//Setgt (115) productsp;
//Readpe (115) productsp;
//If not %Eof(productsp);
SetGTPRODUCTSP(115);
ReadPEPRODUCTSP(115);
//IF NOT %EOF(PRODUCTSP);
If not (sqlstate = '02000');
  data = %Char(PRID);
EndIf;

dsply data;

//close productsp;
EXEC SQL CLOSE c_PRODUCTSP;

end = %timestamp();
       
Dsply %Char(%Diff(end:start:*mseconds));

Return;

Dcl-Proc SetGTPRODUCTSP;
  Dcl-Pi *N;
    pPRID Like(PRID) Const;
  End-Pi;
  EXEC SQL FETCH AFTER FROM c_PRODUCTSP;
  EXEC SQL FETCH PRIOR FROM c_PRODUCTSP INTO :PRODUCTSP;
  Dow NOT (sqlstate = '02000');
    If (pPRID = PRID);
      EXEC SQL FETCH RELATIVE +1 FROM c_PRODUCTSP;
      Return; //Found the record!
    Endif;
    EXEC SQL FETCH PRIOR FROM c_PRODUCTSP INTO :PRODUCTSP;
  Enddo;
End-Proc;
Dcl-Proc ReadPEPRODUCTSP;
  Dcl-Pi *N;
    pPRID Like(PRID) Const;
  End-Pi;
  EXEC SQL FETCH PRIOR FROM c_PRODUCTSP INTO :PRODUCTSP;
  Dow NOT (sqlstate = '02000');
    If (pPRID = PRID and pPRID = PRID);
      Return; //Found the record!
    Endif;
    EXEC SQL FETCH PRIOR FROM c_PRODUCTSP INTO :PRODUCTSP;
  Enddo;
End-Proc;

Results

I called each program three times to see if the programs would perform better the more times they were ran. While not a huge improvement, it's neat that it hasn't made it worse.

RLA

> call readpe1 
  DSPLY  115   
  DSPLY  132000
> call readpe1 
  DSPLY  115   
  DSPLY  120000
> call readpe1 
  DSPLY  115   
  DSPLY  119000

Embedded SQL

> call readpe1  
  DSPLY  115    
  DSPLY  120000 
> call readpe1  
  DSPLY  115    
  DSPLY  119000
> call readpe1 
  DSPLY  115   
  DSPLY  117000