ice72b

FM srv ODBC performance

Discussion created by ice72b on Mar 12, 2018
Latest reply on Mar 12, 2018 by ice72b

Hi,

 

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).

 

This SELECT:

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).

 

Any idea?


TIA

Roberto C.

Outcomes