CODEWITHSUNDEEP
performancepowershellfile-iostreamwritersqlcommand
Dylan Brams
Dylan Brams

Reputation: 2099

Powershell SqlCommand and StreamWriter slow write

I'm writing a PowerShell script that pulls arbitrary SQL queries from a database. Those queries can become extremely large, and after trying sqlcmd I ended up with a StreamWriter / SqlCommand combination. The problem I'm having is that execution is a little slow. Like 10x as slow. I've been told it should be faster, hence the question. To Code! 

                $conn.ChangeDatabase("MyDB")
                $mycmd = new-object System.Data.SqlClient.SqlCommand($Myquery, $conn) 
                $mycmd.CommandType = [System.Data.CommandType]::Text
                $mycmd.CommandTimeout = 300
                echo "Executing Reader."        
                $Results = $mycmd.ExecuteReader()


                echo "Opening file for writing."                    
                $sw2 = new-object system.IO.StreamWriter($sqlOutput, 1)
                echo "File Opened for Writing."     
                $delimiter = ","
                echo "Starting Row Reading"
                $Counter = $Results.FieldCount
                echo $Counter
                # Put in header row on first execution.
                $currtext = ""
                if ($Counter -gt 0)
                {
                    echo "Writing Header Row as:"
                    for ($i = 0; $i -lt $Counter; $i++)
                    {
                        $currtext = $currtext + $Results.GetName($i)
                        if ($i -lt $Counter - 1)
                        {$currtext = $currtext + $delimiter}
                    }

                    echo $currtext
                    $sw2.writeline($currtext)
                    $firstexecution = 0
                }
                else
                {
                    $sw2.writeline("No Data Found")
                }

                $rowcount = 0
                while ($Results.Read())
                {
                    $rowcount = $rowcount + 1
                    #echo "Reading Row"
                    $i = 0
                    $currtext = ""
                    for ($i = 0; $i -lt $Counter; $i++)
                    {
                        #echo "Processing Row"
                        $currtext = $currtext + """" + [string]$Results[$i] + """" 
                        if ($i -lt $Counter - 1)
                            { $currtext = $currtext + $delimiter }
                    }
                    #echo "Writing Line."
                    #echo $currtext
                    $sw2.writeline($currtext)
                }
                echo "Total Rowcount:" + $rowcount
                $sw2.flush()
                $sw2.close()
                $sw2.dispose()
                $Results.close()

Anyone know what I'm doing wrong / how to fix it?

Upvotes: 1

Views: 1786

Answers (1)

tkrn
tkrn

Reputation: 606

Add the Snapins:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Call your variables:

$SQLUsername        = "user"
$SQLPassword        = "password"
$SQLDatabase        = "database"
$SQLServer          = "localhost"

Grab your data:

$DBComputers = Invoke-Sqlcmd -ServerInstance $SQLServer -Username $SQLUsername -Password $SQLPassword `
 -Database $SQLDatabase -Query "SELECT * FROM Physical WHERE Retired=0 AND OS LIKE '%Windows%' ORDER BY Name"

Iteriate through the rows:

foreach ( $DBComputer in $DBComputers ) {

    Write-Host $DBComputer.Name    ##$DBComputer.COLUMNNAME

}

I've done alot of SQL with PowerShell by using the following example above. The example above queries data on a SQL Standard instance on a Dell R720 server with pretty darn good speed. Is this a Microsoft Access Database or a true SQL instance? The Access Engine is not ideal plus a lot would matter based on your disk type.

You mentioned a large dataset, I think the biggest thing is to make sure the database is indexed and running a real SQL engine without knowing more about the database from a non-code perspective.

Upvotes: 2

Related Questions