Notifications

88 views

Best Practice of Column/Field* Creation

1) On a subproduction instance (subprod A), from the Dictionary, in an update set, a field is created.

  • Columns should be created on subprods only. And on tables that have a small number number of records or are empty.
  • Reasons are in section "Transaction Timeout during field creation?"

2) The update set might be committed/moved onto a different instance for further testing (subprod B).

3) Eventually, the update set is committed/moved to production (prod C).

* Column and field can be interchangeable. In the database, the word "column" is usually used. On the instance, "field" is typically used.

 

Questions

How long will it take?

  • This will depend on how many records the table contains. The larger the table, the longer it will take.
    • There is no definite duration we can give. We have seen a column creation on a table that has 10 million records take around 1.5 hours.
  • The duration comes from the constraints of the database and unfortunately we cannot do much about it.
  • Note that the [task] table is very likely flattened meaning that other tables such as change_request, problem, sc_req_item, etc are all a part of task (these tables extend from task).
    • When creating fields on any table which extends [task] table, the entire [task] MAY have to be altered depending on various factors. 
    • Example: I am creating a field on [change_request] table and that table only has 20k records so I expect it to be pretty fast. However, the entire [task] will have to be altered. In this scenario, task has 8 mil records and so the table alter will take awhile. 

 

Transaction Timeout during field creation?

  • When creating columns through the Dictionary, that transaction is subject to Quota Rules, specifically the out-of-box one named: UI Transactions
  • Since the creation will take awhile, the out-of-box UI Transaction Quote Rule will terminate any transaction longer than 298 seconds (~5 mins).
  • If you are creating a column on any instance, the termination of the column creation often cause improper behavior of the column
    • Some examples of improper behavior: the field cannot be added to a list view/form, data cannot be stored in the field, the name of the column is "label"
  • Two workarounds to this:
    1. Truncate/clean up the table. Since on subprod instances, millions of records are generally not needed, truncating the table will speed up the process.
    2. Disable the UI Transaction Quote Rule. We DO NOT recommend doing this on production. Columns should only be created on subprod instances.
    3. As per the workaround in KB0695149, you can create a column from the Form/Layout Editor, and this will run the in the background and not time out.
  • Update sets ARE NOT subject to any Quota Rules as they run in the background.  

 

Why capture field creations in Update Set?

  • Creation of fields should only be done on subprod instances.
  • They should also be captured in update sets.
  • Field/column creations applied via update sets will not time out as they are not subject to Quota Rules. They run in the background.

 

What does the table alter process look like?

Online alter operations are completed in the following steps: 

  1. An empty table is created with the same schema as the table that is to be altered.
  2. The ALTER operation is performed on the new, empty table.
  3. Triggers are added on the live table to copy any changes that are made against the live table to the new table.
  4. All data is copied in chunks, from the live table to the new table.
  5. Once all of the data is copied, the tables are swapped. This requires a very brief table lock.
  6. The old live table is dropped.

This method will only briefly lock the table, and should not cause any noticeable issues on the instance for end users. 

 

Creating a field on a table larger than 50mil records?

 

Article Information

Last Updated:2019-01-11 10:49:28
Published:2019-01-11