

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:
The methods that suck are (courtesy of David L Penton):
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:
<%
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.
|