0

What is the most performant way of evaluating two separate powershell objects populated with data from Invoke-SqlCmd to see if the result sets are the same and to highlight any differences assuming the result sets are not ordered?

Would it be best to take my $Query1 and convert each system.data.datarow into a concatenated string and then do a Contains against every system.data.datarow of $Query2? I am not so much concerned about what in each data row is different, but just the fact that a data row in $result1 does not match up with a data row in $result2. Would it be better to use a hashtable to accomplish this?

$result1 = Invoke-DbaQuery -SqlInstance mySqlInstance -database TESTDB -Query "SELECT TOP 2 * from myTable ORDER BY EmployeeID DESC"

$result2 = Invoke-DbaQuery -SqlInstance mySqlInstance -database TESTDB -Query "SELECT TOP 2 * from myTable ORDER BY EmployeeID ASC"

foreach($row in $result1) { 
$rowString1 = Out-String -InputObject $row #| out-gridview

    foreach($row2 in $result2) {

        $rowString2 = Out-String -InputObject $row2 #| out-gridview
        if($rowString1.String -eq $rowString2.String)
        {
        write-host 'there is a match!'
        break;
        }
        else
        {
        write-host 'there is not a match.  Keep Searching'
        }
    }
}

Edit: string search is too slow

Data Dill
  • 353
  • 4
  • 14
  • `Compare-Object` – Abraham Zinala May 27 '21 at 15:15
  • I am aware of Compare-Object, but that does solve the problem. – Data Dill May 27 '21 at 15:18
  • You haven't provided much details or how your tables look like. What did you try? – Santiago Squarzon May 27 '21 at 15:27
  • Added code for what I am currently trying. Converting each system.data.datarow into a string and then doing a string search. Basically trying to mimic doing a left join on my source table in SQL on all columns to see where NULLs are returned.. same idea. – Data Dill May 27 '21 at 15:44
  • Warren Frame created a function that works like `JOIN`s in PowerShell called `Join-Object`. Have you checked out http://ramblingcookiemonster.github.io/Join-Object/ ? – Shaneis May 27 '21 at 15:48
  • Okay so I am playing around with that function and it seems to be exactly what I need, but I can't figure out how to pass multiple "joinProperties". The only way this will work is if I join on every single column as a PK may not be present. – Data Dill May 27 '21 at 16:13
  • Unfortunately that function does not allow for multiple join properties and the MSFT one that Warren based his off of does allow multiple join properties, but does not account for duplicate column names. I think a string compare is going to be my best best. – Data Dill May 27 '21 at 17:22
  • Using this [`Join-Object`](https://www.powershellgallery.com/packages/Join) cmdlet (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `$result1 |Join $result2 -on String`. It is probably slower than the `linq` version (note that you really need to understand the performance results of a deferred execution to determine that) but it is faster than `Compare-Object`, has an easier syntax and supports more features along with multiple join properties and merging duplicate column names. – iRon May 28 '21 at 13:05

0 Answers0