Query performance using promoted properties - SmartPlant Foundation - IM Update 46 - Help - Hexagon

SmartPlant Foundation Help

Language
English
Product
SmartPlant Foundation
Search by Category
Help
SmartPlant Foundation / SDx Version
10
SmartPlant Markup Plus Version
10.0 (2019)
Smart Review Version
2020 (15.0)

A SmartPlant Foundation database is divided into several domain groups that include the following table types:

  • Object

  • Relationship

  • Interface

  • Property

  • Property Detail (This contains extended details for objects with very long property values.)

A subset of properties have previously been configured to take the latest value found in the property table and store it on the object table, such as Name. When you use a Quick Find, or a name-based query, the system accesses the column on the object table faster to increase general query performance.

The Description and SPFRevState properties also store their latest values in a column on the object table in the database for faster access.

Description property

The Description property is often used in queries as criteria since it contains information that makes it easier for you to identify objects. For example, the Description property can contain information the query is searching for, such as location or classification information.

The Description property value is limited to 512 characters in the object table. If you enter more than 512 characters, the object table entry is truncated to 512 characters. However, the latest entry in the object table is also stored in the property table, which reflects the full value of the Description property. The value displayed in Desktop Client will always be the full value, even if the object table entry has been truncated.

However, only 512 characters can be accessed by the search criteria in a query. If a customer wants to search on descriptions that are longer than 512 characters, you can set the StoreOnObjTable option on the description property to False, which makes the search function use only the property table.

SPFRevState

The SPFRevState property is used to filter document queries, such as when only the current and working revisions are to be returned. It is also used in a similar way for edge expansions. The SPFRevState property value is also now stored in a new column on the main object table to help improve document query performance. This functionality is supplied for the PUB, DATA, and DOC domain groups only. If there is a requirement to store documents in further domain groups, you have to manually add the specific column and related indexes to the database.

The following examples show SQL statements for adding a column and recommended indexes that use TEST as the name of the target domain group for each database type:

Oracle Database

ALTER TABLE " TESTOBJ" ADD "SPFREVSTATE" VARCHAR2(512 CHAR) NULL;

CREATE INDEX "IDX_ TESTOBJ_DUID_CFG_TD_UPREV" ON "TESTOBJ" ("OBJDEFUID", "DOMAINUID", "CONFIG", "TERMINATIONDATE", UPPER("SPFREVSTATE"));

Microsoft SQL Server

ALTER TABLE [dbo].[TESTOBJ] ADD [SPFREVSTATE] [nvarchar](512) NULL;

CREATE INDEX [IDX_TESTOBJ_REV_DES] ON [dbo].[TESTOBJ] ([DOMAINUID], [OBJDEFUID], [TERMINATIONDATE], [CONFIG]) INCLUDE ([OBID], [OBJNAME], [DESCRIPTION], [SPFREVSTATE]);

SPFMergeStatus

The SPFMergeStatus property is used to determine if an object or relationship has been terminated or deleted in a project, before the change is merged into the as-built system. This property is treated differently from the Description and SPFRevStatus properties, as the SPFMergeStatus property value is also stored in the Marked for removal column on the main object table. This optimizes the performance in SQL queries by accessing the latest value from the main object table.

It is not possible to remove the use of this column through configuration.