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

Post a Comment