Improving Database Performance

For the most part, applications generated by m-Power are run and executed in a timely fashion. However, there are the odd times when end-users have to wait too long for an application to return a dataset. In the case of slow database performance, an index can be created to significantly speed up your application. This document discusses indexes in general and also explains how to create them yourself. However, continue reading if you are interested in learning how to use m-Power to create indexes on your behalf.

m-Power to the Rescue

Of course, not everyone is a knowledgeable DB Administrator and that's where m-Power helps by creating indexes on your behalf. To access this tool, click the "DB Performance" button under the "Other Operations" menu on the "Build and Customize" screen.

You will then be brought to a screen showing Suggested and Optional Indexes.

Suggested Indexes are recommended by mrc to create in order to help the performance of your application. These indexes are suggested based on selected sequence and record selection fields.

Optional Indexes are listed in the chance that users decide to invoke column sorting/searching at run-time. Because invoking any one of these options at run-time modifies the SQL statement, it is possible that Suggested Indexes would not be sufficient. However, if you don't plan on allowing users this type of sorting/searching functionality at run-time, there is no need to create these indexes.

The syntax of the CREATE INDEX statement uses an incremental numbering system by default (Represented by "_____"). For instance, CREATE INDEX SCHEMA/MPIDX_____ ON SCHEMA/TABLE (FIELD) will create the first index named MPIDX00001 in SCHEMA. The second index will be named MPIDX00002, and so on. However, if you would like to control the naming of the indexes on your system, you have the ability to do so by removing the underscores and entering in your own naming convention.

Once you have decided what Indexes you wish to create, click the green "plus" icon next to the index. You will see an animated bar indicating that your index is being created. Once it has completed, you will see a green "check" mark. Additionally, if you allowed m-Power to auto-number your index, you will notice that the full index name has now been given.

Once you have created the necessary indexes, simply re-run your application. You do not need to register your index or even recompile your application as your DB's SQL manager will automatically recognize the SQL Index.

In the example above, I decided to create my Suggested index but only one of my Optional indexes. I will not allow my users to sort/search on Invoice # (INVNUM) or Invoce Line (INVLINE), so there is no performance boost in creating those indexes.

Limiting Control to the Database Performance Option

If you do not want certain users to access the Database Performance Option, simply go to the "Admin Menu" -> "System Management" -> "Manage m-Power Developers." Click the "Edit" icon for the User in question. Be sure to set their "DB Performance" option to "No". Click "Accept". That user will no longer be able to access the "Database Performance" page to create indexes.

Release Notes

• Indexes are built based on tables/fields, not applications. This means that previously built indexes may be applicable to an application you just created.
• m-Power intentionally shows you the SQL statement it uses to create the index on your behalf. This is particularly useful if you need to create a similar index on another database server (For instance, a production database server).
• The only portion of the Index SQL String you should modify is the "MPIDX_____" portion. Changing any other text could affect the creation of your index.
• If you need to drop an index, please see this document.
• Changing your application specifications could cause a previously built index to be ignored by the SQL manager. To minimize unneeded indexes, mrc recommends building indexes only after you have finalized any changes to your application.