I have an FM16 server (win 2012, 2 core, 4GB RAM, 100GB HD) with ODBC server enabled. I have created a DB with simple tables (no calculated fields):
- tst: id (integer PK, auto-inc), txt1 TEXT
- tst2: id (integer PK, auto-inc), idTbl1 (integer, fk on tst1), txt2 TEXT
The tst.id, tst2.id and tst2.idTbl1 fields are indexed and a relation is established between: tst.id<->tst2.idTbl1
I have written a small java program which connect to FMSrv via JDBC. The INSERT ratio is acceptable (100 rec/sec),
but the SELECT are slow.
Particularly a "SELECT MAX(id) FROM tst" require 2 sec with 20,000 records. It's more fast to execute a "SELECT id FROM tst" and calc the MAX in java (0.7 sec) (the "SELECT MAX" is concurrency weak, but this is another problem).
SELECT tst.id,COUNT(tst2.id) FROM tst INNER JOIN tst2 ON tst.id=tst2.idTst GROUP BY tst.id
with 20,000 rec on tst and 60,000 on tst2 require an ... infinite time (I stopped it after 17mins with FMXDBC_LISTENER process on the server fixed to a 100% CPU load).