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:

[VBSCRIPT]
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
        rs.MoveNext
    Loop
Else
    wscript.echo "error: no records found"
End If
 
rs.Close
conn.Close
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing
 
wscript.echo Timer-t1 & " seconds"
[/VBSCRIPT]

[PS_ADO]
$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
 
$adoConnection.Open($sqlConnectionString)
$adoRecordset.Open($query, $adoConnection, $adoOpenStatic, $adoLockOptimistic)
$adoRecordset.MoveFirst()
$rows = $adoRecordset.RecordCount
    
do {
    write-host $adoRecordset.Fields.Item("ProductName").Value
    $adoRecordset.MoveNext()
} until ($adoRecordset.EOF -eq $TRUE)

$adoRecordset.Close()
$adoConnection.Close()

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

[PS_ADONET]
$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
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
 
#output the data
$DataSet.Tables[0]
 
$runtime = New-TimeSpan $t1 $(Get-Date)
write-host "Runtime: "$runtime.Seconds" seconds"
[/PS_ADONET]

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