Recordset in RAM

Hi,

Normally when you show records from a database in a table
in .asp you create connectionstring, recordset and so on.
If the amount of data in the database is small ( < 20000
records) and easily will fit in RAM you could improve
performance dramatically by creating a recordset in RAM
and also do the sorting there as well.

An example:

Dim rstR
Set rstR = Server.CreateObject("ADODB.Recordset")
rstR.Fields.Append "myDate", adDate
rstR.Fields.Append "myAmount, adCurrency
rstR.Open

Now there is a recordset in RAM that can be used, although
empty still.

I also have a normal persistent table (initialised,
connected and named rstX) from where I fetch the records.

With rstX
.MoveFirst
Do While Not .Eof
rstR.AddNew
rstR.Fields("myDate") = .Fields("myDate")
rstR.Fields("myAmount") = .Fields("myAmount")
.MoveNext
Loop
End With

Now the rstR inline recordset in filled up with data.

Let's assume I want to let all sessions in a webapp share
the same inline table (rstR).

It's absolute critical that the rstR inline table should
ONLY be created ONCE on the webserver and that every
session can use it.

Should I use the application object to achive this?

Could someone give me some code and where to initialize
that code?

TIA

/Kenneth

Bob
Fri Aug 01 09:22:10 CDT 2003

I said you should save it to a file. Where is the ambiguity? One thing I
forgot to mention. Once you have saved it to a file in your
Application_onstart sub, you should close it and destroy it. The original
recordset that is still in RAM can't be used in any other threads (remember:
ADO is not free-threaded*), so it is just taking up server resources if you
leave it open. If you're worried about the disk IO, then don't be, the time
needed to re-open this file in each of your pages will be insignificant
compared to going to the database every time you need to get the data that's
cached in this recordset file.

Bob Barrows
PS. There IS a way to make ADO free-threaded, allowing you to store a
recordset in Application, but it is not recommended if you are using Access
at all from this your web server. There is batch file called makfre15.bat in
your C:\Program Files\Common Files\System\ADO folder which will make the
registry changes for you. Again, do not do this if any of your applications
are using Jet databases.

Bob Barrows

Kenneth wrote:
> Bob,
>
> Thanks, although there's one thing I don't really get.
> When I save the recordset to a file, I presume I should
> save it to RAM and not to a persistent file? And can I
> then close the rstR recordset and then let any page open
> it again from RAM?
>
> /Kenneth
>
>> No! Do not use the Application or Session object to store COM
>> objects unless they are free-threaded (which ADO is not).
>>
>> You can use your Global.asa file to create an Application_onstart
>> sub in which you can create and populate the rstR recordset. After
>> it is populated, use rstR.Save <filename> to save the recordset to a
>> file. Then, any page that needs it can open it by using:
>> set rstR = createobject("adodb.recordset")
>> rstR.Open <filename>
>>
>> HTH,
>> Bob Barrows

Kenneth
Fri Aug 01 15:18:03 CDT 2003

Bob,

If I'd do ADO free-threaded* with makfre15.bat, would it
then be possible for every session to access the rstR
recordset in RAM instead of calling the database each
time? And would it be possible to let every page ask for
this rstR recordset and let every page create a
subrecordset of rstR thus minimizing RAM? The rstR
recordset will vary from 20 - 50 MB, about 10000 to 25000
records with each record abour 2k.

By the way, is ADO.NET free-threaded*? Would it be easier
to accomplish this thing using ADO.NET and ASP.NET?

/Kenneth

>-----Original Message-----
>I said you should save it to a file. Where is the
ambiguity? One thing I forgot to mention. Once you have
saved it to a file in your
>Application_onstart sub, you should close it and destroy
it. The original
>recordset that is still in RAM can't be used in any other
threads (remember:
>ADO is not free-threaded*), so it is just taking up
server resources if you
>leave it open. If you're worried about the disk IO, then
don't be, the time
>needed to re-open this file in each of your pages will be
insignificant
>compared to going to the database every time you need to
get the data that's
>cached in this recordset file.
>
>Bob Barrows
>PS. There IS a way to make ADO free-threaded, allowing
you to store a
>recordset in Application, but it is not recommended if
you are using Access
>at all from this your web server. There is batch file
called makfre15.bat in
>your C:\Program Files\Common Files\System\ADO folder
which will make the
>registry changes for you. Again, do not do this if any of
your applications
>are using Jet databases.
>
>Bob Barrows
>
>Kenneth wrote:
>> Bob,
>>
>> Thanks, although there's one thing I don't really get.
>> When I save the recordset to a file, I presume I should
>> save it to RAM and not to a persistent file? And can I
>> then close the rstR recordset and then let any page open
>> it again from RAM?
>>
>> /Kenneth
>>
>>> No! Do not use the Application or Session object to
store COM
>>> objects unless they are free-threaded (which ADO is
not).
>>>
>>> You can use your Global.asa file to create an
Application_onstart
>>> sub in which you can create and populate the rstR
recordset. After
>>> it is populated, use rstR.Save <filename> to save the
recordset to a
>>> file. Then, any page that needs it can open it by
using:
>>> set rstR = createobject("adodb.recordset")
>>> rstR.Open <filename>
>>>
>>> HTH,
>>> Bob Barrows
>
>
>
>.
>

Bob
Fri Aug 01 15:51:41 CDT 2003

Kenneth wrote:
> Bob,
>
> If I'd do ADO free-threaded* with makfre15.bat, would it
> then be possible for every session to access the rstR
> recordset in RAM instead of calling the database each
> time?

Yes. Just do this:
In Global.ASA:
sub Application_onstart
'create and populate the recordset, then
set application("rstR") = rstR
end sub

In each page:
<%
dim rstR
Set rstR = application("rstR")
'etc.
%>


> And would it be possible to let every page ask for
> this rstR recordset and let every page create a
> subrecordset of rstR thus minimizing RAM? The rstR
> recordset will vary from 20 - 50 MB, about 10000 to 25000
> records with each record abour 2k.

Omigod! Don't store this amount of data in Application! It will be much more
conservative of system resources for each page to request only the records
they need from the database. WHAT ARE YOU THINKING!?!? I thought you were
talking about a little 10-50 record recordset! Your database is the proper
place to keep this data. For one thing, using SQL to get the records will be
much more efficient that using a recordset filter, especially with this much
data ... 20-50MB in RAM!!! Enough said!

>
> By the way, is ADO.NET free-threaded*? Would it be easier
> to accomplish this thing using ADO.NET and ASP.NET?
>
I haven't a clue. You'll need to ask on a dotnet group - I suggest
microsoft.public.dotnet.framework.adonet. 20-50MB ...