Are there any guidelines on how to inprove/ensure application/database performance? Because there is no possiblity to directly influence the acutal SQl query that the Xas generates and executes, I would like to know the following: Are there guidelinesrules/best practises on when to define indexes on what objects? Are there guidelines/rules/best practises on how to define xpath which constraints in what sequence on datagrids? Do the guidelines differ with the type of database used?
With the risk of stating the obvious, here is some advice:
Index attributes that are searched on
Unless creating/updating becomes a performance bottleneck don't be shy to create 20-30 indexes
Index on multiple attributes if searches are on multiple attributes.
Don't forget the first attribute is the entry to the index. If that attribute is not in the query the index will not be used
Prevent NOT-EQUAL searches where possible (in xpath or search grid). They cannot use an index. If needed combine query with another attribute that is a first attribute in a combined index
Do not create indexes on small tables (less than 1000 entries). The database can keep them in memory
Monitor queries and tune afterwards. Oracle - the database I have most experience with - can collect statistics like a top list of queries by disk-access (not using index), duration or many other stats. Remember that database do differ, but probably not on the basics.
Take special care with =empty queries. To use an index you need to make a combined index with an attribute following that is never empty. At least Oracle does not store empty's (NULL's) in the index.
Since databases optimize queries the order in the statement should not matter
In many cases you can gain some performance by writing Java actions. For example to cache objects in HashMaps, or to use batches so that the server keeps only a limited set of objects in memory.
Further, if you modify associations in Java, you can gain much performance on large sets, if you work with raw IMendixIdentifiers, without instantiating the corresponding IMendixObjects.