Performance Guidelines

Data types and storage

  • Use Integer Primary Keys as much as possible. This will reduce data fragmentation and fill factors. It will also help keep records created “near” each other more likely in the same database page.
  • Guid primary keys should only be used in replication scenarios where multiple instances of the same database need to be joined into a central database. You should not compromise the general use case for sake of special use cases.
  • Fields should try to use the shortest possible width that is adjusted to the business requirements. The more records that can fit into a database page the faster as reads and writes.
  • Especially try to avoid using Memo type fields unless strictly necessary. These fields bloat database size even when empty.
  • Genio will infer the necessary indexes based on the declared relations between tables. Each formula, EPH’s, Unique restrictions, List sortings and filters and other constraints will propose a support index. Keep these to what is strictly necessary for the business and try to be consistent in the constrains you use (ex. Try to sort always on the same default column) to avoid index bloat.

Calculations

  • Daily calculation formulas can become especially heavy. Avoid making business that depends on the current date. Instead of persisting things like age try to persist the basis of those concepts like the birth date.
  • Avoid using non-deterministic calculations in your formulas or validations. Deterministic functions are functions that for the same input are guaranteed to output the same output every time. Things like random numbers, database querys, filesystem acess or user context information should be avoided as much as possible, and in case of business formulas should not be use at all.