I have learned that when adding new records into a table, sometimes the more elegant, readable code that Servoy allows carries a performance hit and you are better off resorting to code that is more efficient, though less elegant. Here's an example.
Let's say I have tables A and B, where B is the child of A (i.e. A to B is one-to-many).
I have a foundset called fsA on table A.
I have a relation called A_to_B.
I have a record selected in fsA and all I want to do is add a new child B record.
To insert the new child record I could do this:
- Code: Select all
fsA.A_to_B.newRecord()
databaseManager.saveData()
This is convenient because it automatically assigns the foreign key(s) in my new B record, and it's also nice, compact, easy-to-understand code.
The problem is that it does more than I really need it to. In addition to inserting the new record it does the following:
1 - it builds the A_to_B related foundset, (i.e. it performs a select for all B records related to the current A record.)
2 - it resolves any aggregates for the A_to_B foundset (i.e. if I have any aggregations defined on table B, then it performs the required SQL to derive those for the A_to_B foundset)
In those situations I get better performance if I do this:
- Code: Select all
fsB = databaseManager.getFoundset('server_name','B')
fsB.newRecord()
fsB.foreign_key = foundset.pk_id
databaseManager.saveData()
This does one and only one thing: it performs the insert.
So in general, when performance is a top priority, you want to be careful about making references to related foundsets in your code. Only refer to the related foundset if you really need the related records. Otherwise, find another way to code your process.
By the way, the way I verified all of this was by using the Performance Data page of the Admin Console. Anytime you want to test what SQL operations are being generated by a specfic section of your code, just go to the Performance Data page, clear the statistics that are there, run your code and then refresh the Performance Data page - there you will see exactly what queries your code just generated.
I welcome your comments/corrections/feedback.
