The use of .RecordCount involves the creation of Recordsets using relatively expensive cursors (click here for details). There are a number of alternative methods that can be used. Listed below are three preferred methods, and two methods that suck (and why they suck).

The preferred methods are:

• Query the SysIndex table (SQL Server only)
• Use of SELECT Count() when you only need a record count by not the data
• Use of .getRows when you need a record count and the data

The methods that suck are (courtesy of David L Penton):

• incrementing a counter variable while iterating the recordset
• populating the recordset via .movelast

Preferred Scenario 1 - (SQL Server Only)
The following page at swynk.com outlines how to query the sysindex table for the number of rows for a given table (opens in a new window): http://www.swynk.com/friends/achigrik/RowCount.asp

The gist of the code required is the construction of an SQL string similar to the following:

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('table_name')
AND indid < 2

Preferred Scenario 2
If you don't need the underlying data (eg to display on the screen), but rather just the number of records that match specific criteria, then use SQL's inbuild Count() operator.

SELECT Count(*) as NumRecords
FROM table1
WHERE foo = 'bar'

Doing a Response.Write(objRS("NumRecords")) will return the number of records that match the criteria. You can also use Count(fieldname) to omit records where Fieldname is Null. The benefit of this method is that no underlying data needs to be extracted from the database and that a fast adOpenForwardOnly cursor can be used.

Preferred Scenario 3 - Need the number of records and the data
If you need the underlying data you should look at using the Recordset's .getRows() method. This moves the recordset into a 0-based VBScript array. Evaluating the UBound() of the 2nd dimension of this array returns the record count.

<%
With objRS
   .Source = strSQL
   .ActiveConnection = objConn
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
End With

objRS.Open ,,,, adCmdText

If Not objRS.EOF then
   arrResults = objRS.GetRows
End if

objRS.Close
Set objRS = Nothing
...
If IsArray(arrResults) Then
   intRecordCount = UBound(arrResults, 2) + 1
End if
%>

The benefit of this method over .RecordCount is that the Recordset's resources can be released immediately after .GetRows() is invoked and that a fast adOpenForwardOnly cursor can be used.

Sucky Scenario 1
This type of scenario involves code such as:

<%
' Assume a connection to SQL Server, DB=pubs

strSQL = "SELECT DISTINCT "
strSQL = strSQL & "a.pub_name "
strSQL = strSQL & "FROM publishers AS a"

RecCount = 0

objRS.Open strSQL, objConn

Do While Not objRS.EOF

   RecCount = RecCount + 1
   objRS.movenext

Loop

objRS.close
Set objRS = Nothing
%>

First off, I know there are eight (8) distinct pub_names in this query. Let's look at this code expanded between the start code to find record count and the end code, line by line as if it was not in a loop:

<%
RecCount = RecCount + 1
objRs.MoveNext
RecCount = RecCount + 1
objRs.MoveNext
RecCount = RecCount + 1
objRs.MoveNext
RecCount = RecCount + 1
objRs.MoveNext
RecCount = RecCount + 1
objRs.MoveNext
RecCount = RecCount + 1
objRs.MoveNext
RecCount = RecCount + 1
objRs.MoveNext
RecCount = RecCount + 1
objRs.MoveNext
%>

Now, this is quite intensive, since I have the Record Set opened for all this time. If there were 100+ people doing this at the same time, there would be much database activity. What if there were 10,000+ records? You can see this is quite intensive.

Therefore, do not loop through a recordset with an incrementing variable for Record Count.

Sucky Scenario 2
Some people prefer to use a MoveLast operation to do this. Demonstrated using the previous code:

<%
objRs.MoveLast
RecCount = objRs.RecordCount
objRs.MoveFirst
%>

At first look, this seems to be a good idea. But let's look at what happens behind the scenes. Line one (1) of this snip will populate the entire recordset. So far, we have not done anything with it, but we have all of it. The provider has traversed the entire query. Now, since the recordset is populated, the .RecordCount Property will now have a value. Set that to our variable (line two (2)). Now we .MoveFirst, or move to the first record in the recordset (line (3)). The provider must place the current record pointer at the beginning of the recordset. Now we can display the records from the beginning (if we want to).

BUT WAIT! If I don't need the records (only the recordcount) then I have populated a recordset for nothing.

Therefore, do not use intensive methods to retrieve a record count.

Thanks to David L Penton for the explanation of the "sucky" ways to get a recordcount. David can be reached at david@davidpenton.com if you have any questions.

Back to FAQ listing.