Use ClearAndInitialize to improve performance

[andreasjordan]

Summarize Functionality

We implemented ClearAndInitialize to improve performance in Get-DbaDbView in #8042.

Background info (https://github.com/sqlcollaborative/dbatools/pull/8042#issuecomment-1003418389):

Write up from SMO team for those interested:

https://github.com/microsoft/sqlmanagementobjects/wiki/Refresh-vs-ClearAndInitialize

ClearAndInitialize offers a parameterized explicit initialization combined with a complete reset of the collection contents. Unlike Refresh, prior object references from the collection are not preserved. For example, if your application had a variable named col that was a reference to a Column object from table.Columns, after table.Columns.Refresh() your reference would be the same as that returned by table.Columns[col.Name]. After ClearAndInitialize the indexer would return a different object. All objects in the collection will have the same set of populated properties after the call, using whatever list had been provided to SetDefaultInitFields plus those provided to the extraFields parameter. It's common for applications using this API to skip calling SetDefaultInitFields and just pass the property names to this function.

Let's use this issue to plan implementing ClearAndInitialize in other Get- commands and for general discussion on this topic.

Is there a command that is similiar or close to what you are looking for?

No

Technical Details

No response

[andreasjordan]

Let me share a small snippet from a code I use when rebuilding all indexes to build page checksums. To be able to have a nice progress bar, I work with DataSpaceUsed and IndexSpaceUsed. To improve performance I get these properties for all tables at once with ClearAndInitialize:

$db = Get-DbaDatabase -SqlInstance $instance -SqlCredential $credential -Database $database
$db.Tables.ClearAndInitialize('', [string[]]('Schema', 'Name', 'HasClusteredIndex', 'DataSpaceUsed', 'IndexSpaceUsed'))

$countTarget = 0
$spaceUsedTarget = 0
foreach ($table in $db.Tables) {
    $countTarget++
    $spaceUsedTarget += $table.DataSpaceUsed + $table.IndexSpaceUsed
}