I have a Servoy solution that gets some data from FileMaker 11 Server, by running SQL queries
I would now like to show the FileMaker Table in a Form. It does work, but is a bit slow
it seems that Servoy gets all the PK’s, then loads all the fields. Since there are over 1 million records, this takes awhile ( not sure if this is limited to 200 records )
I did set the “onShow” property to start with an empty Found Set, but it did not help:
var fs = databaseManager.getFoundSet(‘filemaker’,‘media’);
forms.media_filemaker.controller.showRecords(fs);
and if I do a “Find” that fails, Servoy tries to load all records again !
- is there any way to stop Servoy from asking for all the PK’s ?
- can I prevent a failed “Find” from loading all records ?
- The form shows 5 columns, yet the Performance Log shows the query asks for ALL columns ? any way to control this ? ( this is a wide table )
greg
That was my next question … How does Servoy impose the 200 limit ?
From the sample query you posted, it appears they use LIMIT and OFFSET
However, it seems that FileMaker 11 and 12 SQL does not support these
greg
PS
it seems that Sybase SQL Anywhere does not support LIMIT and OFFSET either
The sql is not limited to 200 records! Working with large tables this will give big performance problems.
If after this the foundset is expanded, it works correct again:
_fs.setSelectedIndex(200)
will result in the following line in the performance data:
Load foundset select crm_contact_id from crm_contact where crm_contact_id in (select crm_contact_id from crm_contact) order by crm_contact_id asc limit ? offset ?
It seems the answer is using databaseManager.setCreateEmptyFormFoundsets() in my “opener” method, to start all foundsets as empty foundsets, and not search for Primary Keys
It appears to work
greg