The Performance Recommendations tool can be used after an existing project has been upgraded from a previous version to a newer version of TimeXtender. The tool will analyze your existing instances and recommend changes that can improve performance.
The Performance Recommendations tool can provide insights by analyzing instances and making recommendations on how to optimize performance. Based on the recommendations provided, the Performance Recommendations tool can apply the following fixes:
- Merge all conditional lookups in a table: Sets the Merge conditional lookups option on tables to Merge all if possible (fastest), which can improve performance on tables with a significant number of conditional lookups of 20% or more.
- Use lookup operator None with joins other than Equal: Changes Operator option on lookup fields to None if the join on the lookup field does not use the Equal operator, which can improve performance since the other operators on lookup fields - average, maximum and minimum - are very slow when the join on the lookup field is not using equals.
- Align lookup operators: Changes the Operator option from Top or None to Maximum on lookup fields on the same table with the same joins. If all operators are either average, count, maximum, minimum or sum, then the SQL statement can be merged, which improves performance.
- Stop using deprecated lookup options: Changes deprecated lookup options to the new defaults:
- On conditional lookup fields, Force sub select is disabled and Multiple lookup fields is set to Take the first value.
- On lookup fields, SQL mode is set to Group by.
-
This improves performance because it makes it possible to merge SQL statements.
- Stop using deprecated and legacy hashing algorithms: Changes the hashing algorithm option from Legacy Binary, Legacy Plain Text or Plain Text settings to SHA-1 SQL Server 2005+ on the Instance level, which affects most hashed fields in the project. This improves performance because the new algorithm is faster in addition to being typesafe. The exceptions to this:
- Supernatural keys and key stores, where changing the hashing algorithm would break existing data.
- Junk dimensions using the Legacy Integer setting, where the legacy hashing algorithm is appropriate.
- Disable other table troubleshooting options: Disables the Disable SCHEMABINDING and Use legacy transformations options on the Troubleshooting tab on tables. These options decrease performance if enabled and should only be enabled when absolutely necessary.
- Enable differential and managed deployment: Enables Differential deployment and Managed deployment on the Instance level, which provide for a faster deployment by only deploying the objects that need to be deployed and in the right order.
- Verify automatic indexes: Verifies that automatically generated indexes are optimal and corrects them if necessary. This cannot be undone with the 'Undo changes' functionality.
Finding and Applying Performance Recommendations
To analyze your Instance to get performance recommendations and apply them, follow the steps below.
- On the Tools menu, point to Performance Recommendations and click Find.... The Performance Recommendations window appears.
- Review the recommendations in the Apply these changes list and clear the check box next to recommendations that should not be applied. Mouse over objects in the Affected objects to see what issue has been identified for the object and the proposed change. Then click OK to apply the changes.
- Deploy the Instance for the changes to take effect.
Undoing and Accepting Changes
The Performance Recommendations tool allows for changes to be undone provided that the most recent undo data has not been cleared.
To undo changes, on the Tools menu, point to Performance Recommendations, then click Undo Changes.
If there is a need to disable the undo changes functionality, then clearing the undo data may accomplish this.
To clear the undo data, on the Tools menu, point to Performance Recommendations, then click Clear Undo Data.
Use the following steps to perform the testing and acceptance of changes:
- Apply a selected set of changes.
- Perform quality control testing to confirm these changes are working as expected.
- After passing validation, accept these changes.
- Continue steps 1 through 3 for the next set of changes.
