I figured it out. SQL Server was slower on the sub query because it was
being performed using the same connection object as the main query. By
creating a second connection object, and using a prepared query on the
second connection object, the time to run the script goes down to 1.2
seconds, which is comparable to what was being acheived with ODBC.
Maybe there's an unwritten rule for SQL Server optimization under ADO,
that each opened recordset should have it's own connection for maximum
speed?
Here's the code containing all the test cases. The optimized case is
uncommented out. For this particular sub query, which returns only a
single value, it might run even faster by using an output return
parameter on the sub query, rather than creating a full record set.
<%@ Language=VBScript %>
<% Option Explicit %>
<html>
<head>
<style type="text/css">\
/* set the table layout to fixed width */
table.datatable { table-layout: fixed; width: 400px; }
table.datatable td { width: 100px; }
</style>
</head>
<body>
<%
Dim StartTime, EndTime
StartTime = Timer
Dim objCN ' ADO Connection object
Dim objRS ' ADO Recordset object
Dim strsql ' SQL query string
Dim objRS2 ' Another ADO Recordset object
Dim objCmd ' ADO Command object
Dim objParam ' ADO Parameter object
Dim objField0, objField1, objField2, objField3
Dim objCN2
' Create a connection object
Set objCN = Server.CreateObject("ADODB.Connection")
Set objCN2 = Server.CreateObject("ADODB.Connection")
' Connect to the data source
'objCN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"&_
' "Data Source=C:\Inetpub\wwwroot\data\test.mdb;"&_
' "Persist Security Info=False"
'objCN.ConnectionString = "DSN=datasource"
'objCN.ConnectionString = "DSN=datasourcesql;User ID=;Password=;"
objCN.ConnectionString = "Provider=SQLOLEDB.1;"&_
"User ID=;Data Source=localhost;"&_
"Initial Catalog=sqltest;Password=;"
objCN.Open
objCN2.ConnectionString = "Provider=SQLOLEDB.1;"&_
"User ID=;Data Source=localhost;"&_
"Initial Catalog=sqltest;Password=;"
objCN2.Open
' Create the a recordset object, and initialize it
Set objRS = Server.CreateObject("ADODB.RecordSet")
With objRS
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.ActiveConnection = objCN
.CursorLocation = adUseServer
.Source = "SELECT Field1,Field2,Field3,Field4 FROM tblData"
End With
' Create the second recordset object, and initialize it
Set objRS2 = Server.CreateObject("ADODB.RecordSet")
With objRS2
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.ActiveConnection = objCN2
.CursorLocation = adUseServer
End With
' Create command object
Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
.ActiveConnection = objCN2
.Prepared = True
.CommandType = adCmdText
.CommandText = "SELECT COUNT(*) FROM tblData WHERE Field1=?"
'.CommandType = adCmdStoredProc
'.CommandText = "dummyquery"
End With
' Set up Parameter object
Set objParam = objCmd.CreateParameter("Param1",adInteger,adParamInput,4)
' Append the parameter to the Command object's parameters collection
objCmd.Parameters.Append objParam
' Execute the SQL query
objRS.Open
' Set up field references after opening recordset
Set objField0 = objRS(0)
Set objField1 = objRS(1)
Set objField2 = objRS(2)
Set objField3 = objRS(3)
' Write out the results in a table
Response.write "<table class="&Chr(34)&"datatable"&Chr(34)&">"
Do While Not objRS.EOF
Response.write "<tr><td>"
Response.write objField0
Response.write "</td><td>"
Response.write objField1
Response.write "</td><td>"
Response.write objField2
Response.write "</td><td>"
Response.write objField3
Response.write "</td></tr>"
Response.write vbCrLf
Response.write "<tr><th colspan=4>"
' Prepared Command Object
objParam.Value = objRS(0)
objRS2.Open objCmd
'objRS2.Open "SELECT COUNT(*) FROM tblData"&_
" WHERE Field1="&objRS(0)
'Set objRS2 = objCN.Execute("EXECUTE dummyquery "&objRS(0))
'Set objRS2 = objCN2.Execute("{call "&_
" dummyquery('"&objRS(0)&"')}")
'Set objRS2 = objCN.Execute("SELECT COUNT(*) FROM tblData"&_
" WHERE Field1="&objRS(0))
'objCN.dummyquery objRS(0), objRS2
Response.write objRS2(0)
objRS2.Close
Response.write "</th></tr>"
objRS.MoveNext
Loop
Response.write "</table>"
objRS.Close
objCN.Close
Set objCN = Nothing
objCN2.Close
Set objCN2 = Nothing
Set objRS = Nothing
Set objRS2 = Nothing
Set objCmd = Nothing
EndTime = Timer
Response.write "<p>processing took "&(EndTime-StartTime)&" seconds<p> "
%>
</body>
</html>
