Wednesday, August 17, 2011

Semi-Showdown: VBscript, PowerShell / ADO, ADO.NET

Ok, so I got REALLY REALLY REALLY bored one day and had to settle a nagging question in my head (I have another nagging question involving the medical/scientific analysis of whether the metabolic rate change incurred by the caffeine in a cup of strong coffee burns off enough calories to offset a tablespoon of sugar in the coffee itself, but that's for another day). 

I posted an article on this subject a long time ago, but without any analysis, just a question about why 99.9 percent of all the PowerShell "database" examples on the Internet use ADO (via COM InterOp) rather than pure/native ADO.NET.  Since then however, the ratio of PowerShell+ADO.NET examples has grown significantly, which is a good thing.  Nothing like trying to impress a consumer with a spiffy new sports car than by showing you how well it can sit idle in a traffic jam.

The Goal:

  • Measure the performance variations between VBscript+ADO, PowerShell+ADO, and PowerShell+ADO.NET.

The Setup:

  • Query a remote SQL Server database table, for one column only
  • Query: Select one column, of type VARCHAR(255), from approximately 5,300 rows
  • The column being queried is indexed
  • The table contains four columns, of types: INT, SMALLDATETIME, VARCHAR(255) and VARCHAR(50)

The Server:

  • Hyper-V 2008 R2 SP1 guest:
    • Windows Server 2008 SP2
    • 20 GB RAM
    • 4 CPUs
    • SQL Server 2008 R2
  • Client:
    • HP 7900 Desktop
    • Windows 7 SP1, 64-bit
    • 12 GB RAM
    • Dual Core CPU

The Code:

query = "SELECT ProductName FROM SoftwareExclusion ORDER BY ProductName"
Set conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")
conn.Open sqlConnectionString
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = query
rs.Open cmd
If Not(rs.BOF And rs.EOF) Then
Do Until rs.EOF
wscript.echo rs.Fields("ProductName").Value
wscript.echo "error: no records found"
End If

Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing

wscript.echo Timer-t1 & " seconds"

$t1 = Get-Date

$query = "SELECT ProductName FROM SoftwareExclusion ORDER BY ProductName"

$adoOpenStatic = 3
$adoLockOptimistic = 3

$adoConnection = New-Object -ComObject ADODB.Connection
$adoRecordset = New-Object -ComObject ADODB.Recordset

$adoRecordset.Open($query, $adoConnection, $adoOpenStatic, $adoLockOptimistic)
$rows = $adoRecordset.RecordCount

do {
write-host $adoRecordset.Fields.Item("ProductName").Value
} until ($adoRecordset.EOF -eq $TRUE)


$runtime = New-TimeSpan $t1 $(Get-Date)
write-host "Runtime: "$runtime.Seconds" seconds"

$t1 = Get-Date

$SqlQuery = "SELECT ProductName FROM SoftwareExclusion ORDER BY ProductName"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $SqlConnectionString

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet

#output the data

$runtime = New-TimeSpan $t1 $(Get-Date)
write-host "Runtime: "$runtime.Seconds" seconds"

The Results:

  • 10 successive execution runs each

  • Average Run Times:

    • Test 1 = 1.6 seconds

    • Test 2 = 22.21 seconds

    • Test 3 = 3.9 seconds


tumtum73 said...

VBScript wins? WTF!!!!! I would have NEVER guessed that.

skatterbrainz said...

Yes indeed. I was as surprised as you. I figured the COM Interop test would be slower, but not by 22x. The PS+ADO.NET test was only slightly slower, but I'm sure that an argument could be made that what it loses in performance, it makes up for with extended capability via .NET (LINQ,etc.)