How to create persistent views

Open: Development > Data and parameters > Views

Introduction

Persistent views are indexed views (Microsoft SQL Server) or materialized views (Oracle) that improve query performance by storing the result set of a query.

Sage recommends working with persistent views to enhance the user experience. When integrated into functional processes, persistent views can significantly improve overall system performance. However, their use can not be suitable in all scenarios. It is important to assess the relevance of persistent views within specific business processes before implementation.

This guide walks you through the steps to create and manage persistent views in Sage X3.

Database requirements

Ensure you follow the specific database requirements for creating persistent views:

  • Prefix the table name with the folder name using %APPLI%.

    Copy 
    SELECT CODACT_0, CODFIC_0, CODIND_0, DESCRIPT_0, NUMLIG_0, HOMONYM_0 
    FROM %APPLI%.ATABIND

  • A persistent view cannot contain another view.

  • The view must contain at least one key that is not duplicated.

  • A persistent view cannot contain an API node. Therefore, you cannot select the Node (API) checkbox for persistent views.

Additional requirements

Refer to the following links for specific restrictions:

Create a persistent view

  1. Go to Development > Data and parameters > Views.

  2. Enter the view's code, abbreviation, and description.

  3. Select the Active checkbox.

  4. Select the Persistent checkbox in the Query section to make the view persistent.

  5. Add the relevant prefixes before the table names in the code sections for Oracle and SQL Server.

  6. Update the keys ensuring at least one key is not duplicated. To do so, set the Duplicates field to No for at least one key.

  7. Save and validate your changes.

Locate your persistent view

  • If you are using an Oracle database, you can find your persistent view is in the Materialized/Indexed Views folder.

  • For an SQL Server database, all views are located in a single View folder. The index is in the Indexes subfolder under your view’s name.

Change view persistence

To revert a persistent view to a regular view, clear the Persistent checkbox and validate.

In your database, the view will move to the regular Views folder.