The Three Scripting Amigos? Uh oh! One Has a Wooden Leg?

Out of curiosity (which almost always gets me into trouble) I decided to test a fairly standard task in each of three script languages to assess performance differences.  All three are using the standard ActiveX Data Objects, or ADO, COM interface library to query a Microsoft Access database table and retrieve the first 100 rows.  Each sets a start and ending time to measure the time lapse in-between to roughly gauge relative performance.

Obviously, there are some major variables at play here.  Machine performance is just one of them.  Other factors include file system integrity and performance, database characteristics, coding methods, ambient room temperature, UV index, tidal fluctuations, wind direction and speed, cosmic radiation, and how much caffeine you've ingested prior to smacking the keyboard like a monkey angry for something to eat.

In my own case, I tested all three on the same machine, using the same Northwind.mdb file included with Microsoft Office Access 2003, running on a Dell Dimension E520 with 4 GB of memory and Windows 7 Ultimate build 7100 (32-bit).  I simply extracted the table "Inventory Transactions" to a new .MDB file and renamed the table to "Transactions" for simplicity of coding.

What I found was that VBScript 5.8 and KiXtart 4.61 (beta1) consistently finished in shorter time, and within a few milliseconds of each other.  There completion times were consistently between 640 and 690 msecs.  However, falling waaaaaaaaaay behind is PowerShell 2.0 (included with Windows 7 build 7100 "RC1").

My gut feeling is that this is the result of COM InterOp overhead, because I'm stepping outside the .NET environment to invoke the ADO interface to stay "apples-to-apples" with the other two.  This is unfair of course, so I will post an update where the PowerShell code stays within the ADO.NET world and see how that fairs.  So, to be honest and "fair" (which life seldom is) I would have to say that PowerShell doesn't have a "wooden leg", but rather, it has an interchangeable bionic leg.  One that works with COM and the other with .NET.

VBScript Example

'****************************************************************
' Filename..: ado_query.vbs
' Author....: skatterbrainz
' Date......: 05/05/09
' Purpose...: query ms-access database table, get first 10 records
' SQL.......: (path and .mdb filename)
'****************************************************************
Const dbfile = "c:\Northwind.mdb"
Const table  = "Transactions"
Const records = 100

Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adUseClient = 3
Const adCmdText = &H0001

t1 = Timer

dsn = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile

Set objConn = CreateObject("ADODB.Connection")
Set objCmd  = CreateObject("ADODB.Command")
Set objRS   = CreateObject("ADODB.Recordset")

strQuery = "SELECT TOP " & records & " * FROM " & table

objConn.Open dsn

objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.LockType = adLockReadOnly

Set objCmd.ActiveConnection = objConn

objCmd.CommandType = adCmdText
objCmd.CommandText = strQuery
objRS.Open objCmd

If objRS.BOF And objRS.EOF Then
objRS.Close
objConn.Close
Set objRS = Nothing
Set objCmd = Nothing
Set objConn = Nothing
Wscript.Echo "error: no records found"
Wscript.Quit
End If

Do Until objRS.EOF
Wscript.Echo "--------------------------"
For i = 0 to objRS.Fields.Count -1
   Wscript.Echo objRS.Fields(i).Name & ": " & objRS.Fields(i).Value
Next
objRS.MoveNext
Loop

Wscript.Echo vbCRLF & "Records: " &  objRS.RecordCount

objRS.Close
objConn.Close
Set objRS = Nothing
Set objCmd = Nothing
Set objConn = Nothing

t2 = Timer

runtime = Fix((t2 - t1) * 1000)
Wscript.Echo "Runtime: " & runtime & " msec"

KiXtart Example

;****************************************************************
; Filename..: ado_query.kix
; Author....: skatterbrainz
; Date......: 05/05/09
; Purpose...: query ms-access database table, get first 10 records
; SQL.......: (path and .mdb filename)
;****************************************************************
Break ON

$dbfile = "c:\Northwind.mdb"
$table  = "Transactions"
$records = 100

$adOpenStatic   = 3
$adLockReadOnly = 1
$adUseClient = 3
$adCmdText   = 1

$t1 = @msecs

$dsn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$dbfile"

$objConn = CreateObject("ADODB.Connection")
$objCmd  = CreateObject("ADODB.Command")
$objRS   = CreateObject("ADODB.Recordset")

$strQuery = "SELECT TOP $records * FROM $table"

$objConn.Open($dsn)

$objRS.CursorLocation = $adUseClient
$objRS.CursorType = $adOpenStatic
$objRS.LockType = $adLockReadOnly

$objCmd.ActiveConnection = $objConn

$objCmd.CommandType = $adCmdText
$objCmd.CommandText = $strQuery
$objRS.Open($objCmd)

If $objRS.BOF And $objRS.EOF
$objRS.Close
$objConn.Close
$objRS = 0
$objCmd = 0
$objConn = 0
? "error: no records found"
Exit @error
EndIf

While Not($objRS.EOF)
? "--------------------------"
For $i = 0 to $objRS.Fields.Count-1
   ? $objRS.Fields($i).Name + " = " + $objRS.Fields($i).Value
Next
$objRS.MoveNext
Loop

? @CRLF+"Records: "+$objRS.RecordCount

$objRS.Close
$objConn.Close
$objRS = 0
$objCmd = 0
$objConn = 0

$runtime = @msecs - $t1
? "Runtime: $runtime msecs"

PowerShell Example*

#****************************************************************
# Filename..: ado_query.ps1
# Author....: skatterbrainz
# Date......: 05/05/09
# Purpose...: query ms-access database table, get first 10 records
# SQL.......: (path and .mdb filename)
#****************************************************************
$dbfile = "c:\Northwind.mdb"
$table  = "Transactions"
$records = 100

$adOpenStatic   = 3
$adLockReadOnly = 1
$adUseClient = 3
$adCmdText   = 1

$t1 = get-date

$dsn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+$dbfile

$objConn = new-object -ComObject ADODB.Connection
$objCmd  = new-object -ComObject ADODB.Command
$objRS   = new-object -ComObject ADODB.RecordSet

$strQuery = "SELECT TOP "+$records+" * FROM "+$table

$objConn.Open($dsn)

$objRS.CursorLocation = $adUseClient
$objRS.CursorType = $adOpenStatic
$objRS.LockType = $adLockReadOnly

$objCmd.ActiveConnection = $objConn

$objCmd.CommandType = $adCmdText
$objCmd.CommandText = $strQuery
$objRS.Open($objCmd)

If ($objRS.BOF -and $objRS.EOF) {
   $objRS.Close()
   $objConn.Close()
   remove-variable $objRS
   remove-variable $objCmd
   remove-variable $objConn
   write-host "error: no records found"
   break
}

While (-not $objRS.EOF) {
   write-host "--------------------------"
   for ($i = 0; $i -lt $objRS.Fields.Count-1; $i++) {
       write-host $objRS.Fields.Item($i).Name: $objRS.Fields.Item($i).Value
   }
   $objRS.MoveNext();
}

write-host "`nRecords: " $objRS.RecordCount
$objRS.Close()
$objConn.Close()
Remove-Variable -name [string]$objRS
Remove-Variable -name [string]$objCmd
Remove-Variable -name [string]$objConn

$runtime = New-TimeSpan $t1 $(get-date)
write-host "Runtime: "$runtime.Milliseconds" msec"

You might be wondering about a few things you see here. For example, why I added "Break ON" in the KiXtart example. That's used to allow a user-initiated "break" in the midst of the loop iteration. If you don't include that and you hit CTRL+BREAK, it will usually cause Windows to log you off. It's a side effect of the fact that KiXtart was intended to be a login script platform, not a general scripting environment. But it just so happens that it makes a great general scripting environment, as long as you understand things like that. 

Also, you may notice the I used the Timer function in the VBScript example, rather than comparing two captures of the NOW object. That's because DateDiff() can only get down to Seconds, not Milliseconds, like Timer can support.

I hope this offers some useful information to someone out there. If so, great. If not, well, sorry. Let me know if you have any questions or comments also. Just post a comment in the Feedback.