Performance Tips

This tool provides recommendations for optimizing your database performance.

Below are some typical recommendations you might encounter and the steps you can take to enhance your database’s performance.

AutoNumber Column Calculated in About NNN Seconds

This recommendation applies to the AutoNumber column.

When multiple records are created concurrently in a table, the system must wait for the completion of record creation to assign the next unique AutoNumber value. This issue commonly occurs when many records are created simultaneously via the REST API.

Solution

Change the Format property of the AutoNumber column to {GUID}. This change eliminates the need to sequentially assign numbers, preventing the AutoNumber column from being blocked during concurrent record creation.

Please Consider Replacing With Built-In Functionality

This recommendation applies to Call-URL actions that use the REST API to call the same database.

Why This is Recommended

• All TeamDesk operations are executed under a transaction. A Call-URL action that calls the same database won’t see changes made by previous actions in the same transaction and might be blocked by the current active transaction.
• These self-referential API calls create unnecessary load on your database.

Solution

Replace the REST API call with built-in functionality, such as:

• Create Record
• Update Record
• Delete Record
• Generate Document
• Use a RecordSet column where applicable.

Summary Column Used in Match Condition

This message appears when a Summary column is used in a relation match condition.

Why This is Recommended

Using a summary column in a match condition prevents the system from utilizing low-level indexes, forcing it to perform a full table scan to match records. This can significantly impact database performance, especially when working with large tables containing many records.

Solution

To improve performance, consider the following approaches:

1. Avoid Using Summary Columns in Match Conditions
   Rethink the database design to eliminate the need for a summary column in the match condition.

2. Cache the Summary Column Value

   • Create a physical column with a Default/Calculate formula set to the value of the summary column.
   • Use this physical column in the match condition instead of the summary column.

   Important Notes:

   • Physical columns will not automatically recalculate if the summary column value changes.
   • Implement logic to handle recalculation of the physical column when the summary column value changes, such as using triggers or workflow actions.

Formula Column Used in Match Condition

This message appears when a Formula column is used in a relation match condition.

Why This is Recommended

Using a formula column in a match condition prevents the system from leveraging low-level indexes, forcing it to perform a full table scan to match records. This can severely impact database performance, especially when dealing with large tables containing many records.

Solution

To enhance performance, consider the following approaches:

1. Change to a Physical Column with a Default/Calculate Formula

   • Replace the formula column with a physical column and set its Default/Calculate formula to mimic the logic of the formula column.
   • If the formula column depends on summary columns, note that the physical column will not automatically recalculate when the summary column value changes.

2. Handle Recalculation

   • Implement logic to ensure the physical column is updated when the summary column value changes.
   • Use triggers, workflow actions, or other methods to keep the physical column synchronized with the summary column.

Consider Using the "Reference Column Name" Column Instead

This message appears when you have a lookup column pointing to the key column value of the referred table.

Why This is Recommended

The reference column already contains the key column value from the referred table. Creating an additional lookup column for the same value is redundant and adds unnecessary overhead by extracting information that is already available.

Solution

• Use the reference column directly instead of the lookup column to access the referred table’s key column value.
• Delete the redundant lookup column to simplify the structure and improve database efficiency.

NNN Invalid Reference(s) Found

This is not a performance issue but a potential data integrity warning. It indicates that some values in the reference column are pointing to non-selectable records in the referred table. These records may have been deleted, or current settings may prevent access to them.

Solution

1. Identify the Invalid References

   • Copy the provided filter formula into a view’s custom filter to isolate and review these records.

2. Fix the Invalid References

   • If the referred records were deleted:
     • Remove or update the invalid references.
   • If the issue is caused by current settings (e.g., access restrictions or filter conditions):
     • Adjust the settings if necessary to make the records accessible, or update the reference values to valid records.

Following these recommendations will help improve the speed, reliability, and overall performance of your database.