Oninit® SQLMorph — Before and After
The same 4GL source is run two ways:
- 4GL: compiled with IBM
c4gl7.51 and run as a stand-alone program. - UDR: converted with
sqlmorph -s informix-4gl -t informix-native, built against the engine's DataBlade headers, registered on an Informix 15.0 engine and called withEXECUTE FUNCTION.
Before: the 4GL source
FUNCTION late_fee(due, paid, per_day)
DEFINE due, paid DATE
DEFINE per_day DECIMAL(8,2)
DEFINE days INTEGER
LET days = paid - due
IF days <= 0 THEN
RETURN 0
END IF
RETURN days * per_day
END FUNCTION
FUNCTION ex_total(cust)
DEFINE cust INTEGER
DEFINE amt, tot DECIMAL(12,2)
LET tot = 0
DECLARE c1 CURSOR FOR SELECT amount FROM ex_orders WHERE cust_id = cust
FOREACH c1 INTO amt
LET tot = tot + amt
END FOREACH
RETURN tot
END FUNCTION
FUNCTION ex_frac(a)
DEFINE a INTERVAL FRACTION TO FRACTION(3)
DEFINE b, r INTERVAL FRACTION TO FRACTION(3)
LET b = INTERVAL(.5) FRACTION TO FRACTION(3)
LET r = a + b
RETURN r
END FUNCTION
FUNCTION ex_sum(n)
DEFINE n, i, t INTEGER
LET t = 0
FOR i = 1 TO n
LET t = t + (i MOD 7) * 2
END FOR
RETURN t
END FUNCTION
FUNCTION ex_primes(reps)
DEFINE reps, r, i, j, cnt INTEGER
DEFINE comp ARRAY[30000] OF SMALLINT
LET cnt = 0
FOR r = 1 TO reps
FOR i = 1 TO 30000
LET comp[i] = 0
END FOR
LET cnt = 0
FOR i = 2 TO 30000
IF comp[i] = 0 THEN
LET cnt = cnt + 1
FOR j = i * 2 TO 30000 STEP i
LET comp[j] = 1
END FOR
END IF
END FOR
END FOR
RETURN cnt
END FUNCTION
FUNCTION ex_interest(n)
DEFINE n, i INTEGER
DEFINE bal DECIMAL(16,6)
LET bal = 100
FOR i = 1 TO n
LET bal = bal * 1.00001 + 0.01
END FOR
RETURN bal
END FUNCTION
FUNCTION ex_collatz(n)
DEFINE n, i, x, steps, best INTEGER
LET best = 0
FOR i = 1 TO n
LET x = i
LET steps = 0
WHILE x <> 1
IF x MOD 2 = 0 THEN
LET x = x / 2
ELSE
LET x = 3 * x + 1
END IF
LET steps = steps + 1
END WHILE
IF steps > best THEN
LET best = steps
END IF
END FOR
RETURN best
END FUNCTION
FUNCTION ex_chars(n)
DEFINE n, i, k, cnt INTEGER
DEFINE s CHAR(40)
LET s = "the quick brown fox jumps over a lazy dog"
LET cnt = 0
FOR i = 1 TO n
LET k = i MOD 40 + 1
IF s[k, k] = "o" OR s[k, k] = "a" THEN
LET cnt = cnt + 1
END IF
END FOR
RETURN cnt
END FUNCTION
After: the install SQL
One script per function. The module file name carries a hash of the generated code, so a new version never overwrites a module the engine may still have loaded. Shown for the first three functions.
DROP FUNCTION IF EXISTS late_fee; CREATE FUNCTION late_fee(due DATE, paid DATE, per_day DECIMAL(8,2)) RETURNING DECIMAL(32) WITH (HANDLESNULLS) EXTERNAL NAME '$INFORMIXDIR/extend/sqlmorph/late_fee_e1ecb63f22fe.so(fgl_late_fee)' LANGUAGE C;
DROP FUNCTION IF EXISTS ex_total; CREATE FUNCTION ex_total(cust INTEGER) RETURNING DECIMAL(12,2) WITH (HANDLESNULLS) EXTERNAL NAME '$INFORMIXDIR/extend/sqlmorph/ex_total_493330676da9.so(fgl_ex_total)' LANGUAGE C;
DROP FUNCTION IF EXISTS ex_frac; CREATE FUNCTION ex_frac(a INTERVAL FRACTION TO FRACTION(3)) RETURNING INTERVAL FRACTION TO FRACTION(3) WITH (HANDLESNULLS) EXTERNAL NAME '$INFORMIXDIR/extend/sqlmorph/ex_frac_caae86e601de.so(fgl_ex_frac)' LANGUAGE C;
After: the generated C for late_fee
The function body only; the other functions have the same shape. The arithmetic goes through the SQLMorph run-time, so DATE, DECIMAL, NULL and INTERVAL behave as they do in 4GL.
mi_decimal *
fgl_late_fee(mi_date p_due, mi_date p_paid, mi_decimal *p_per_day, MI_FPARAM *fparam)
{
fglrtmi_t _mi;
fglrt_ctx_t *ctx = fglrtmi_enter(&_mi, fparam, FGL_NSLOTS);
fglrt_val_t _t[2], _fe[1], _fs[1], _cs[1];
int _c = 0;
(void)_c;
fglrt_val_t v_due;
fglrt_val_t v_paid;
fglrt_val_t v_per_day;
fglrt_val_t v_days;
for (int _i = 0; _i < 2; _i++) _t[_i] = fglrt_null(FGLRT_T_INTEGER);
for (int _i = 0; _i < 1; _i++) { _fe[_i] = fglrt_null(FGLRT_T_INTEGER); _fs[_i] = _fe[_i]; }
for (int _i = 0; _i < 1; _i++) _cs[_i] = fglrt_null(FGLRT_T_INTEGER);
v_due = fglrt_default(ctx, FGLRT_T_DATE, 0);
v_paid = fglrt_default(ctx, FGLRT_T_DATE, 0);
v_per_day = fglrt_default(ctx, FGLRT_T_DECIMAL, 8);
v_days = fglrt_default(ctx, FGLRT_T_INTEGER, 0);
{ fglrt_val_t _in = fglrtmi_in_date(fparam, 0, p_due);
fglrt_free(ctx, &v_due);
v_due = fglrt_assign(ctx, FGLRT_T_DATE, 0, 0, &_in);
fglrt_free(ctx, &_in); }
{ fglrt_val_t _in = fglrtmi_in_date(fparam, 1, p_paid);
fglrt_free(ctx, &v_paid);
v_paid = fglrt_assign(ctx, FGLRT_T_DATE, 0, 0, &_in);
fglrt_free(ctx, &_in); }
{ fglrt_val_t _in = fglrtmi_in_decimal(fparam, 2, p_per_day);
fglrt_free(ctx, &v_per_day);
v_per_day = fglrt_assign(ctx, FGLRT_T_DECIMAL, 8, 2, &_in);
fglrt_free(ctx, &_in); }
/* 4GL line 5 */
_t[0] = fglrt_sub(ctx, &v_paid, &v_due);
{ fglrt_val_t _n = fglrt_assign(ctx, FGLRT_T_INTEGER, 0, 0, &_t[0]); fglrt_free(ctx, &v_days); v_days = _n; }
fglrt_free(ctx, &_t[0]);
/* 4GL line 6 */
_t[0] = fglrt_int(FGLRT_T_INTEGER, 0);
_t[1] = fglrt_bool(fglrt_le(ctx, &v_days, &_t[0]));
_c = fglrt_istrue(fglrt_to_tri(&_t[1]));
fglrt_free(ctx, &_t[0]);
fglrt_free(ctx, &_t[1]);
if (_c) {
/* 4GL line 7 */
_t[0] = fglrt_int(FGLRT_T_INTEGER, 0);
{ mi_decimal *_rv = fglrtmi_ret_decimal(&_mi, &_t[0]);
FGL_CLEANUP();
return _rv; }
}
/* 4GL line 9 */
_t[0] = fglrt_mul(ctx, &v_days, &v_per_day);
{ mi_decimal *_rv = fglrtmi_ret_decimal(&_mi, &_t[0]);
FGL_CLEANUP();
return _rv; }
/* end of function */
FGLRTMI_NULLRET(&_mi);
FGL_CLEANUP();
return NULL;
}
#undef FGL_CLEANUP
#undef FGL_FREE_VARS
#undef FGL_NSLOTS
Results: 4GL and UDR side by side
| Case | 4GL result | UDR result | Same |
|---|---|---|---|
| late_fee, 10 days late at 1.50 | 15.00 | 15.0000000000000 | yes |
| late_fee, paid early | 0.00 | 0.00000000000000 | yes |
| ex_frac(.250), INTERVAL FRACTION | .750 | .750 | yes |
| ex_frac(.750), wraps past one second | .250 | .250 | yes |
ex_total (a FOREACH over a table, 30.75 for customer 1) needs a database connection, so it is shown only on the engine, in the results of calls.sql below.
Performance: processing-intense code
Each function is pure computation: no SQL runs inside it. The 4GL column is the time for one call inside the 4GL program (CURRENT before and after), best of three runs of the program. The UDR column is the wall clock of one EXECUTE FUNCTION through dbaccess, less an empty dbaccess session, best of three runs. Same machine, run back to back; timings move by tens of percent with load.
| Function | What it does | Result | 4GL (s) | UDR (s) | UDR vs 4GL |
|---|---|---|---|---|---|
ex_sum(5000000) | integer loop, 5,000,000 iterations | 30000000 | 2.167 | 0.590 | 3.7x faster |
ex_primes(100) | sieve of Eratosthenes over a 30,000-element array, 100 passes | 3245 | 0.909 | 0.808 | 1.1x faster |
ex_interest(1500000) | DECIMAL(16,6) compound interest, 1,500,000 steps | 3595648276.55830 | 1.514 | 0.731 | 2.1x faster |
ex_collatz(30000) | Collatz chain lengths for 1..30,000 (integer MOD, divide, branch) | 307 | 1.649 | 0.579 | 2.8x faster |
ex_chars(1500000) | CHAR(40) substring tests, 1,500,000 iterations | 225000 | 0.628 | 0.360 | 1.7x faster |
Reading it: both sides compute the same answers. The converted code is faster than c4gl on every one of these, by roughly 1.5x to 3x here. Three things account for it: the generated C is built with -O2 and the common integer operations are inlined into it (fglrt_fast.h); short strings are kept on a free list instead of going to the engine's allocator on every pass; and an exact integer divide skips the decimal divide. Code that spends its time in SQL, not in computation, will not see this. Rerun the script to see your own numbers.
All the engine calls
create table if not exists ex_orders (cust_id integer, amount decimal(12,2)); delete from ex_orders; insert into ex_orders values (1, 10.50); insert into ex_orders values (1, 20.25); insert into ex_orders values (2, 99.99); execute function late_fee(mdy(3,1,2024), mdy(3,11,2024), 1.50); execute function late_fee(mdy(3,10,2024), mdy(3,1,2024), 1.50); execute function late_fee(NULL, mdy(3,1,2024), 1.50); execute function ex_total(1); execute function ex_total(2); execute function ex_total(3); execute function ex_frac(interval(.250) fraction to fraction(3)); execute function ex_frac(interval(.750) fraction to fraction(3));
Table created.
(expression)
15.0000000000000
(expression)
0.00000000000000
(expression)
(expression)
30.75
(expression)
99.99
(expression)
0.00
(expression)
.750
(expression)
.250