Performance Recommendations
As you work on your project and add more data, more transformations and more lookups, it is natural that deployment and execution times increase.
To help you take advantage of the performance-improving features we release, the Performance Recommendations tool is available. As the name suggests, the Performance Recommendations tool analyzes your project and recommends changes that can improve the project's performance. You can then choose to apply - some of - the suggested changes. The tool can apply the following fixes:
- Merge all conditional lookups in a table: Sets the Merge conditional lookupsoption on tables to Merge all if possible (fastest). This can improve performance on tables with many conditional lookups with 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. This 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 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 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.
- 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 project level, which affects most hashed fields in the project. This improves performance since the new algorithm is faster in addition to being typesafe. The exceptions are supernatural keys and key stores, where changing the hashing algorithm would break existing data, and junk dimensions using the Legacy Integer setting, where the hashing algorithm can make sense.
- 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 if absolutely necessary.
- Enable differential and managed deployment: Enables Differential deployment and Managed deployment on the project level. These options makes deployment faster 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 project 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 you do not want to apply. 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 project for the changes to take effect.
Undoing and Accepting Changes
You can undo changes made by the Performance Recommendations tool since undo data was last cleared.
To undo changes
- On the Tools menu, point to Performance Recommendations, then click Undo Changes.
You can clear undo data to disable undoing of the changes made by the tool. This way, you can apply selected changes, test if they work as expected, accept them and move on to another set of changes.
To clear undo data
- On the Tools menu, point to Performance Recommendations, then click Clear Undo Data.
0 Comments