Performance Considerations

Overview

This document is intended for m-Power developers who are familiar with SQL and have a general understanding of DBA.

m-Power applications are designed with speed as paramount. Sometimes, a generated application can be slow, but there are ways to improve the performance. Below, you will see a table of some of the most common culprits of poor performance and how to resolve them.

Problem	Diagnose	Resolution	Common
Poor DB Performance	Turn on debug and check timing stats.	Follow the steps outlined in the current document.	Most
Too Much Data Returned to Screen	Have you changed the record governor? Does the page eventually load and show thousands of records?	Redesign the application to avoid too much data. For example, summarize data first or require runtime selections with JavaScript.	Occasional
Server Issues	Are you running Tomcat from an AS400? Is Tomcat not being run as a Service?	Consider setting up Tomcat on a PC server, as the performance is far superior. Be sure Tomcat is being run as a service. This document can assist in both cases.	Rare
Network Issues	When you use m-Power, is the Field Settings screen especially slow?	Ensure DB server and m-Power Application server are located within the same network.	Rare

The remainder of this document will address poor DB performance. The first thing that can be done is to turn on debug (available from the Application Properties screen), and inspect the timer count.

This application does not need an Index		This application would benefit from an Index

You’ll see that the Java application logic time is usually sub-second, often less than one-hundredth of a second (first image). If an application is slow to load (second image), it is almost always a matter of poor DB performance. That can be corrected by creating the proper indexes in the DB or by changing the application to match existing indexes. If matching existing indexes cannot be achieved, then this document describes techniques to improving performance by creating indexes. mrc recommends that you create indexes over your well known large queries to dramatically increase performance for end users. When an m-Power application is run, it sends an SQL request to the DB and it analyzes the SQL statement. If the DB finds a matching index, it will use it to immediately return the dataset. If one cannot be found, the end user must wait while a temporary one is built for them on-the-fly. By building the index ahead of time, your end user does not have to wait while one is created. Also, bear in mind that an index does not always need to be created for every application; rather it should be used as a tool to help performance with very large datasets or with applications that are too slow for end users. Lastly, remember that your DB will decide when to use the proper index. With this in mind, realize that one index could greatly increase the performance of numerous applications.

How to Build an Index

In general, indexes should be built by including all fields that are used within the WHERE clause and the ORDER BY clause. In m-Power speak, this would include Sequence Keys and fields used with Record Selections. When declaring fields to your index, every m-Power application requires that you specify the fields in a specific order:

1. Hard coded record selections
2. Used Runtime record selections
3. Sequence Keys
4. Each field, when using GROUP BY statement (Reports only)

Below we will discuss each m-Power template in more detail to give you a better idea of how to build indexes for various scenarios.

Retrievals

For most scenarios, users describe retrievals as being relatively quick due to the small dataset returned to the screen. However, retrieval performance can still be increased where necessary.

Basic Example

Let’s say I am working in the MRCWORKLIB data dictionary, building an index over a table named SALESHIST from the FRNDTA021 library. Further, let’s say in this most basic case that I will sequence by two fields: CUSNO & ORDERNUM. I also plan on turning off advanced sort and advanced search. When creating your index, there is not a specific naming structure that you must use. However, it is useful to stay organized by keeping consistent with your naming structure. For every example described in this document, you will see a prefix of “idx_” followed by an abbreviation of the table name, followed by a unique identifier. To create the index, enter your SQL statement with the following syntax:

CREATE INDEX MRCWORKLIB.idx_SHISA1 ON FRNDTA021.SALESHIST (CUSNO, ORDERNUM);

Explanation of Index Created

The above index was created with CUSNO and ORDERNUM because they are both sequence keys.

Advanced Example

In this example, I will duplicate the example above, however with a few modifications. In this case, I will include a hard coded record selection of CMPNO equal to 1, and also will enable advanced search and advanced sort. Because I will allow users to sort, I am effectively allowing them to change the Sequence (ORDER BY) key at runtime. Since I do not know what field they will sequence by, I must account for each case. If my application has 8 fields, I would need to create 8 separate indexes, one for each field my user may sort by. Because of this, we highly recommend removing unnecessary sorting keys via m-Painter. Not only does this clean up the screen painting, it will remove the chance the user attempts to access a non-indexed dataset. My indexes would look like this:

1. CREATE INDEX MRCWORKLIB.idx_SHISB1 ON FRNDTA021.SALESHIST (CMPNO, CUSNO, ORDERNUM);
2. CREATE INDEX MRCWORKLIB.idx_SHISB2 ON FRNDTA021.SALESHIST (CMPNO, ORDERNUM);
3. CREATE INDEX MRCWORKLIB.idx_SHISB3 ON FRNDTA021.SALESHIST (CMPNO, FIELD4);
4. etc.

…

Explanation of Indexes Created

1. CMPNO is first because it is a hard coded record selection. CUSNO and ORDNUM are listed next because they are sequence keys.
2. This index was created to account for the instance where an end user decides to ad-hoc sort on the ORDERNUM field.
3. This index was created to account for the instance where an end user decides to ad-hoc sort on the FIELD4 field.

Since I also turned on Advanced Search, I should build indexes to take into account if a user decides to search. In this case, the advanced search field would be added to the index after any hard coded record selection fields, but before any sequence key fields. Using the example from above, I want to allow the user to search on FIELD4. Here are the indexes I should build:

1. CREATE INDEX MRCWORKLIB.idx_SHISC1 ON FRNDTA021.SALESHIST (CMPNO, CUSNO, ORDERNUM);
2. CREATE INDEX MRCWORKLIB.idx_SHISC2 ON FRNDTA021.SALESHIST (CMPNO, ORDERNUM);
3. CREATE INDEX MRCWORKLIB.idx_SHISC3 ON FRNDTA021.SALESHIST (CMPNO, FIELD4);
4. CREATE INDEX MRCWORKLIB.idx_SHISC4 ON FRNDTA021.SALESHIST (CMPNO, FIELD4, CUSNO);
5. CREATE INDEX MRCWORKLIB.idx_SHISC5 ON FRNDTA021.SALESHIST (CMPNO, FIELD4, ORDERNUM);

Explanation of Indexes Created

1. CMPNO is first because it is a hard coded record selection. CUSNO and ORDNUM are listed next because they are sequence keys. FIELD4 would not be used for ad-hoc search.
2. Takes into account an instance where the end user ad-hoc sorts on ORDERNUM field. FIELD4 would not be used for ad-hoc search.
3. Takes into account an instance where the end user ad-hoc sorts on FIELD4 field or if FIELD4 is used in an ad-hoc search.
4. Takes into account an instance where the end user ad-hoc searches on FIELD4 field, and ad-hoc sorts on CUSNO.
5. Takes into account an instance where the end user ad-hoc searches on FIELD4 field, and ad-hoc sorts on ORDERNUM.

As you can tell, the more options you give your end user to search or sort by, the number of indexes needed grows rapidly. Consider carefully the ad-hoc abilities your end user truly needs, then plan your indexes accordingly.

In conclusion, use these rules of thumb when building indexes for retrievals:

1. Turn on debug to see the SQL statement (WHERE and ORDER BY clauses).
2. Only build indexes where necessary.
3. Consider limiting user ad-hoc options to simplify the cases in which an index would be required (by removing unnecessary sorting and searching options).
4. Build indexes using fields used in the ORDER BY and WHERE clauses.
5. The order of fields listed needs to be: Hard coded record selections, used runtime record selections, then sequence keys.

Reports

Reports differ from retrievals based on how data is returned to the screen. While retrievals display a handful of records at a time, reports will return the entire result set to the screen. Please note that the time it takes to return large sets of data to your browser will not be decreased by proper indexes.

Standard Report Templates with Detail

When considering an index for a standard report with detail, the overall process is similar to that of retrievals, more information can found in this section. Aside from those instructions, reports differ significantly from retrievals in terms of runtime record selections. See this section for working with prompted record selection.

For example, let’s say I have a standard report with 5 fields (FIELDA, FIELDB, FIELDC, FIELDD, & FIELDE), I plan on showing a detail report. FIELDA is a hard coded record selection, FIELDB is my runtime record selection, and FIELDC is my sequence key. I plan on using simple JavaScript to force my user to enter the runtime record selection on FIELDB. My index would look like this:

CREATE INDEX MRCWORKLIB.idx_SHISD1 ON FRNDTA021.SALESHIST (FIELDA, FIELDB, FIELDC);

Explanation of Index Created

The above index was created in the listed order because FIELDA is a hard coded record selection, FIELDB is a runtime record selection, and FIELDC is a sequence key.

Interactive Report Template & Creating Dropdown Lists from the Current Application

If you choose to use this popular feature, consider building an additional index per record selection. When you use this feature, you are asking the database to run an additional SQL statement to populate this list. For this example, assume the three record selections will be on: FIELDA, FIELDB, and FIELDC. To ensure this dropdown lists populate quickly, you would need to create the following indexes:

1. CREATE INDEX MRCWORKLIB.idx_SHISF1 ON FRNDTA021.SALESHIST (FIELDA);
2. CREATE INDEX MRCWORKLIB.idx_SHISF2 ON FRNDTA021.SALESHIST (FIELDB);
3. CREATE INDEX MRCWORKLIB.idx_SHISF3 ON FRNDTA021.SALESHIST (FIELDC);

Explanation of Indexes Created

Each index was created for the same purpose. Because each record selection field is being used for a dropdown list, they each need their own index.

Standard Report Templates with No Detail & Interactive Report Templates

The following section explains how to handle the use of a standard report template with no detail or an Interactive Report. In either case, the SQL statement is executed the same, using GROUP BY extensively within the SQL statement. The indexes you build would need to include each field that will be included within the GROUP BY statement. If the DB has to determine this logic on the fly, it might be noticeably slow for end users. For this example, let’s assume I have a non-detail Report (or an Interactive Report Template application) that consists of five fields (FIELDA, FIELDB, FIELDC, FIELDD, and FIELDE). FIELDA is the sequence key, FIELDB is a hardcoded record selection, and FIELDC and FIELDA are runtime record selection with dropdown lists. I also want to enable sorting. I would need to build the following indexes, at minimum:

1. CREATE INDEX MRCWORKLIB.idx_SHISE1 ON FRNDTA021.SALESHIST (FIELDC);
2. CREATE INDEX MRCWORKLIB.idx_SHISE2 ON FRNDTA021.SALESHIST (FIELDA);
3. CREATE INDEX MRCWORKLIB.idx_SHISE3 ON FRNDTA021.SALESHIST (FIELDB, FIELDC, FIELDA, FIELDD, FIELDE);
4. CREATE INDEX MRCWORKLIB.idx_SHISE4 ON FRNDTA021.SALESHIST (FIELDB, FIELDA, FIELDC, FIELDD, FIELDE);
5. CREATE INDEX MRCWORKLIB.idx_SHISE6 ON FRNDTA021.SALESHIST (FIELDB);
6. CREATE INDEX MRCWORKLIB.idx_SHISE7 ON FRNDTA021.SALESHIST (FIELDD);
7. CREATE INDEX MRCWORKLIB.idx_SHISE8 ON FRNDTA021.SALESHIST (FIELDE);

Explanation of Indexes Created

1. Was created because FIELDC is a runtime record selection with a dropdown list.
2. Was created because FIELDA is a runtime record selection with a dropdown list.
3. Was created with FIELDB first because it is a hard coded record selection, FIELDC may be chosen as the first record selection, then FIELDA as the second. Lastly, all other fields are listed because they are part of the GROUP BY statement.
4. Was created with FIELDB first because it is a hard coded record selection, FIELDA may be chosen as the first record selection, then FIELDC as the second. Lastly, all other fields are listed because they are part of the GROUP BY statement.
5. Was created to account for the user ad-hoc sorting on FIELDB.
6. Was created to account for the user ad-hoc sorting on FIELDD.
7. Was created to account for the user ad-hoc sorting on FIELDE.

Working with Runtime Record Selections

As you may know, runtime record selections in reports are optional for the end user; they can choose whether or not to use them at runtime. While this feature is useful for greater flexibility, it does make index creation more complicated. When your DB analyzes m-Power’s SQL statement, it checks to see the WHERE and ORDER BY fields that are used. When your end user skips a record selection, m-Power will not include this field from the WHERE portion of the SQL statement. With a given field missing from the SQL statement, a pre-built index may no longer match. Let’s look at an example:

You built an index in MRCWORKLIB, called idx_SHISG1, using the following fields from FRNDTA021.SALESHIST: FIELDA, FIELDB, FIELDC, FIELDD. You then wrote many reports over this data. FIELDA & FIELDB were hard coded record selections, where FIELDC & FIELDD were sequence keys. These reports were very, very fast. Now, you want to let your end user select what the record selection should be for FIELDA & FIELDB at runtime.

If your end user selects values for FIELDA & FIELDB at runtime, the application will still be fast because your pre-built index will be used. However, if your end user skips one or both of these record selections, the report will be slow because your sql statement will no longer match your pre-built index.

There are two solutions for this issue:

1. Utilize JavaScript to force the end user to select both record selection fields.
2. Build additional indexes to take into account each scenario of record selections, such as:
   1. All selections
   2. Either selection used
   3. No selections used

1. CREATE INDEX MRCWORKLIB.idx_SHISG1 ON FRNDTA021.SALESHIST (FIELDA, FIELDB, FIELDC, FIELDD);
2. CREATE INDEX MRCWORKLIB.idx_SHISG2 ON FRNDTA021.SALESHIST (FIELDA, FIELDC, FIELDD);
3. CREATE INDEX MRCWORKLIB.idx_SHISG3 ON FRNDTA021.SALESHIST (FIELDB, FIELDC, FIELDD);
4. CREATE INDEX MRCWORKLIB.idx_SHISG4 ON FRNDTA021.SALESHIST (FIELDC, FIELDD);

Explanation of Indexes Created

1. Was created for the scenario of both runtime record selections being used.
2. Was created for the scenario of just FIELDA runtime record selection being used.
3. Was created for the scenario of just FIELDB runtime record selection being used.
4. Was created for the scenario of neither runtime record selection being used.

In conclusion, use these rules of thumb when building indexes for reports:

1. Turn on debug to see the SQL statement (WHERE and ORDER BY clauses).
2. In standard m-Power Reports, build indexes with hard coded record selections first, used runtime selections next, and sequence keys last. Recall that if users omit certain record selections, your pre-built index may be ignored. Consider implementing scripts to force users to enter all required record selections.
3. When using GROUP BY, (either with Interactive Report Template or a subtotal only report), you will need to add each field that is used in your application to the index.
4. When building an Interactive Report Template and use dropdown lists to populate data, consider building individual indexes for each field to ensure dropdown lists load quickly.

Summaries

Generally speaking, summaries are designed to run in batch so application speed is less crucial. However, it is possible to affect application speed. The DB work for m-Power summaries is similar to that of reports with detail. See this section for more detail.

Maintenance

mrc recommends that maintenance applications over large tables be created to match pre-existing indexes. If those do not exist, please build them in the same field orders that retrievals use: Hard coded record selections, used runtime record selections, then sequence keys.

Other Things to Consider

Joins

As you create your indexes, remember that indexes are built over single tables. When creating applications that use more than one table, take note of the fields used in the join(s), as this information will be used when creating your indexes. Generally speaking, an application with two tables will have at least 2 indexes; one for each table. When creating the index for secondary tables, be sure to list the join field in the list of fields. For example, let’s say I am creating an application with two tables, joining on CUSNO. My record selection field (FIELDA) and sequencing field (FIELDB) will come from my primary table (SALESHIST), only a description field will come from my secondary table (DMCMP100).

1. CREATE INDEX MRCWORKLIB.idx_SHISH1 ON FRNDTA021.SALESHIST (FIELDA, FIELDB);
2. CREATE INDEX MRCWORKLIB.idx_DMCMH2 ON FRNDTA021.DMCMP100 (CUSNO);

Explanation of Indexes Created

1. Was created for primary table. FIELDA is my record selection and FIELDB is my sequence key.
2. Was created for my secondary table. CUSNO is my join field that links primary and secondary table.

Other Databases

1. To delete an index, use the following syntax:
   • AS400 — DROP INDEX index_name
   • Oracle — DROP INDEX index_name
   • MySQL — DROP INDEX index_name ON schema_name.table_name
   • MSSQL 2000 — DROP INDEX index_name ON schema_name.table_name
   • MSSQL 2005/2008 — DROP INDEX index_name ON schema_name.dbo.table_name
2. Expect MySQL indexes to take an equal amount of time to be dropped as it took to be created.

General Notes

• Updating a table with indexes usually takes more time than updating a table without, because the indexes are updated as well. So you should only create indexes on tables that will be frequently searched against by your users.
• When creating indexes, the index name must be unique to the schema in which it is created.
• The time it takes to build the index and the size required to store this file have a direct relationship with the number of fields you include in your CREATE INDEX statement.