I ran into performance issues with Invoke-DbaQuery\Write-DbaDbDataTable as well and found that the default output type (DataRow) is the slowest in performance.
Try adding "-as DataTable" to your Invoke-DbaQuery.
Using your code in my environment, I get 8sec with DataRow and 0.5sec using DataTable/DataSet
Copy-DbaDbTableData is still coming out fastest :)
I ran into performance issues with Invoke-DbaQuery\Write-DbaDbDataTable as well and found that the default output type (DataRow) is the slowest in performance.
Try adding "-as DataTable" to your Invoke-DbaQuery.
Using your code in my environment, I get 8sec with DataRow and 0.5sec using DataTable/DataSet
Copy-DbaDbTableData is still coming out fastest :)