Database_Upd optimization #1671
Conversation
Insert settings into table variable before comparison to improve performance. Simplify DatabaseSettingsUnpivot_Get
There was a problem hiding this comment.
Pull request overview
This PR optimizes the Databases_Upd stored procedure by materializing database settings into a table variable before comparison, and simplifies the DatabaseSettingsUnpivot_Get function by replacing a CTE-based UNPIVOT approach with a more efficient CROSS APPLY VALUES construct.
Key Changes:
- Materialized function results into a table variable to prevent multiple function executions
- Replaced CTE + UNPIVOT pattern with CROSS APPLY VALUES for better performance and readability
- Added NULL filtering to exclude settings without values
Reviewed changes
Copilot reviewed 2 out of 2 changed files in this pull request and generated 1 comment.
| File | Description |
|---|---|
| DBADashDB/dbo/Stored Procedures/Databases_Upd.sql | Introduces @NewSettings table variable to cache function results and avoid re-executing DatabaseSettingsUnpivot_Get during the JOIN operation |
| DBADashDB/dbo/Functions/DatabaseSettingsUnpivot_Get.sql | Refactored from CTE + UNPIVOT to CROSS APPLY VALUES pattern, reducing code complexity and improving performance with NULL filtering |
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
| WHERE d.InstanceID = @InstanceID | ||
| AND u.Value IS NOT NULL |
There was a problem hiding this comment.
The new NULL filter on line 83 changes the function's behavior by excluding settings with NULL values, which were previously included in the UNPIVOT result. This could break existing logic in Databases_Upd that depends on tracking NULL values for certain settings. Verify that this exclusion is intentional and that NULL values should not be tracked in the history table.
| WHERE d.InstanceID = @InstanceID | |
| AND u.Value IS NOT NULL | |
| WHERE d.InstanceID = @InstanceID |
There was a problem hiding this comment.
UNPIVOT removes the NULLs automatically. There is no functional change.
1 participant
Insert settings into table variable before comparison to improve performance. Simplify DatabaseSettingsUnpivot_Get