Enterprise Admins.org

I was recently reviewing a Powershell script that had slow performance. The script used Get-MailboxFolderStatistics to pull information from an Exchange Server environment and insert (or update) a record in a SQL database based off the email address. The code was pretty simple…it ran a loop like the following for each user:

$count = "SELECT count(*) from MailboxFolderStats where email=$_"

IF ($count -eq 0) { 
#SQL INSERT CODE
} else { 
#SQL UPDATE CODE
}

Note: This isn’t actual powershell, it is just an outline of the logic used by the script.

Each iteration of the above block would not take long to run, but the loop was executing that block about 40,000 times. After a bit of searching, I came across this Stackoverflow.com article on using a insert update stored procedure. The article describes this technique as an optimistic insert or upsert/merge. Here is how it works…starting with the simplified powershell code:

#SQL EXEC CODE

Note: This isn’t actual powershell, it is just an outline of the logic used by the script.

In SQL, you create a stored procedure that handles the logic (which was previously handled by the client powershell script)

update MailboxFolderStats set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into MailboxFolderStats (Col1, Col2) values (@col1, @col2)

The interesting part of this change is that we no longer select the count of rows from SQL. Instead, SQL attempts to update a record without checking to see if it exists first. Silly, no? Actually, this is what helps with performance most. The majority of the time when this stored procedure executes, a record will exist that needs updated. After the update, SQL checks to make sure something updated — and if not will finish up by adding the entry.

The previous code required (approximately)
– 40,000 select statements
– 39,500 update statements
– 500 insert statements

The modified code will perform (approximately)
– 40,000 update statements
– 500 insert statements

That’s 39,500 fewer connections to the remote SQL server! This reduces network I/O from the client and load on the SQL server at the same time. This single change saved about 8 minutes on the execution time of the script.

My favorite part of this updated code? It shows that performance can be gained in a variety of ways. My script was written in powershell, but the performance gain was provided by more efficient use of SQL server. When troubleshooting performance problems in code there are usually a variety of solutions — some of them require more code and some of them require less. In this example, I have less code (even when you combine the powershell code with the stored procedure code) and better performance. I have another example from this same code review (it is the exact lines of code, just in a different order) but I’ll save that for a future post.

Recently I was given a task at work…someone pointed at a haystack and asked me to find a needle. Actually, the task involved reading the body of hundreds of email messages looking for telephone numbers. Regular expressions and powershell make this pretty easy:

([regex]"[2-9]\d{2}-\d{3}-\d{4}").Match("Body with phone number like 345-555-6789 in it.") | %{ $_.Value }

After completing this task, I figured it would be fairly easy to turn this into a function with predefined regular expressions that might be common. Here is that function.

Function Get-RegexMatches{
      param(
            [parameter(Mandatory=$true, ValueFromPipeline=$true)][alias("input")][String]$inputText,
            [parameter(Mandatory=$false)][alias("email")][switch]$mail,
            [parameter(Mandatory=$false)][alias("telephoneNumber")][switch]$phone,
            [parameter(Mandatory=$false)][alias("zipCode")][switch]$zip,
            [parameter(Mandatory=$false)][alias("SocialSecurityNumber")][switch]$ssn,
            [parameter(Mandatory=$false)][alias("ip")][switch]$ipv4,
            [parameter(Mandatory=$false)][switch]$ipv6,
            [parameter(Mandatory=$false)][alias("HostName")][alias("dnsHostName")][switch]$dns,
            [parameter(Mandatory=$false)][alias("regex")][string]$pattern
      )

      process {
            if ($mail) { $regexTag = [regex]"[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"  }
            if ($phone) { $regexTag = [regex]"[2-9]\d{2}-\d{3}-\d{4}([\ ][x]\d{1,5})?" }
            if ($zip) { $regexTag = [regex]"\d{5}([\-]\d{4})?"} 
            if ($ssn) { $regexTag = [regex]"((?!000)(?!666)(?:[0-6]\d{2}|7[0-2][0-9]|73[0-3]|7[5-6][0-9]|77[0-2]))-((?!00)\d{2})-((?!0000)\d{4})" }
			if ($ipv4) { $regexTag = [regex]"0*([1-9]?\d|1\d\d|2[0-4]\d|25[0-5])\.0*([1-9]?\d|1\d\d|2[0-4]\d|25[0-5])\.0*([1-9]?\d|1\d\d|2[0-4]\d|25[0-5])\.0*([1-9]?\d|1\d\d|2[0-4]\d|25[0-5])" }
			if ($ipv6) { $regexTag = [regex]"\s*((([0-9A-Fa-f]{1,4}:){7}([0-9A-Fa-f]{1,4}|:))|(([0-9A-Fa-f]{1,4}:){6}(:[0-9A-Fa-f]{1,4}|((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3})|:))|(([0-9A-Fa-f]{1,4}:){5}(((:[0-9A-Fa-f]{1,4}){1,2})|:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3})|:))|(([0-9A-Fa-f]{1,4}:){4}(((:[0-9A-Fa-f]{1,4}){1,3})|((:[0-9A-Fa-f]{1,4})?:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){3}(((:[0-9A-Fa-f]{1,4}){1,4})|((:[0-9A-Fa-f]{1,4}){0,2}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){2}(((:[0-9A-Fa-f]{1,4}){1,5})|((:[0-9A-Fa-f]{1,4}){0,3}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){1}(((:[0-9A-Fa-f]{1,4}){1,6})|((:[0-9A-Fa-f]{1,4}){0,4}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(:(((:[0-9A-Fa-f]{1,4}){1,7})|((:[0-9A-Fa-f]{1,4}){0,5}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:)))(%.+)?\s*" }
			if ($dns) { $regexTag = [regex]"([\d\w-.]+?\.(a[cdefgilmnoqrstuwz]|b[abdefghijmnorstvwyz]|c[acdfghiklmnoruvxyz]|d[ejkmnoz]|e[ceghrst]|f[ijkmnor]|g[abdefghilmnpqrstuwy]|h[kmnrtu]|i[delmnoqrst]|j[emop]|k[eghimnprwyz]|l[abcikrstuvy]|m[acdghklmnopqrstuvwxyz]|n[acefgilopruz]|om|p[aefghklmnrstwy]|qa|r[eouw]|s[abcdeghijklmnortuvyz]|t[cdfghjkmnoprtvwz]|u[augkmsyz]|v[aceginu]|w[fs]|y[etu]|z[amw]|aero|arpa|biz|com|coop|edu|info|int|gov|mil|museum|name|net|org|pro)(\b|\W(?

"my text with emailuser@somedomain.com" | Get-RegexMatches -mail
"my text with emailuser@somedomain.com" | Get-RegexMatches -host