Database Optimization Function in Sage X3

In sage x3, we have a function called database optimization which is used to track or create custom index. This function help us for easy reapplication when updating or migrating data set. It is useful  to create the indexes in order to achieve a specific optimization (for example in a report or to optimize a complex inquiry). A typical example of this is the addition of an index to a large table to accelerate the creation of data in an inquiry created by the requestor, or the use of a dedicated index in the object setup, to use a specific order in a left list.

Database Optimization function comes  under the Setup module and therefore  it is folder specific. There is a copy function to allow you to copy custom indexes to other endpoint folders. Make sure you select the proper folder.

Follow the below steps:

Navigate to > Set up > Usage > Data > Database Optimization

  • Table: In this column, we will enter the table code of the table in which you want to add custom index.
  • Index: In this column, we have to label the index starting with SPE_ then whatever else you want to label it as. It must start with SPE_ or else the index will not add.
  • Index Descriptor: The third column is the index descriptor which is the organizing field columns in which you want to use. Here we have to enter the field based on which you need to add index. For the column separator you must use minus (-) or plus (+).
  • Active: The Active column is a simple yes/no for if the index is active or not.
  • Comments: Lastly the Comments column is just a text field for you to leave yourself comments about the index.

Here we are using ZTEMP table and are adding the “ZITMREF+ZSTOFCY ” as index descriptors and will keep this as active.

Now click on save. Once we have saved we have to click on RUN button to apply the index. This will create an index for the table ZTEMP. Refer the below screen shot.

Fig01: Database optimization screen

Note, it will apply all indexes marked as active.

If users are in the system the the below warning message will pop up and  the index will not be applied.

Fig02: Warning Message

If there will be no pop up msg then everything is successful.

We cant able to see the custom index through table dictionary. Custom indexes applied from the Database Optimization function are written directly to SQL. You will have to log into SQL Server to verify that the index was applied. Refer the below screenshot.

Fig03: SQL Server

It is also possible to remove the custom index. If you want to remove a custom index, change the active column from yes to no and click the run button again to remove the index.

Fig04: Database Optimization

You can verify the same by logging into SQL.

Fig05: SQL

In this way we can add or remove custom index from table by using database optimization function.

About Us

Greytrix – a globally recognized and one of the oldest Sage Development Partner is a one-stop solution provider for Sage ERP and Sage CRM organizational needs. Being acknowledged and rewarded for multi-man years of experience, we bring complete end-to-end assistance for your technical consultations, product customizations, data migration, system integrations, third-party add-on development and implementation competence. 

Greytrix caters to a wide range of Sage X3, a Sage Business Cloud Solution, offerings. Our unique GUMU™ integrations include Sage X3 for Sage CRMSalesforce.com, Dynamics 365 CRM and Magento eCommerce along with Implementation and Technical Support worldwide for Sage X3. Currently we are Sage X3 Implementation Partner in East Africa, Middle East, Australia, Asia, US, UK. We also offer best-in-class Sage X3 customization and development services, integrated applications such as POS | WMS | Payment Gateway | Shipping System | Business Intelligence | eCommerce and have developed add-ons such as Catch – Weight  and Letter of Credit and India Legislation for Sage X3 to Sage business partners, end users and Sage PSG worldwide. Greytrix Sales Commission App for Sage X3 simplifies commission calculations and postings on sales transactions.

The GUMU™ Cloud framework by Greytrix forms the backbone of cloud integrations that are managed in real-time for processing and execution of application programs at the click of a button.

Greytrix is a recognized Sage champion ISV Partner for GUMU™ Sage X3 – Sage CRM integration also listed on Sage Marketplace; GUMU™ integration for Sage X3 – Salesforce is a 5-star rated app listed on Salesforce AppExchange and GUMU™ integration for Dynamics 365 CRM – Sage ERP listed on Microsoft AppSource.

For more information on Sage X3 Integration and Services, please contact us at x3@greytrix.com, We would like to hear from you.