INFORMIX
Extending INFORMIX-Universal Server: User-Defined Routines
Chapter 5: Performance Considerations
Home Contents Index Master Index New Book

I/O Considerations

 The database server stores user-defined routines and triggers in the following system catalog tables:

These system catalog tables can grow very large with heavy use of user-defined routines in a database. You can tune the key system catalogs as you would any heavily utilized data tables. To improve performance, use the following methods:

Isolate System Catalogs

 If your database server has multiple physical disks available, you can isolate your system catalogs on a single device and place the tables for your application in a separate dbspace that resides on a different device. This separation reduces contention for the same device.

Balance the I/O Activities

If you have a large number of user-defined routines that span multiple extents, you can spread the system catalog tables across separate physical devices (chunks) within the same dbspace to balance the I/O activities.

To spread user-defined routine catalogs across devices

    1. Create the dbspace for the user-defined routine system catalog tables with several chunks. Create each chunk for the dbspace on a separate disk.

    2. Use the CREATE DATABASE statement with the IN dbspace clause to isolate the system catalog tables in their own dbspace.

    3. Load approximately one-half of your user-defined routines with the CREATE PROCEDURE or CREATE FUNCTION statement.

    4. Create a temporary table in the dbspace with an extent size large enough to use the remainder of the disk space in the first chunk.

    5. Load the remainder of the user-defined routines. The last half of the routines should spill into the second chunk.

    6. Drop the temporary table.



Extending INFORMIX-Universal Server: User-Defined Routines, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.