Improving Database Performance

Your ultimate application performance depends largely on the underlying performance and load of the database to which the application is connected.

Iron Speed Designer creates the most efficient queries possible

Iron Speed Designer supports database tables of any size – whether they contain a few rows or tens of millions of records. Queries and stored procedures will only return the number of rows shown on the page. For example, if only 10 rows are being displayed out of 10 million records, the SQL query will retrieve only the 10 records being shown on the page. For the best performance, Iron Speed Designer performs server-side paging by searching, filtering and sorting within the SQL query or the stored procedure before returning the requested number of rows. No further filtering or sorting is ever performed at the user interface layer.

Since all pagination, searching, filtering and sorting are performed at the database query level, the performance of the query is highly dependent on the indexing of the database fields that are being used in the search, filter or sort.

Iron Speed Designer uses one of two pagination techniques based on the features available in the database product to retrieve only the minimum set of records regardless of the sorting. Oracle and SQL Server 2005 (and later versions) support the concept of a Row Number. Older databases products like SQL Server 2000 or Microsoft Access do not support Row Numbers, so a different technique is used. Iron Speed Designer automatically uses the most efficient of these two techniques based on the database product being used.

Example:

Please note that the queries displayed below are simplified queries shown for understandability and may not work exactly as specified.

The following query will display ten records from a Customers table sorted by state in descending order and last name in ascending order:

SELECT TOP 10 *

FROM Customers

WHERE Country = ‘USA’

ORDER BY State DESC, LastName ASC

This will display 10 records customers from Wyoming whose last names start with A.

However, this query cannot be used by itself if the user requests the 37th page, since only records 361 to 370 should be returned. The SQL language standard does not support requesting a range of records like 361 to 370 and different database vendors have implemented this requirement in their own custom extensions to the SQL language standard. Iron Speed Designer takes advantage of the SQL extensions if they are provided by the underlying database product.

Oracle: Oracle has extended PLSQL to support a ROWNUM pseudo column. Iron Speed Designer uses the ROWNUM pseudo column and creates a query such as:

SELECT TOP 10 *

FROM Customers

WHERE Country = ‘USA’ and ROWNUM >= 361 and ROWNUM <= 370

ORDER BY State DESC, LastName ASC

MySQL: MySQL allows the creation of a row number pseudo column and then this can be used to get the specific rows.

SELECT *

FROM (SELECT @rownum:=@rownum+1 rownum, Customers.*

FROM (SELECT @rownum:=0) r, Customers

WHERE Country = ‘USA’)

WHERE rownum >= 361 and rownum <= 370

Microsoft Access and Microsoft SQL Server 2000: A row number feature is not supported in these products. Iron Speed Designer uses a triple somersault technique to retrieve the appropriate set of records. This triple somersault technique first retrieves the TOP 370 records, then it reverse sorts them and retrieves the TOP 10 records, and then it reverse sorts them again to return the records in the requested sort order.

SELECT *

FROM

(SELECT TOP 10 *

FROM

(SELECT TOP 370 *

FROM Customers

WHERE Country = ‘USA’

ORDER BY State DESC, LastName ASC)

ORDER BY State ASC, LastName DESC)

ORDER BY State DESC, LastName ASC

Each of these techniques requires that all columns used for searching, filtering and sorting must be indexed in order to get the best performance. When the columns are indexed, the database products are very good at ensuring that these types of queries can be executed in the most efficient manner.

SQL Server 2005 and later: Microsoft has extend TSQL to support a ROW_NUMBER() function. This function is not available in SQL Server 7 or SQL Server 2000. Iron Speed Designer will create a query such as:

SELECT * FROM

(SELECT ROW_NUMBER() OVER (ORDER BY State DESC, LastName ASC) AS row_number, *

FROM Customers

WHERE Country = ‘USA’

) temp

WHERE row_number >= 361 and row_number <= 370

By default, Iron Speed Designer execute queries with ROW_NUMBER(), but you can set to use somersault in database table property to execute queries liked Microsoft SQL 2000:

Index your tables!

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 Databases 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 treat 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 treat 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.)

Improving performance table pages

On a show or edit table page that handles million of records in the database table, you might see that the page takes longer time to be loaded because executing SELECT COUNT(*) requires longer time. In order to improve the loading time, you need to do the following to avoid from running this query. This method works for Microsoft SQL 2005 or above, Inline only.

On the database table property, set the table to use RowNum:

This change will only be effective if your pagination control does not use total record count. If your page has a pagination control where the total record count is used, then, you must change the ASCX file path for the pagination control so that it points to the pagination ASCX control that does no show the total record count as shown in the following screen shot.

Improving performance in database views

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.