I created this script in Powershell to read a DB record and create a hash of it salty and insert it back in a table, good script works fine the way I need, but what sticks is the delay 9 million record two days. I need if anyone has any command that could make it faster to things.

Who has little record and want to use this script are comfortable it works.

$user = "user"
$pwd = "pws"
$database = "bdd"
$SqlServer = "round"
$SqlLogs = "T_Powershll"
$Date = (Get-Date -format g)
$SqlConnection = New-Object system.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString ="Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"
$SqlConnection2 = New-Object system.Data.SqlClient.SqlConnection("Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;")
 
$seq=1
$fim=9561421
While ($seq -le $fim){
$SqlCommandText = "Select Cartao from T_Powershll_base where Id_seq= $seq "
$SqlConnection.Open()
$SqlConnection2.Open()
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$sqlcmd2 = $SqlConnection2.createcommand()
$Reader = $SqlCmd.ExecuteReader()
$SqlData = @( )
while ($Reader.Read())
{
 $SqlData += "" | Select-Object `
 @{ n = 'Cartao' ; e = { $Reader["Cartao"].ToString() } }
}
$Reader.Close()
$sha1 = New-Object System.Security.Cryptography.SHA1CryptoServiceProvider;
$sha1.Initialize();
$Salgado="710000hhgiiAAAhhhhooohzaqnpa
          epszx7w2soPmdfapa)gbbakkazwq
    "
$cart=$SqlData.Cartao
$cart2=[System.Text.Encoding]::ASCII.GetBytes($cart.PadRight(32,'_'));
$lim4=[System.Convert]::FromBase64String($Salgado.ToString());
$ab =$sha1.ComputeHash($cart2+$lim4);
$HASH="{0}" -f [system.BitConverter]::ToString($ab)-replace "-",""
$sqlcmd2.CommandText = "INSERT INTO $SQLLogs (Cartao, Cartao_Hash) VALUES ('$($SqlData.Cartao)', '$($HASH)')"
$Results = $SqlCmd2.ExecuteNonQuery()
$seq++
$SqlConnection.close()
$SqlConnection2.close()
}

TKS.

I am no expert in database programming with PowerShell, but why do you have two different database connections to the same database? The only thing that looks like it needs to change is the SqlCommand. Also, it looks like you are looping through 9 million records, one at a time and only grabbing one column, process that data and insert it, and then move on.

Why not grab all 9 million records, and then loop through those records one at a time? And like I said I am no expert, so I can be wrong on my logic.

Do you have any ideas for the SqlCommand?

tks.

Technically I did not answer your question, so you can unmark it as the answer for the time being. The sql commands, maybe something like this would do, I cannot test as I don't have SQL server to play with, but you can hopefully get an understanding of it.

$sqlConnection  = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Your connection string here"
$sqlConnection.Open()
$sqlCmd = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "SELECT QUERY TO RETRIEVE ALL 9 MILLION RECORDS"
$sqlReader = $sqlCommand.ExecuteReader()

$sqlCmd2 = $sqlConnection.CreateCommand()

While($reader.Read())
{
  # Do all of your processing here

  $sqlCmd2.CommandText = "INSERT INTO $SQLLogs (Cartao, Cartao_Hash) VALUES ('$($SqlData.Cartao)', '$($HASH)')"
  $results = $sqlCmd2.ExecuteNonQuery()
}

$reader.Close()
$sqlConnection.Close()

This is just a quick snippet, and most likely needs adjustments, but gives you a general idea. Definitely run this against a test database, so if things do not go well, you can just restore the database back, make adjustments and try again.  

clayman2 is correct, you can modify your code to use only a single connection without an issue.

I have a number of thoughts on this one. You'll have to try things out to see what actually gets you speed gains, though. It all depends on the nature of the data you're working with.

1. You can take your $SqlConnection.Open() and $SqlConnection.Close() calls outside of your while loop. That way you open your connection once, execute a bunch of commands, and then close it.

2. Another suggestion clayman2 had - performing a smaller number of operations against the SQL Server, doing the most work possible in each, will save you time. For example, could you just run this query:

SELECT Cartao FROM T_Powershll_base WHERE Id_seq BETWEEN 1 AND $fim

and loop through the results? That would eliminate 9561420 additional SELECT queries.

3. You're using a DataReader to retrieve the results line by line. There's a chance you might get better performance using a DataSet, which will handle retrieving results in batches on its own. Something like this will get you started.

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $SqlCmd
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null

for ($row in $dataSet.Tables[0].Rows) {
    # Your logic here. Use $row.FieldName to access data
}

4. The most efficient way to perform this task would be if you could create a stored procedure on the SQL Server itself and remove the network traffic and PowerShell processing from the equation. After a quick search, I found that there is the HASHBYTES function, which you can use to get the SHA1 hash of a string. You might consider digging into the Transact-SQL to see if you can achieve it that way.

I know that's a lot of info, but tuning the performance of these things is often a try-it-and-see process. Good luck!

Thanks for your help, I will re-testing and post here finding a solution.

Use BCP with a custom file.  It can insert millionsof records ina couple of minutes.

You can also use it from PowerShell.  Her eis an example:

# Copy-SQLBulk.ps1
# This show how to use BulkCopy in PowerShell by uploading a spreadsheet ot an MSSQLServer data table.

$filepath = 'C:\scripts\All.xlsx'
$excelConnection="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$filepath;Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"
$sqlConnection='Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=issue;Data Source=OMEGA\SQLEXPRESS;'
$excelQuery='select * from [SearchResult$]'
$tablename='SearchResult'

Try{
    $conn = New-Object System.Data.OleDb.OleDbConnection($excelConnection) 
    $conn.open()
    $cmd=$conn.CreateCommand()
    $cmd.CommandText=$excelQuery
    $rdr=$cmd.ExecuteReader()
    
    # create the BC object
    $sqlbc=[System.Data.SqlClient.SqlBulkCopy]$sqlConnection
    $sqlbc.DestinationTableName=$tableName
    
    # add all columns - you can add as few  as you like.
    for($i=0; $i -lt $rdr.FieldCount;$i++){
        $fname=$rdr.GetName($i)
        Write-Host $fname -ForegroundColor green
        [void]$sqlbc.ColumnMappings.Add($fname, $fname)
    }
    
    # write all of teh data to the table
    $sqlbc.WriteToServer($rdr)
}
Catch{
    Write-Host "$_" -ForegroundColor red
}

$sqlbc.Close()
$conn.Close()

The example uses Excel but any SQL connection can be substituted.   The example copies all column but you can just pick a single column.  The column can be transformed in the upload.

THe BCP utility will be easier for non-programmers. 

You can also define a trnsform in SSIS which will bemuch faster.

Row-by-row updates are very slow.

Here is how to load a whole table at high speed:

$instance=$env:COMPUTERNAME
$database='issue'
$userid='dbuser1'
$password='guessitnow'
$connString="User ID=$userid;Password=$password;Initial Catalog=$database;Data Source=$instance"
$SqlConnection=New-Object System.Data.SqlClient.SqlConnection $connString
$SqlConnection.Open()

$sqlCommand = $SqlConnection.CreateCommand()
$sqlCommand.CommandText="SELECT COUNT(*) FROM GPLINKS_OUT WHERE TRANS_TYPE='ACG'"

$adapter= New-Object System.Data.SqlClient.SqlDataAdapter $sqlCommand
$dataset= New-Object System.Data.DataSet

$rec=$adapter.Fill($dataset)
Write-Host "Records returned=$rec"  -Fore green
$SqlConnection.Close()

foreach($a in $dataset.tables[0]){
    $a.demo_task_id
}

THe issue you willhave is that millions of records will not likely fit in your memory.  A forward only reader like you are usingis fast but the updates are slow.  SQLBulCopy does the updates in batches at a very high speed with block write to SQLServer. It is blindingly fast.