1
\$\begingroup\$

I have this script that pulls text out of .docx, .doc and .pdf files and uploads that text to an Azure SQL Server. This is so users can search on the contents of those documents without using Windows Search / Azure Search.

The filenames are all in the following format:

firstname surname - id.extension

The id is incorrect though, the ID is from an outdated database and the new database that I am updating holds both (newID and oldID).

COLUMNS:

  • ID - New ID of the candidate record
  • OldID - Old ID of the candidate record (old database schema)
  • OriginalResumeID - Document link ID for the candidate table to the document table
  • CachedText - The field I am updating (holds the document text) at the moment this will mostly be NULL

Here is the script:

## Get resume list
$params = @{
    'Database' = $TRIS5DATABASENAME
    'ServerInstance' = $($AzureServerInstance.FullyQualifiedDomainName)
    'Username' = $AdminLogin
    'Password' = $InsecurePassword
    'query'    = "SELECT id, OldID, OriginalResumeID FROM Candidate WHERE OriginalResumeID IS NOT NULL"
}
$IDCheck = Invoke-Sqlcmd @params

## Word object
$files = Get-ChildItem -force -recurse $documentFolder -include *.doc, *.pdf, *.docx
$word = New-Object -ComObject word.application
$word.Visible = $false
$saveFormat = [Enum]::Parse([Microsoft.Office.Interop.Word.WdSaveFormat], "wdFormatText")

foreach ($file in $files) {
    Write-Output "Processing: $($file.FullName)"
    $doc = $word.Documents.Open($file.FullName)
    $fileName = $file.BaseName + '.txt'
    $doc.SaveAs("$env:TEMP\$fileName", [ref]$saveFormat)
Write-Output "File saved as $env:TEMP\$fileName"
    $doc.Close()

    $4ID = $fileName.split('-')[1].replace(' ', '').replace(".txt", "")
    $text = Get-Content "$env:TEMP\$fileName"
    $text = $text.replace("'", "''")

    $resumeID = $IDCheck | where {$_.OldID -eq $4id} | Select-Object OriginalResumeID
    $resumeID = $resumeID.OriginalResumeID

    <# Upload to azure #>
    $params = @{
        'Database' = $TRIS5DATABASENAME
        'ServerInstance' = $($AzureServerInstance.FullyQualifiedDomainName)
        'Username' = $AdminLogin
        'Password' = $InsecurePassword
        'query'    = "Update Document SET CachedText = '$text' WHERE id = $ResumeID"
    }
    Invoke-Sqlcmd @params -ErrorAction "SilentlyContinue"
    Remove-Item -Force "$env:TEMP\$fileName"
}

$word.Quit()

The problem is that running this on a large dataset, let's say 750000 documents takes far too long per document. I'm fairly certain that this is because it has to search through the entire $IDCheck object of 750000 records before it can get the originalResumeID of the record to upload to.

Running this on a smaller database is quite quick (around 200000 per 24 hours). I was thinking I could check the documents table and only pull rows where the CachedText field is null and loop that to run every 50000 documents so it would get quicker as it goes. Problem is the documents table will be massive and will take a long time to search through every time this is called.

Any help on speeding this up would be much appreciated.

EDIT:

Looks like it is the upload to azure causing the delay:

<# Upload to azure #>
$params = @{
    'Database' = $TRIS5DATABASENAME
    'ServerInstance' = $($AzureServerInstance.FullyQualifiedDomainName)
    'Username' = $AdminLogin
    'Password' = $InsecurePassword
    'query'    = "Update Document SET CachedText = '$text' WHERE id = $ResumeID"
}
Invoke-Sqlcmd @params -ErrorAction "SilentlyContinue"
\$\endgroup\$
  • \$\begingroup\$ Your Get-Content is super slow too. Use -raw parameter to get the text as a string. \$\endgroup\$ – wOxxOm Sep 29 '18 at 7:02
  • \$\begingroup\$ Thanks for the response, can you explain why this would be faster? I did a test by creating 10000 text files in a folder and running both Get-Content and Get-Content -raw on all files, it took 7 seconds on both runs. \$\endgroup\$ – Owain Esau Oct 1 '18 at 1:21
  • \$\begingroup\$ Those were smallish files I guess. Get-Files without -raw creates an array of lines so with really large files the difference could be insane. This is a known peculiarity. \$\endgroup\$ – wOxxOm Oct 1 '18 at 4:23
  • \$\begingroup\$ Thanks, this made a small difference, the files aren't very large. They are resumes so 1 - 2 page documents. \$\endgroup\$ – Owain Esau Oct 2 '18 at 0:07
2
\$\begingroup\$

I would try to use bulkcopy to load all of the IDs and their CachedText at once into a staging table in Azure, and then do a single update on your document table.

 CREATE TABLE document
(docKey     BIGINT IDENTITY(1, 1) PRIMARY KEY, 
 CachedText NVARCHAR(MAX), 
 id         INT
);
CREATE TABLE document_stage
(CachedText NVARCHAR(MAX), 
 id         INT
);

As you iterate over the files, you create a PSObject with the properties you want in your sql table and add it to an collection. Then after all files are done, or at set batching limits you can use Out-DataTable to convert the collection into a data table, and then let SqlBulkCopy upload to the stage table in one batch, and a single UPDATE will update your primary table.

UPDATE Document
  SET 
      CachedText = stg.CachedText
FROM document_stage stg
WHERE document.id = stg.id;

PS script

$files = Get-ChildItem -force -recurse $documentFolder -include *.doc, *.pdf, *.docx

$stagedDataAsArray = @()
foreach ($file in $files) {

    $fileName = $file.name
    $4ID = $fileName.split('-')[1].replace(' ', '').replace(".txt", "")
    $text = Get-Content "$($file.FullName)"
    $text = $text.replace("'", "''")


    $resumeID = $IDCheck | where {$_.OldID -eq $4id} | Select-Object OriginalResumeID
    $resumeID = $resumeID.OriginalResumeID

    <# create the row and add it to our  #>
     $fileInstance = New-Object -TypeName psobject 
    $fileInstance | add-member -type NoteProperty -Name cachedText -Value $text
    $fileInstance | add-member -type NoteProperty -Name resumeID -Value $resumeID
    $stagedDataAsArray += $fileInstance
    Remove-Item -Force "$env:TEMP\$fileName"

    }
    $stagedDataAsTable = $stagedDataAsArray | Out-DataTable
    $cn = new-object System.Data.SqlClient.SqlConnection("YOUR AZURE DB CONNECTION STRING");
    $cn.Open()
    $bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
    $bc.DestinationTableName = "dbo.document_stage"
    $bc.WriteToServer($stagedDataAsTable)
    $cn.Close()


    $params = @{
        'Database' = $TRIS5DATABASENAME
        'ServerInstance' = $($AzureServerInstance.FullyQualifiedDomainName)
        'Username' = $AdminLogin
        'Password' = $InsecurePassword
        'query'    = "UPDATE Document
  SET 
      CachedText = stg.CachedText
FROM document_stage stg
WHERE document.id = stg.id;"
    }
    Invoke-Sqlcmd @params -ErrorAction "SilentlyContinue"
\$\endgroup\$
  • \$\begingroup\$ Thanks for the reply, i changed tacts and did this in Python in Linux using SED, unzip and PdfToText which is far faster. Im hosting the linux box in a high powered Azure VM and getting the files via blob storage and its running close to 100 times faster now. Ill give you the accepted answer since i like this approach. \$\endgroup\$ – Owain Esau Jan 29 at 2:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.