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!
These fields should be indexed in your database:
Primary key fields. Most Show Record and Edit Record pages access data via the database table’s primary key field.
Virtual primary key fields. Many database views do not have regular primary keys declared in the database, so designating them as Virtual Primary Keys instructs Iron Speed Designer to generate code treating them as primary keys. Accordingly, those fields should be indexed to speed record retrieval. (Virtual primary keys are declared within Iron Speed Designer but they reference actual database fields.)
Foreign key fields. Most pages display data from tables joined via foreign key relationships, and adding indexes for these fields dramatically improves performance.
Virtual foreign key fields. Many database views do not have regular foreign keys declared in the database, so designating them as Virtual Foreign Keys instructs Iron Speed Designer to generate code treating them as foreign keys. Accordingly, those fields should be indexed to speed record retrieval. (Virtual foreign keys are declared within Iron Speed Designer but they reference actual database fields.)
Any field in a table panel that is sortable, including primary and secondary sorting defaults specified in the Configuration Wizard. 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.
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.
Any field used in a filter (filter dropdowns, list boxes and text boxes).
Any field used in a full-text search (search control).
Any field used in a WHERE clause in a multi-table joins created with Iron Speed Designer. (Multi-table joins are typically created through the Query Wizard 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.
Performance Tuning
Improving Database Performance
Improving Application Performance
View State Storage Options for Improving Performance
Windows 2003 Application Performance Suggestions
Running Applications on Multiple Servers