Re: Recordset or Array?

"Mark J. McGinty" <mmcginty@xxxxxxxxxxxxxxx> wrote in message
news:%231dkhnMSGHA.4792@xxxxxxxxxxxxxxxxxxxxxxx

"Chris Hohmann" <nospam@xxxxxxxxxxxx> wrote in message
news:ugW%23D6ESGHA.336@xxxxxxxxxxxxxxxxxxxxxxx
"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:OTXrJAESGHA.4952@xxxxxxxxxxxxxxxxxxxxxxx
Mark J. McGinty wrote:

If anyone is interested in the scripts I used to arrive at these
numbers, feel free to ask, your request will be ignored in the order
it was received. :-) j/k, I'll be more than happy to post them --

Nope, not necessary. :-)
I will concede the point.

Anthony, yes, I will concede that sometimes, maybe even usually, the
performance with recordsets is sufficient, but I like to be consistent
(I
know - " ... hobgoblin of small minds ... ", but still ...), so I
typically
take the array approach first. But there's no need to recover this
ground.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.



Actually, I'd be interested in seeing the scripts. I believe that the
increased memory footprint that Mark is observing is not due to the fact
that GetRows returns an array of variants. The Recordset.Field.Value
property is also a variant.

Quite true, but only one row's worth of data at a time is made available
via field objects. The rest is stored internally.


Ok, then you could call GetRows to retrieve one row at a time.


I believe that the problem lies in the fact that he is returning the
array all at once. You can make multiple calls to the GetRows method,
using the Rows parameter to avoid the need to load the

Irrelevant in the context of client cursors, which load and store all data
returned by the underlying query. I'm comparing memory consumed by
storing n rows in a recordset, to that consumed by storing the same n rows
in an array. That lets server cursors out of this loop.


Just because the client side recordset loads and stores all the data at once
doesn't mean that the array has to do the same.


The methodology of my tests was very simple:

Open a client-side recordset (CursorLocation = adUseClient)
Block execution with a MsgBox, observe memory allocated to process in
task manager
Call GetRows
Block execution with a MsgBox, observe memory allocated to process in
task manager
Re-assign array variable to an empty string
Block execution with a MsgBox, observe memory allocated to process in
task manager

(Needless to say this isn't do-able as such in ASP, but client script uses
the same recordset object, the same variant, and if it doesn't use the
same script engine as well, I'd be very much surprised.)


I'd still like to see the scripts.


If you then do the math to calculate the storage for fields * rows, you'll
see that recordset internal storage of data incurs little overhead,
whereas rendering all rows into an array of variants incurs significant
overhead. (Note that using a small number of rows for this test may make
the results seem inconclusive, due to overhead incurred by the scripting
engine, and the call to MsgBox.)


What are the hard numbers? How much memory does the recordset consume? What
if you don't load the entire dataset into the array, but do it piecemeal as
I've suggested?


entire dataset in memory at once and thereby simulate the paging that
occurs behind the scenes in a recordset. I alluded to this in a previous
discussion on this matter:

Please enlighten me if I'm wrong, but afaik, client-side recordsets do no
any paging behind the scenes.


I don't know. Having heard nothing to the contrary, I assumed paging
occurred regardless of cursor location. I'm willing to be persuaded if you
can should evidence to the contrary. However, I don't think that this would
effect my argument which is this. You don't need to load the entire result
set into the array at once, you can do it in pieces.




.

Relevant Pages

  • Re: Recordset or Array?
    ... I'd be interested in seeing the scripts. ... that GetRows returns an array of variants. ... property is also a variant. ... Open a client-side recordset ...
    (microsoft.public.inetserver.asp.db)
  • arrays
    ... I'm working on an ASP site for a client. ... I dumped a recordset into an array ... so that, with a bit of string handling and a few for..next loops, I can do a ... with this amended data in the array. ...
    (microsoft.public.scripting.vbscript)
  • Re: arrays
    ... >> I'm working on an ASP site for a client. ... I dumped a recordset into ... >> the table with this amended data in the array. ... Sparky Mark ...
    (microsoft.public.scripting.vbscript)
  • RE: Error 3021
    ... The only thing I see is that after you open the recordset, ... Create proto-file names using the selected job names and storre to an array ... Save and close the document and repeat the loop ...
    (microsoft.public.access.modulesdaovba)
  • Re: Logon script - function array and select case not working
    ... I am trying to rewrite my logon scripts which are all in vbscript. ... set objTSout = objFSO.CreateTextFile ... objTSout.writeline retrv ... below, i am trying to have a dynamic array, becuase this is polling group member ship and everyone will be different. ...
    (microsoft.public.scripting.vbscript)