Faster Singleton Queries with ADO

It is very common operation to request one field of information via a database query. This is called a singleton query. ADO.Net has specific functions for singleton queries, but not ADO (ActiveX Data Objects). This article describes how to use an ADO command object to perform faster singleton queries.

The fastest way to obtain a singleton would probably be to create a stored procedure in the database. I don't typically use stored procedures for the following three reasons:

1. During development, I would have to switch between the database's query editor and my code editor (Textpad) very frequently, which is a waste of time.
2. Stored procedure languages are less standardized, increasing database lock-in.
3. Many applications have no need for highly optimized performance.

Returning a Singleton with an ADO Recordset

The most common way to get a singleton would be to use ADO's Recordset object. The function would take an initialized recordset and opened connection object, perform the query, and return the result.

Set objRS = Server.CreateObject("ADODB.Recordset")
Set objCN = Server.CreateObject("ADODB.Connection")
objCN.ConnectionString = "Provider=SQLOLEDB.1;" & _
	"Persist Security Info=False;User ID=username;" & _
	"Password=password;Data Source=sampledb;"
objCN.Open
strSQL = "SELECT ISNULL(SUM(Price*Qty), 0) AS InventoryValue FROM Products"
Response.Write "Inventory Value: " & SlowSingletonQuery(objRS, objCN, strSQL)

Function SlowSingletonQuery(ByRef objRS, ByRef objCN, ByVal strSQL)
	objRS.Open strSQL,objCN,adOpenForwardOnly,adLockReadOnly,adCmdText
	If Not objRS.EOF Then
		SlowSingletonQuery = objRS.Fields(0).Value
	Else
		SlowSingletonQuery = Null
	End If
	objRS.Close
End Function

This function had a fastest execution time of 4 milliseconds in my system environment. This is incredibly slow. The overhead is due to the clumsy recordset object, which contains things like field, paging, and cursor information that is useless for singleton queries. There is also the overhead of checking for the EOF condition. Instead, we can use the lighter-weight ADO command object, as shown below.

Returning a Singleton with an ADO Command

In this function, we pass the query and an opened connection object. The function then creates and initializes an ADODB command object as a prepared, text query. To return the singleton, we append a single output parameter of variant type and execute the command. Then, we simply return the value, which may be null if the query returned no records. You can use the ISNULL function in SQL server to prevent returning null as a value.

Note that the query is also different. The ? = unnamed parameter placeholder must be placed prior to the column expression. Also, you can not specify an alias for that column expression (in SQL Server). You should bind the active connection property to the open connection object prior to setting the command text and prepared flag, so that ADO will prepare the query when Execute is called.

' ... Open connection
strSQL = "SELECT ? = ISNULL(SUM(Price*Qty), 0) FROM Products"
Response.Write "Inventory Value : " & SingletonQuery(objCN, strSQL)

Function SingletonQuery(ByRef objCN, ByVal strSQL)
	Set objCmd = Server.CreateObject("ADODB.Command")
	Set objCmd.ActiveConnection = objCN
	objCmd.CommandText = strsql
	objCmd.CommandType = adCmdText
	objCmd.Parameters.Append _
		objCmd.CreateParameter(,adVariant,adParamOutput)
	objCmd.Prepared = True
	objCmd.Execute
	SingletonQuery = objCmd.Parameters.Item(0)
	Set objCmd = Nothing
End Function

In my system environment, the above function had a fastest time of 0.75 milliseconds. While still slow, it is more than four times faster than using a recordset. Furthermore, a three millisecond difference multiplied over several queries will make a perceptible difference to an end-user of an interactive system (such as a web page). This function is much faster despite having to instantiate a command object.

There is likely to be a penalty to preparing the query the first time it is executed. SQL Server tracks statistics and optimizes indexes, meaning that the execution of a particular query could speed up each time it is run. I observed this while testing. This server-based optimization is probably why preparing the command before execution makes it run faster even though the query is only being run once in the page. When the page is executed multiple times, the prepared query becomes faster, because SQL server optimizes its execution. This behaviour would be different for databases other than SQL Server.

Returning a Single Row with an ADO Command

The above function can be extended easily to return a single row as an array, without the overhead of a recordset.

strSQL = "SELECT ? = Price, ? = Qty FROM Products WHERE ID=100"

Function SingleRowQuery(ByRef objCN, ByVal strSQL, ByVal CountCols)
	Dim i, ArrayToReturn()
	Set objCmd = Server.CreateObject("ADODB.Command")
	Set objCmd.ActiveConnection = objCN
	objCmd.CommandText = strsql
	objCmd.CommandType = adCmdText
	For i = 0 To CountCols - 1
		objCmd.Parameters.Append _
		objCmd.CreateParameter(,adVariant,adParamOutput)
	Next
	objCmd.Prepared = True
	objCmd.Execute
	Redim ArrayToReturn(CountCols - 1)
	For i = 0 To CountCols - 1
		ArrayToReturn(i) = objCmd.Parameters.Item(i)
	Next
	Set objCmd = Nothing
	SingleRowQuery = ArrayToReturn
End Function

Notes

This technique does not work with the Jet (Access MDB) database engine, but works under SQL Server and other engines that support queries with output parameters. The above code is in VBScript in an Active Server Pages (IIS 5.1 on WinXP Pro) environment, and was run against a SQL Server 2000 database. The testing was done on a 1.8 Ghz Athlon XP processor and timed at high-resolution using the free Toolsack Baseline Stopwatch COM object.

You should not use singleton queries more than a few dozen times in one screen. To return larger tables of data at high speed, see my article ASP Speed Tricks. It contains more tips and sample code for improving the performance of ASP pages that use ADO with SQL Server. For more information, check the ADO 2.8 API Reference at Microsoft.com.