Legacy platform

Specific tuning by using key randomizer

To avoid performance bottlenecks in the index blocks, primary keys are enhanced by using the key randomizer for specific tables.

The Sterling Selling and Fulfillment applications, by default, generate primary keys with one of the following formats:

  • YYYYMMDDHHmmSSSSSSqqqqqqqq (non sharded)
  • ccYYMMDDHHmmSSSSSSqqqqqqqq (sharded)

where

  • YYYY or YY is the four or two digit year,
  • cc represents a two digit schema id (for sharded)
  • MM is the two digit month,
  • DD is the two digit day of the month
  • HH is the two digit hour (24 hour)
  • mm is the two digit minute of the hour
  • SSSSSS is the seconds and milliseconds
  • qqqqqqqq is a sequence number

Although the key format is easy to use, the fact that the key values are only ever increasing can be a performance bottleneck. As key values are added to the primary key index, the database stores the value in the right sequential order. Since the key values are ever increasing, the index values will be added to the high end of the index, which is also commonly known as the right hand block. At very high transactional rates, the index block can become a bottleneck as transactions contend to insert values into the block. In a clustered database, the block must move between cluster members. Starting in 9.2, some tables are automatically randomized so that inserts do not land on the same right hand index block. This randomization is controlled by setting the KeyType attribute to "Random" in the entity.xml. The following entity.xml is an example:

<Entity
Description="This table stores status-quantity break up for 
  each line-schedule." EntityType="TRANSACTION" HasHistory="Y" 
  Module="omp" KeyType="Random" Name="Order_Release_Status" 
  Prefix="YFS_" TableName="YFS_ORDER_RELEASE_STATUS" 
  TableType="TRANSACTION" GenerateIndexedListMethods="true" 
  XMLName="OrderStatus">
<Attributes>
<Attribute ColumnName="ORDER_RELEASE_STATUS_KEY" 
      DataType="Key" DefaultValue="' ' "
      Description="The primary key for the 
        YFS_ORDER_RELEASE_STATUS table."
      Name="Order_Release_Status_Key" Nullable="false" 
      XMLName="OrderReleaseStatusKey"/>
,Attribute ColumnName="ORDER_RELEASE_KEY" 
      DataType="Key"
      Description="The order release that is associated with this 
        order release status record."
      Name="Order_Release_Key" Nullable="true" 
      XMLName="OrderReleaseKey"/>
…
</Attributes>
</Entity>

The randomized key format is

  • YYYYMMDDHHRRmmSSSSSSqqqqqqqq (non-sharded)
  • ccYYMMDDHHRRmmSSSSSSqqqqqqqq (sharded)

Currently, the primary keys for the following tables are by default randomized:

  • YFS_ORDER_RELEASE_STATUS
  • YFS_ORDER_LINE_SCHEDULE
  • YFS_INVENTORY_DEMAND
  • YFS_ORDER_AUDIT_DETAIL
  • YFS_ORDER_AUDIT_LEVEL
  • YFS_ORDER_DATE
  • YFS_ORDER_INVOICE_DETAIL
  • YFS_CREDIT_CARD_TRANSACTION
  • YFS_INVENTORY_DEMAND
  • YFS_INVENTORY_SUPPLY
  • YFS_ADDITIONAL_DATE
  • YFS_ORDER_INVOICE
  • YFS_PAYMENT
  • YFS_LINE_CHARGES
  • YFS_INVOICE_COLLECTION
  • YFS_INSTRUCTION_DETAIL
  • YFS_STATISTICS_DETAIL