Your ultimate application performance depends largely on the complexity of the generated SQL statements in your application and on the underlying performance and load of the database to which the application is connected.
You can greatly increase your application’s performance by appropriate database table indexing. Generally, any database table field that is used in SQL WHERE and ORDER BY clauses should be indexed. Your database provides an easy way to specify the fields to be indexed, and the indexed fields are displayed on the Databases folder in Iron Speed Designer. In our experience, slow application performance is usually related to un-indexed tables. We can’t stress this enough!
Iron Speed Designer automatically generates SQL queries with these fields in WHERE and ORDER BY clauses:
Primary key fields
Virtual primary key fields
Foreign key fields
Virtual foreign key fields
Any field in a table panel that is specified as the ‘default sort’ (primary and secondary defaults) in the Table Panel Wizard.
Any field in a table panel that is sortable by application users. By default, all displayed fields are sortable in table panels, and each of these fields is used in the associated ORDER BY clause. (Sortable means the application user can click on the column heading and sort the displayed table based on the selected column heading.) Often times, you can significantly increase SQL query performance by making table panel columns non-sortable by application users, eliminating their inclusion in the SQL ORDER BY clause.
(1) Do you have indexes for all primary key fields in your database tables? If not, add them! Most Show Record and Edit Record pages access data via the database table’s primary key field.
(2) Do you have indexes for all virtual primary key fields in your database tables? (Virtual primary keys are declared within Iron Speed Designer but they reference actual database fields.)
(3) Do you have indexes for all foreign key fields used in your database tables? Most pages display data from tables joined via foreign key relationships, and adding indexes for these fields dramatically improves performance.
(4) Do you have indexes for all virtual foreign key fields used in your database tables? (Virtual foreign keys are declared within Iron Speed Designer but they reference actual database fields.)
(5) Do you have indexes for all fields that have corresponding sortable columns in any page in your Designer-generated application? End-users can click on any sortable field in a table to re-sort the table accordingly. If the sortable columns are not indexed, a full table scan generally results, which is quite slow in most databases of any size.
If you are specifying a default primary and secondary sort order for a table, make sure that both of these fields are indexed.
If you allow sorting of columns in a database view, make sure the column in the underlying table is indexed.
(6) Do you have indexes for all fields that have corresponding filter controls on any page in your Designer-generated application?
(7) Do you have indexes for all fields that used by search controls on any page in your Designer-generated applications?
(8) Do you have indexes for all fields used in WHERE clauses in multi-table joins created with Iron Speed Designer? (Multi-table joins are typically created through the various panel wizards in Iron Speed Designer.)
If you are using Views in your application, add a Virtual Primary Key to the View. You can create a composite Virtual Primary Key by designating two or more fields as keys.
If you are using Views in your application, remove the Order By clause in the view schema definition if possible. The application creates a SQL query based on the default sort order specified for the table, or the column sort order selected by the end user. This results in double sorting the view. If possible, remove the Order By clause in the view’s schema definition to avoid the double sorting and slowing down the application.