Posted 18 years ago on 11/14/2006 and updated 2/26/2007
Take Away: In ASP, you can buffer a RecordSet in either a session or application variable. This code snippet shows you how to create a reusable method for buffering RecordSets in an application variable.
KB100410
The following code snippet uses Set Application("MyAppVar") = ARecordSet to assign a record set to an application variable. It also uses a second variable to store the current time and uses that variable to determine when to redo the select.
Sub DB_SelectReadBufferedApp(ASeconds, ByRef ARecordSet, AStrSQL) Dim AppVar Dim AppVarDateTime
''' DB_SelectRead is a typical read forward ASP execute select method. '''
DB_SelectRead ARecordSet, AStrSQL Application.Lock Set Application(AppVar) = ARecordSet Application(AppVarDateTime) = Now Application.Unlock
ElseIf DateDiff("s", CDate(Application(AppVarDateTime)), Now) > ASeconds Then DB_SelectRead ARecordSet, AStrSQL Application.Lock Set Application(AppVar) = ARecordSet Application(AppVarDateTime) = Now Application.Unlock End If
Set ARecordSet = Application(AppVar)
If ARecordSet.RecordCount > 1 Then ARecordSet.MoveFirst End If End Sub
Here is a usage example:
Dim obj Dim strSQL
strSQL = "select * from Core_Members where ProfileIsPublic = 'Y' and Picture<>'' " DB_SelectReadBufferedApp 3600, objMembersRS, strSQL objMembersRS.Sort = "LastName, FirstName"
while Not objMembersRS.EOF Response.Write objMembersRS.Fields("LastName") & ", " & objMembersRS.Fields("FirstName") objMembersRS.MoveNext WEnd
What about storing a RecordSet in a session variable?
You don't want to use this trick with session variables because that will break the thread model and actually make your website less scalable.
Can I over use this techinique?
Yes, very very easily. You should use this for ONLY the most frequently used data.
ASP Classic
Print This
