Recordset or Array?

Coding an ASP application and I'm creating a Public Function to return the
results from a query. I've been told that it's better to return a recordset
containing the results than it is to return an array.

I'm not sure which would be better, or how I'd handle passing a real
recordset back from my function. Below are the two functions, one returning
a recordset (I think) and the other returning an array (which works as I'm
using it now).

Comments?

'----------------------------------
Public Function GetUsersRS

Dim rs, conn, strSQL
Set rs = Server.CreateObject("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")

conn.open "myDB","dbUser","dbPassword"

strSQL = "SELECT * FROM users;"
rs.Open sSQL, conn

Set GetUsersRS = rs

'Finished with DB
rs.close
set rs=Nothing
conn.close
set conn=Nothing

End Function

'--------------------------------------
Public Function GetUsersArray

Dim rs, conn, strSQL
Set rs = Server.CreateObject("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")

conn.open "myDB","dbUser","dbPassword"

strSQL = "SELECT * FROM users;"
rs.Open sSQL, conn

if Not rs.EOF then
GetUsersArray = rs.GetRows()
else
GetUsersArray = Array()
end if

'Finished with DB
rs.close
set rs=Nothing
conn.close
set conn=Nothing

End Function

Re: Recordset or Array?

Noozer wrote:
> Coding an ASP application and I'm creating a Public Function to
> return the results from a query. I've been told that it's better to
> return a recordset containing the results than it is to return an
> array.

I'm assuming this does not involve marshalling objects across processes
(such as with a COM object).

It depends. Does the calling procedure require the functionality of a
recordset? If so, return a recordset. if not, return the array. Arrays are
much "cheaper" objects, so I would be inclined to use arrays if at all
possible.

>
> I'm not sure which would be better, or how I'd handle passing a real
> recordset back from my function. Below are the two functions, one
> returning a recordset (I think)

Yes, that would work. I would probably use a client-side cursor and
disconnect it if doing this.

const adUseClient=
rs.CursorLocation=adUseClient
rs.open ...
set rs.activeconnection=nothing
conn.close:set conn=nothing
set GetUsersRS=rs

> and the other returning an array
> (which works as I'm using it now).
>
> Comments?
>
> '----------------------------------
> Public Function GetUsersRS
>
> Dim rs, conn, strSQL
> Set rs = Server.CreateObject("ADODB.Recordset")
> Set conn = Server.CreateObject("ADODB.Connection")
>
> conn.open "myDB","dbUser","dbPassword"

Are you using a DSN? http://www.aspfaq.com/show.asp?id=2126

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Recordset or Array?

"Noozer" wrote in message
news:eTbRf.139597$B94.137607@pd7tw3no...
> Coding an ASP application and I'm creating a Public Function to return the
> results from a query. I've been told that it's better to return a
recordset
> containing the results than it is to return an array.
>

The problem with advice like that is that it often comes as-is without any
qualification as to why or in what circumstances it is 'better'.

> I'm not sure which would be better, or how I'd handle passing a real
> recordset back from my function

By way of balance I would suggest that a recordset would be better. Bob is
inclined to use an array because it is much "cheaper". However the cost of
a technique should not be put before function or easy of use IMO.

Admittedly arrays are fairly easy to use but when used in this way they can
make code code difficult to read and are can be a source of bugs.

Depending on the database you are using a forward only recordset from SQL
Server can be "cheaper" in terms of memory footprint than an array .


>Below are the two functions, one returning
> a recordset (I think) and the other returning an array (which works as I'm
> using it now).
>
> Comments?
>
> '----------------------------------
> Public Function GetUsersRS
>
> Dim rs, conn, strSQL
> Set rs = Server.CreateObject("ADODB.Recordset")
> Set conn = Server.CreateObject("ADODB.Connection")
>
> conn.open "myDB","dbUser","dbPassword"
>
> strSQL = "SELECT * FROM users;"
> rs.Open sSQL, conn
>
> Set GetUsersRS = rs
>
> 'Finished with DB
> rs.close
> set rs=Nothing
> conn.close
> set conn=Nothing
>
> End Function

Key problem here is you are closing the rs before you've had a chance to use
it.

I would write this:-

Public Function GetUsersRS()

Dim sSQL

Set GetUsersRS = Server.CreateObject("ADODB.Recordset")

sSQL = "SELECT UserID, Firstname, Lastname FROM users;"

GetUsersRS.Open sSQL, gstrConn 'gstrConn defined in a global ASP include

End Function

To use:-

Dim rs

Set rs = GetUsersRS()

Do Until rs.EOF
%>

<%=Server.HTMLEncode(rs("UserID"))%>
<%=Server.HTMLEncode(rs("Firstname"))%>
<%=Server.HTMLEncode(rs("Lastname "))%>

<%
rs.Movenext
Loop

rs.Close

The code above is very straight forward.


> '--------------------------------------
> Public Function GetUsersArray
>
> Dim rs, conn, strSQL
> Set rs = Server.CreateObject("ADODB.Recordset")
> Set conn = Server.CreateObject("ADODB.Connection")
>
> conn.open "myDB","dbUser","dbPassword"
>
> strSQL = "SELECT * FROM users;"
> rs.Open sSQL, conn
>
> if Not rs.EOF then
> GetUsersArray = rs.GetRows()
> else
> GetUsersArray = Array()
> end if
>
> 'Finished with DB
> rs.close
> set rs=Nothing
> conn.close
> set conn=Nothing
>
> End Function
>


Alternatively here is my best effort at the array approach

Public Function GetUsersArray()

Dim rs
Dim sSQL

Set GetUsersRS = Server.CreateObject("ADODB.Recordset")

sSQL = "SELECT UserID, Firstname, Lastname FROM users;"

GetUsersRS.Open sSQL, gstrConn 'gstrConn defined in a global ASP include

If Not rs.EOF then
GetUsersArray = rs.GetRows()
Else
GetUsersArray = Array()
End If

rs.Close

End Function


To use:-

Const cUserID = 0
Const cFirstName = 1
Const cLastName = 2

Dim arr
Dim i

arr = GetUsersArray()

If UBound(arr,1) > -1 Then

For i = 0 To UBound(arr,2) Then
%>

<%=Server.HTMLEncode(arr (cUserID, i))%>
<%=Server.HTMLEncode(arr (cFirstName, i))%>
<%=Server.HTMLEncode(arr (cLastName, i))%>

<%
Next

End If

As you can see its a little messy compared to the RS based one. The real
fun begins when the query is modified where the ordinal position of the
fields have changed. The set of constants that list the fields need to be
adjusted as well. Not hard in this simple example but in more complex
scenarios it can waste a fair bit of time in debugging.

Hence my recommendation would be use recordsets, then after the code is
stable and working and if performance testing shows it's warranted switch to
using an array if that will perform better.

Anthony.

Re: Recordset or Array?

"Noozer" wrote in message
news:eTbRf.139597$B94.137607@pd7tw3no...
> Coding an ASP application and I'm creating a Public Function to return the
> results from a query. I've been told that it's better to return a
> recordset containing the results than it is to return an array.
>
> I'm not sure which would be better, or how I'd handle passing a real
> recordset back from my function. Below are the two functions, one
> returning a recordset (I think) and the other returning an array (which
> works as I'm using it now).
>
> Comments?

I don't think your recordset function will work as-is, you're returning a
reference to a recordset, then closing it. The caller will get a closed
recordset. Further, you're closing the connection too, which will close all
recordsets opened on it.

The caller can close both the recordset and the connection:

Set rs = GetUsersRS()
' code to use recordset
Set cn = rs.ActiveConnection
rs.Close
cn.Close


-Mark



> '----------------------------------
> Public Function GetUsersRS
>
> Dim rs, conn, strSQL
> Set rs = Server.CreateObject("ADODB.Recordset")
> Set conn = Server.CreateObject("ADODB.Connection")
>
> conn.open "myDB","dbUser","dbPassword"
>
> strSQL = "SELECT * FROM users;"
> rs.Open sSQL, conn
>
> Set GetUsersRS = rs
>
> 'Finished with DB
> rs.close
> set rs=Nothing
> conn.close
> set conn=Nothing
>
> End Function
>
> '--------------------------------------
> Public Function GetUsersArray
>
> Dim rs, conn, strSQL
> Set rs = Server.CreateObject("ADODB.Recordset")
> Set conn = Server.CreateObject("ADODB.Connection")
>
> conn.open "myDB","dbUser","dbPassword"
>
> strSQL = "SELECT * FROM users;"
> rs.Open sSQL, conn
>
> if Not rs.EOF then
> GetUsersArray = rs.GetRows()
> else
> GetUsersArray = Array()
> end if
>
> 'Finished with DB
> rs.close
> set rs=Nothing
> conn.close
> set conn=Nothing
>
> End Function
>

Re: Recordset or Array?

"Anthony Jones" wrote in message
news:uxyhCcsRGHA.1236@TK2MSFTNGP11.phx.gbl...
>
> "Noozer" wrote in message
> news:eTbRf.139597$B94.137607@pd7tw3no...
>> Coding an ASP application and I'm creating a Public Function to return
>> the
>> results from a query. I've been told that it's better to return a
> recordset
>> containing the results than it is to return an array.
>>
>
> The problem with advice like that is that it often comes as-is without any
> qualification as to why or in what circumstances it is 'better'.

I was told that the footprint of a recordset would be smaller, as strings
wouldn't be converted to unicode. Also that working with a recordset is
simpler(?) than working with arrays.

>>Below are the two functions, one returning
>> a recordset (I think) and the other returning an array (which works as
>> I'm
>> using it now).
>>
>> Comments?
>>
>> '----------------------------------
>> Public Function GetUsersRS
>>
>> Dim rs, conn, strSQL
>> Set rs = Server.CreateObject("ADODB.Recordset")
>> Set conn = Server.CreateObject("ADODB.Connection")
>>
>> conn.open "myDB","dbUser","dbPassword"
>>
>> strSQL = "SELECT * FROM users;"
>> rs.Open sSQL, conn
>>
>> Set GetUsersRS = rs
>>
>> 'Finished with DB
>> rs.close
>> set rs=Nothing
>> conn.close
>> set conn=Nothing
>>
>> End Function
>
> Key problem here is you are closing the rs before you've had a chance to
> use
> it.
>
> I would write this:-
>
> Public Function GetUsersRS()
> Dim sSQL
> Set GetUsersRS = Server.CreateObject("ADODB.Recordset")
> sSQL = "SELECT UserID, Firstname, Lastname FROM users;"
> GetUsersRS.Open sSQL, gstrConn 'gstrConn defined in a global ASP include
> End Function
>

But wouldn't this mean a constant connection to the DB?

I'd prefer to make the connection at the time I'm generating the recordset,
and only long enough to get the data.


> As you can see its a little messy compared to the RS based one. The real
> fun begins when the query is modified where the ordinal position of the
> fields have changed. The set of constants that list the fields need to be
> adjusted as well. Not hard in this simple example but in more complex
> scenarios it can waste a fair bit of time in debugging.

One of the reasons I'd prefer to use recordsets instead of arrays.

> Hence my recommendation would be use recordsets, then after the code is
> stable and working and if performance testing shows it's warranted switch
> to
> using an array if that will perform better.

Re: Recordset or Array?

> "Noozer" wrote in message
> news:eTbRf.139597$B94.137607@pd7tw3no...
>> Coding an ASP application and I'm creating a Public Function to return
>> the results from a query. I've been told that it's better to return a
>> recordset containing the results than it is to return an array.
>>
>> I'm not sure which would be better, or how I'd handle passing a real
>> recordset back from my function. Below are the two functions, one
>> returning a recordset (I think) and the other returning an array (which
>> works as I'm using it now).
>>
>> Comments?
>
> I don't think your recordset function will work as-is, you're returning a
> reference to a recordset, then closing it. The caller will get a closed
> recordset. Further, you're closing the connection too, which will close
> all recordsets opened on it.

That's what I thought. I'm not sure how I can return a recordset without
maintaining a connection to the DB.

> The caller can close both the recordset and the connection:
>
> Set rs = GetUsersRS()
> ' code to use recordset
> Set cn = rs.ActiveConnection
> rs.Close
> cn.Close

I'm trying to avoid having the caller do any work on the connection.

I'm probably misunderstanding something here... Can a recordset stand on
it's own, with no reference to a database or connection?

Basically, I'm trying to create a function that accepts an SQL statement and
returns the resulting recordset, handling opening the connection, retrieving
the recordset and closing the connection all within the function.

ie.

Dim rsMyUsers

Set rsMyUsers = SQLrs("SELECT * FROM users;")

Do While Not rsMyUsers.EOF
response.write rsMyUsers("UserName") & "
"
rsMyUsers.MoveNext
Loop

Set rsMyUsers=Nothing

Re: Recordset or Array?

Noozer wrote:
>> "Noozer" wrote in message
>> news:eTbRf.139597$B94.137607@pd7tw3no...
>>
>> I don't think your recordset function will work as-is, you're
>> returning a reference to a recordset, then closing it. The caller
>> will get a closed recordset. Further, you're closing the connection
>> too, which will close all recordsets opened on it.
>
> That's what I thought. I'm not sure how I can return a recordset
> without maintaining a connection to the DB.
>
>> The caller can close both the recordset and the connection:
>>
>> Set rs = GetUsersRS()
>> ' code to use recordset
>> Set cn = rs.ActiveConnection
>> rs.Close
>> cn.Close
>
> I'm trying to avoid having the caller do any work on the connection.
>
> I'm probably misunderstanding something here... Can a recordset stand
> on it's own, with no reference to a database or connection?

Yes. See my first reply, which I stand by even though I failed to see the
issue where you closed and destroyed the recordset within the function.

You can open a client-side recordset using a database data source,
disconnect the database by setting the recordset's ActiveConnection property
to Nothing, do stuff to the recordset, and if you've set the recordset's
locktype to adLockOptimisticBatch, you can reconnect the recordset to the
database by setting the ActiveConnection property to an open connection
object, and use the recordset's UpdateBatch method to send your changes back
to the database.

>
> Basically, I'm trying to create a function that accepts an SQL
> statement and returns the resulting recordset, handling opening the
> connection, retrieving the recordset and closing the connection all
> within the function.

Due to the dangers of sql injection (you will be building these sql strings
via concatenation, correct?) I would be very hesitant in implementing or
using such a function. Being too generic can be a bad thing.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Recordset or Array?

Noozer wrote:
> "Anthony Jones" wrote in message
> news:uxyhCcsRGHA.1236@TK2MSFTNGP11.phx.gbl...
>>
>> "Noozer" wrote in message
>> news:eTbRf.139597$B94.137607@pd7tw3no...
>>> Coding an ASP application and I'm creating a Public Function to
>>> return the
>>> results from a query. I've been told that it's better to return a
>>> recordset containing the results than it is to return an array.
>>>
>>
>> The problem with advice like that is that it often comes as-is
>> without any qualification as to why or in what circumstances it is
>> 'better'.
>
> I was told that the footprint of a recordset would be smaller, as
> strings wouldn't be converted to unicode. Also that working with a
> recordset is simpler(?) than working with arrays.

I definitely disagree with this. A recordset is a large COM object which
contains lots of metadata, objects and methods which, while making a
recordset a little easier to work with than an array, also make the
recordset bulkier and slower. In an ASP application, you usually only need
about 10-20% of the functionality provided by a recordset object. See the
aspfaq.com article about recordset iteration to see how much faster looping
through an array can be. And in the same article, look at the tremendous
speed improvements that can be gained by using GetString.

In my applications, I've seen tremendous performance improvements from
making recordset use the exception, rather than the rule.

>
>> As you can see its a little messy compared to the RS based one.

Not so much that I would sacrifice the performance gained by using the
array. I maintain that recordsets should only be used when you require
functionality offered by the recordset that is not offered by using arrays:
sorting, filtering, etc.

>> The real fun begins when the query is modified where the ordinal
>> position of the fields have changed.

Umm, we usually have control over that don't we? And good
commenting/documentatin should cover the cases where we don't.

>> The set of constants that list
>> the fields need to be adjusted as well. Not hard in this simple
>> example but in more complex scenarios it can waste a fair bit of
>> time in debugging.
>
> One of the reasons I'd prefer to use recordsets instead of arrays.
>
>> Hence my recommendation would be use recordsets, then after the
>> code is stable and working and if performance testing shows it's
>> warranted switch to
>> using an array if that will perform better.


I do not believe ther's any question that an array will perform better,
unless you have a single-row recordset.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Recordset or Array?

"Noozer" wrote in message
news:04wRf.140453$sa3.99464@pd7tw1no...
>
> "Anthony Jones" wrote in message
> news:uxyhCcsRGHA.1236@TK2MSFTNGP11.phx.gbl...
> >
> > "Noozer" wrote in message
> > news:eTbRf.139597$B94.137607@pd7tw3no...
> >> Coding an ASP application and I'm creating a Public Function to return
> >> the
> >> results from a query. I've been told that it's better to return a
> > recordset
> >> containing the results than it is to return an array.
> >>
> >
> > The problem with advice like that is that it often comes as-is without
any
> > qualification as to why or in what circumstances it is 'better'.
>
> I was told that the footprint of a recordset would be smaller, as strings
> wouldn't be converted to unicode. Also that working with a recordset is
> simpler(?) than working with arrays.
>

I think the recordset foot print would depend on the internal implementation
of the provider.
I could well imagine that the single byte characters would remain such until
actually read through ADO. In the case of a SQL Server forward only
recordset the foot print is smaller since a cursor isn't actually maintained
at all and once you've consumed a record it is discarded.

My examples demonstrate that working with a recordset is simpler (and IMO
more robust when things change) but use of arrays isn't prohibitively
complex either when done with care.

> >>Below are the two functions, one returning
> >> a recordset (I think) and the other returning an array (which works as
> >> I'm
> >> using it now).
> >>
> >> Comments?
> >>
> >> '----------------------------------
> >> Public Function GetUsersRS
> >>
> >> Dim rs, conn, strSQL
> >> Set rs = Server.CreateObject("ADODB.Recordset")
> >> Set conn = Server.CreateObject("ADODB.Connection")
> >>
> >> conn.open "myDB","dbUser","dbPassword"
> >>
> >> strSQL = "SELECT * FROM users;"
> >> rs.Open sSQL, conn
> >>
> >> Set GetUsersRS = rs
> >>
> >> 'Finished with DB
> >> rs.close
> >> set rs=Nothing
> >> conn.close
> >> set conn=Nothing
> >>
> >> End Function
> >
> > Key problem here is you are closing the rs before you've had a chance to
> > use
> > it.
> >
> > I would write this:-
> >
> > Public Function GetUsersRS()
> > Dim sSQL
> > Set GetUsersRS = Server.CreateObject("ADODB.Recordset")
> > sSQL = "SELECT UserID, Firstname, Lastname FROM users;"
> > GetUsersRS.Open sSQL, gstrConn 'gstrConn defined in a global ASP
include
> > End Function
> >
>
> But wouldn't this mean a constant connection to the DB?
>
> I'd prefer to make the connection at the time I'm generating the
recordset,
> and only long enough to get the data.
>

Why is this good?

Conventional wisdom 10 years ago would have us, quite rightly, going out of
our way to avoid holding database server resources longer than we need to.
Not doing so could lead to scalability problems.

However, with today's technology I think you would have to be talking
multi-webservers in a farm before you really need to worry overly about
scalability of the DB side of things.

Bear in mind that potentially the bottle neck in the process could well be
the network between Web server and SQL Server, in which case you could find
that the array approach doesn't release SQL Server resources as quick as you
might have hoped.

This is a performance choice that can and should be made later (unless you
happen to be developing something that you know upfront needs to support
4000 concurrently active users). Keep the code simple, get it complete and
working. Once that is done analyse any areas that may be a concern
performance wise and tweak/test. Customers are usually happier to have
something that works (albeit slower than desirable) than to have something
that appears to be blisteringly fast but is riddled with bugs.

>
> > As you can see its a little messy compared to the RS based one. The
real
> > fun begins when the query is modified where the ordinal position of the
> > fields have changed. The set of constants that list the fields need to
be
> > adjusted as well. Not hard in this simple example but in more complex
> > scenarios it can waste a fair bit of time in debugging.
>
> One of the reasons I'd prefer to use recordsets instead of arrays.
>

Agreed.

> > Hence my recommendation would be use recordsets, then after the code is
> > stable and working and if performance testing shows it's warranted
switch
> > to
> > using an array if that will perform better.
>
>
>

Re: Recordset or Array?

> >
> >> As you can see its a little messy compared to the RS based one.
>
> Not so much that I would sacrifice the performance gained by using the
> array. I maintain that recordsets should only be used when you require
> functionality offered by the recordset that is not offered by using
arrays:
> sorting, filtering, etc.
>

Ok. OTH I still maintain the simplest approach should be used until you
know you need to make it more complex.

> >> The real fun begins when the query is modified where the ordinal
> >> position of the fields have changed.
>
> Umm, we usually have control over that don't we? And good
> commenting/documentatin should cover the cases where we don't.

Commenting and documentation, Hmm.. I'm not going to go there, don't want to
spark off a religous war. ;)

We have control over it. Do we have control over ourselves?? I know I make
mistakes all the time. The simpler my code can remain the fewer mistakes I
make. Perhaps those brighter and better programmers than I can use more
complex methods with impunaty.

See I can't even speel straight.

>
> >> The set of constants that list
> >> the fields need to be adjusted as well. Not hard in this simple
> >> example but in more complex scenarios it can waste a fair bit of
> >> time in debugging.
> >
> > One of the reasons I'd prefer to use recordsets instead of arrays.
> >
> >> Hence my recommendation would be use recordsets, then after the
> >> code is stable and working and if performance testing shows it's
> >> warranted switch to
> >> using an array if that will perform better.
>
>
> I do not believe ther's any question that an array will perform better,
> unless you have a single-row recordset.
>

I think the real question is, does it need to perform better?

> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Re: Recordset or Array?

Anthony Jones wrote:
> "Noozer" wrote in message
> news:04wRf.140453$sa3.99464@pd7tw1no...
>>
>> "Anthony Jones" wrote in message
>> news:uxyhCcsRGHA.1236@TK2MSFTNGP11.phx.gbl...
>>>
>>> "Noozer" wrote in message
>>> news:eTbRf.139597$B94.137607@pd7tw3no...
>>>> Coding an ASP application and I'm creating a Public Function to
>>>> return the
>>>> results from a query. I've been told that it's better to return a
>>>> recordset containing the results than it is to return an array.
>>>>
>>>
>>> The problem with advice like that is that it often comes as-is
>>> without any qualification as to why or in what circumstances it is
>>> 'better'.
>>
>> I was told that the footprint of a recordset would be smaller, as
>> strings wouldn't be converted to unicode. Also that working with a
>> recordset is simpler(?) than working with arrays.
>>
>
> I think the recordset foot print would depend on the internal
> implementation of the provider.
> I could well imagine that the single byte characters would remain
> such until actually read through ADO. In the case of a SQL Server
> forward only recordset the foot print is smaller since a cursor isn't
> actually maintained at all and once you've consumed a record it is
> discarded.

Even in a forward-only cursor, all the metadata is populated, and all the
structures devoted to maintaining the metadata are there, even if not used
(for example, in a forward-only recordset, the only value one will find in
the recordCount property is -1. But, the RecordCount Property object and the
methods required to access that property are still present). Unless we are
dealing with a large number of records (which should be a no-no in as ASP
application in the first place), the recordset footprint almost has to be
larger than the array's.

Also, if dealing with Access, all data is stored as unicode anyways.

>
>> But wouldn't this mean a constant connection to the DB?
>>
>> I'd prefer to make the connection at the time I'm generating the
>> recordset, and only long enough to get the data.
>>
>
> Why is this good?

Connection pooling. Or in the OLE DB world: session pooling.
http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2 .asp
http://support.microsoft.com/?kbid=328476 (connection failures due to lack
of pooling)

>
> Conventional wisdom 10 years ago would have us, quite rightly, going
> out of our way to avoid holding database server resources longer than
> we need to. Not doing so could lead to scalability problems.

I believe this is still the case. see the KB article cited above.
>
> However, with today's technology I think you would have to be talking
> multi-webservers in a farm before you really need to worry overly
> about scalability of the DB side of things.
>
> Bear in mind that potentially the bottle neck in the process could
> well be the network between Web server and SQL Server, in which case
> you could find that the array approach doesn't release SQL Server
> resources as quick as you might have hoped.

I guess we're going to have to agree to disagree.

Bob Barrows
--
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.

Re: Recordset or Array?

Uh oh everybody, look out, I'm going to... [gasp] top-post -- holy crap a
big chunk of the sky just fell right next to me! Hmm, the rest still looks
stable enough, must be a fluke. :-)

Disconnecting the recordset (as Bob suggests) will relieve your caller
function of any connection cleanup. Note (if it hasn't been stated already)
that you must use a client cursor for this (CursorLocation = adUseClient).
It has been my experience that GetRows performs much better with client
cursors than with server cursors, but your mileage may vary, I suggest you
test it both ways.

[For the rest of this, assume I'm talking about recordsets using client
cursors.]

As for recordset overhead, there are at least some cases (from my tests, all
cases, though again, your mileage may vary) where the resulting array from
GetRows consumes more memory than does the source recordset.

Consider that GetRows returns an array of variants. Variant overhead is a
minimum of 8 bytes per value (not counting the value itself) and, depending
on the type, can be as much as 16 bytes (as in the case of a SafeArray.)
Also, as has been mentioned, variants of type VT_STRING are stored in
Unicode, so if the source strings were ANSI, it's a 2:1 storage hit there
too.

Arrays are, in all cases I've observed, faster than recordsets, but the
difference can be minimized by a.) referencing fields using ordinal value
instead of the name of the field; and b.) by using adodb.field objects.
(Before anyone screams about magic numbers and code readability, the use of
Const definitions to represent field ordinals is strongly suggested.)

Otoh, I've observed cases where the array returned by GetRows consumed 3x
the memory used by the recordset it came from -- and it all has to be in
memory at once, even if only for an instant. Memory allocation overhead
becomes more than substantial if the memory must be virtualized -- memory
consumption is most definitely a scalability issue.

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 -- DDL will be for SQL server
only; Jet users will have to adjust. (I don't do Jet anymore, life is too
short.)


-Mark




"Bob Barrows [MVP]" wrote in message
news:%230j0321RGHA.4956@TK2MSFTNGP09.phx.gbl...
> Noozer wrote:
>>> "Noozer" wrote in message
>>> news:eTbRf.139597$B94.137607@pd7tw3no...
>>>
>>> I don't think your recordset function will work as-is, you're
>>> returning a reference to a recordset, then closing it. The caller
>>> will get a closed recordset. Further, you're closing the connection
>>> too, which will close all recordsets opened on it.
>>
>> That's what I thought. I'm not sure how I can return a recordset
>> without maintaining a connection to the DB.
>>
>>> The caller can close both the recordset and the connection:
>>>
>>> Set rs = GetUsersRS()
>>> ' code to use recordset
>>> Set cn = rs.ActiveConnection
>>> rs.Close
>>> cn.Close
>>
>> I'm trying to avoid having the caller do any work on the connection.
>>
>> I'm probably misunderstanding something here... Can a recordset stand
>> on it's own, with no reference to a database or connection?
>
> Yes. See my first reply, which I stand by even though I failed to see the
> issue where you closed and destroyed the recordset within the function.
>
> You can open a client-side recordset using a database data source,
> disconnect the database by setting the recordset's ActiveConnection
> property to Nothing, do stuff to the recordset, and if you've set the
> recordset's locktype to adLockOptimisticBatch, you can reconnect the
> recordset to the database by setting the ActiveConnection property to an
> open connection object, and use the recordset's UpdateBatch method to send
> your changes back to the database.
>
>>
>> Basically, I'm trying to create a function that accepts an SQL
>> statement and returns the resulting recordset, handling opening the
>> connection, retrieving the recordset and closing the connection all
>> within the function.
>
> Due to the dangers of sql injection (you will be building these sql
> strings via concatenation, correct?) I would be very hesitant in
> implementing or using such a function. Being too generic can be a bad
> thing.
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: Recordset or Array?

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.

Re: Recordset or Array?

"Bob Barrows [MVP]" wrote in message
news:OTXrJAESGHA.4952@TK2MSFTNGP09.phx.gbl...
> 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. 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
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:

http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/30bc57381712d89b

Also, here's the link to the documentation for the Field.Value property,
indicating that it is a variant:

http://msdn.microsoft.com/library/en-us/ado270/htm/mdprovalu e.asp

I look forward to seeing the scripts. It's a subject that I find quite
interesting.

Re: Recordset or Array?

"Chris Hohmann" wrote in message
news:ugW%23D6ESGHA.336@TK2MSFTNGP12.phx.gbl...
> "Bob Barrows [MVP]" wrote in message
> news:OTXrJAESGHA.4952@TK2MSFTNGP09.phx.gbl...
>> 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.

> 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.

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.)

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.)

> 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.


-Mark



> http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/30bc57381712d89b
>
> Also, here's the link to the documentation for the Field.Value property,
> indicating that it is a variant:
>
> http://msdn.microsoft.com/library/en-us/ado270/htm/mdprovalu e.asp
>
> I look forward to seeing the scripts. It's a subject that I find quite
> interesting.
>
>

Re: Recordset or Array?

>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

Which DB Engine are using?
You may be specifying a Client side cursor location but what CursorType has
been requested or are you leaving it at it's default?

Anthony.

Re: Recordset or Array?

Anthony Jones wrote:
>> 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
>
> Which DB Engine are using?
> You may be specifying a Client side cursor location but what
> CursorType has been requested or are you leaving it at it's default?
>
Client-side cursor = Static cursor, regardless of provider. When a
client-side cursor is requested, ADO creates a cursor using its local cursor
library. It then populates this cursor using a temporary firehose cursor
which is then discarded*. Since the data is contained in a cursor maintained
by the local cursor library, it cannot react to changes made to data by
other users. So by definition, you have a static cursor.

Bob Barrows

*From "Programming ADO" by David Sceppa.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Recordset or Array?

> Client-side cursor = Static cursor, regardless of provider. When a
> client-side cursor is requested, ADO creates a cursor using its local
cursor
> library. It then populates this cursor using a temporary firehose cursor
> which is then discarded*. Since the data is contained in a cursor
maintained
> by the local cursor library, it cannot react to changes made to data by
> other users. So by definition, you have a static cursor.
>

Actually the reason for my questions is this. If the DB in use is SQL
Server and the cursortype requested is a forwardonly recordset (which is the
default) then the reason why resources being used by ADO seem so small is
because there is no cursor at all. Not even a static one.

The TDS stream may see a bit of buffering on the client end but thats it.
If performance is a concern then it's likely in an ASP envirionment that SQL
server will be the DB of choice. By default opening a simple SELECT based
recordset will give you a forwardonly 'cursor'. Typically then the ASP runs
through the records spitting out HTML as it goes. If it weren't for
response buffering which is typical in ASP the memory footprint of all this
could remain at a reasonably small constant level regardless of the number
of rows being read.

What damages scalability is the fact that the SQL Server is likely to be
able to provide records way faster than ASP can read and process them.
Hence spitting out of TDS records will stall leaving some of the query
resources allocated on the SQL server for longer.

Having said that if ASP can process one record faster than it can be
transfered over the network between Web Server and SQL Server then the
network becomes the bottleneck and GetRows won't alleviate this. All it
would do is require more memory on the Web Server which grows with the size
of rowset.


Anthony.

Re: Recordset or Array?

Anthony Jones wrote:
>> Client-side cursor = Static cursor, regardless of provider. When a
>> client-side cursor is requested, ADO creates a cursor using its
>> local cursor library. It then populates this cursor using a
>> temporary firehose cursor which is then discarded*. Since the data
>> is contained in a cursor maintained by the local cursor library, it
>> cannot react to changes made to data by other users. So by
>> definition, you have a static cursor.
>>
>
> Actually the reason for my questions is this. If the DB in use is SQL
> Server and the cursortype requested is a forwardonly recordset (which
> is the default)

.... again, this depends on the cursorlocation ... if a client-side cursor is
opened, you get a client-side static cursor, regardless of the cursor type
requested.

Ken Schaefer has some code on his site that illustrates this.
http://www.adopenstatic.com/experiments/clientsidecursortype s.asp

> then the reason why resources being used by ADO seem
> so small is because there is no cursor at all. Not even a static one.
>

Right. if a server-side cursor is requested, there is no use made of the ADO
Cursor Library (I think that's what you mean by "no cursor"). Of course,
there is a cursor that is maintained on the database server.

> The TDS stream may see a bit of buffering on the client end but thats
> it.

This can be controlled by the CacheSize recordset property. Even a
forward-only cursor can provide some backward navigation capabilities if
more than one record is buffered. Technically, the entire resultset can be
buffered, but then, one might as well be using a client-side cursor ...

> If performance is a concern then it's likely in an ASP
> envirionment that SQL server will be the DB of choice. By default
> opening a simple SELECT based recordset will give you a forwardonly
> 'cursor'.


Given that the default server-side cursor is requested....

> Typically then the ASP runs through the records spitting
> out HTML as it goes. If it weren't for response buffering which is
> typical in ASP the memory footprint of all this could remain at a
> reasonably small constant level regardless of the number of rows
> being read.
>
> What damages scalability is the fact that the SQL Server is likely to
> be able to provide records way faster than ASP can read and process
> them. Hence spitting out of TDS records will stall leaving some of
> the query resources allocated on the SQL server for longer.

Unless a client-side cursor is used ... but then, resource usage is simply
moved to the client (the web server)
>
> Having said that if ASP can process one record faster than it can be
> transfered over the network between Web Server and SQL Server then the
> network becomes the bottleneck and GetRows won't alleviate this.

No test I've ever seen or run backs this up. There is no case I have ever
seen in which opening a recordset and looping through it has out-performed
using getrows to populate an array which is used in a loop. If you have such
a test case, I would definitely be interested in seeing it.

Granted, if processing the recordset does not involve looping through a
bunch of records, then GetRows may be overkill, and in fact, should not be
used.

> All
> it would do is require more memory on the Web Server which grows with
> the size of rowset.
>
>
No, that's not all it would do. Yes, memory used by the array would
increase, but given that processing an array is much faster, that memory
will be released much quicker. In addition, the memory required by ADO to
retrieve the data is released even before the array processing has begun
(given efficient coding), and, best of all, the connection is released to
the pool quicker, allowing its re-use by another thread rather than forcing
another connection to be spawned.

Bob Barrows
--
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.

Re: Recordset or Array?

>... again, this depends on the cursorlocation ... if a client-side cursor
is
>opened, you get a client-side static cursor, regardless of the cursor type
>requested.

>Ken Schaefer has some code on his site that illustrates this.
>http://www.adopenstatic.com/experiments/clientsidecursortyp es.asp

Interesting. I'd always believed it the other way round. That
CursorLocation had no effect unless a static type was requested. One learns
a new thing every day. :)

>Right. if a server-side cursor is requested, there is no use made of the
ADO
>Cursor Library (I think that's what you mean by "no cursor"). Of course,
>there is a cursor that is maintained on the database server.

Well not really. There is a series of buffers but as each are read by the
client they are discarded and SQL Server will stall the query if the client
is getting behind.

>> If performance is a concern then it's likely in an ASP
>> envirionment that SQL server will be the DB of choice. By default
>> opening a simple SELECT based recordset will give you a forwardonly
>> 'cursor'.

>Given that the default server-side cursor is requested....

Yes I see.

>> Having said that if ASP can process one record faster than it can be
>> transfered over the network between Web Server and SQL Server then the
>> network becomes the bottleneck and GetRows won't alleviate this.

>No test I've ever seen or run backs this up. There is no case I have ever
>seen in which opening a recordset and looping through it has out-performed
>using getrows to populate an array which is used in a loop. If you have
such
>a test case, I would definitely be interested in seeing it.

I doubt it. It's a theoretical thing. In the real world the SQL Server
will be too well connected to the Web Server for VBScript to out perform the
network.

>No, that's not all it would do. Yes, memory used by the array would
>increase, but given that processing an array is much faster, that memory
>will be released much quicker. In addition, the memory required by ADO to
>retrieve the data is released even before the array processing has begun
>(given efficient coding), and, best of all, the connection is released to
>the pool quicker, allowing its re-use by another thread rather than forcing
>another connection to be spawned.

Yes I don't doubt that in the overall set of trade-offs that using the array
would be better in terms of performance.

Re: Recordset or Array?

"Anthony Jones" wrote in message
news:%23yQF6uNSGHA.1160@TK2MSFTNGP09.phx.gbl...
> >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
>
> Which DB Engine are using?

SQL Server 2000.

> You may be specifying a Client side cursor location but what CursorType
> has
> been requested or are you leaving it at it's default?

Client-side cursors are always static (regardless of what was requested.)


-Mark


> Anthony.
>
>

Re: Recordset or Array?

"Mark J. McGinty" wrote in message
news:%231dkhnMSGHA.4792@TK2MSFTNGP14.phx.gbl...
>
> "Chris Hohmann" wrote in message
> news:ugW%23D6ESGHA.336@TK2MSFTNGP12.phx.gbl...
>> "Bob Barrows [MVP]" wrote in message
>> news:OTXrJAESGHA.4952@TK2MSFTNGP09.phx.gbl...
>>> 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.

Re: Recordset or Array?

"Chris Hohmann" wrote in message
news:%2316evYSSGHA.4740@TK2MSFTNGP14.phx.gbl...
> "Mark J. McGinty" wrote in message
> news:%231dkhnMSGHA.4792@TK2MSFTNGP14.phx.gbl...
>>
>> "Chris Hohmann" wrote in message
>> news:ugW%23D6ESGHA.336@TK2MSFTNGP12.phx.gbl...
>>> "Bob Barrows [MVP]" wrote in message
>>> news:OTXrJAESGHA.4952@TK2MSFTNGP09.phx.gbl...
>>>> 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.

My guess is that by doing so, you would lose everything you gained in
performance, compared to just using the recordset (not to mention that the
code would be unnecessarily convoluted, and considerably more difficult to
read.)

>>> 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.

You're missing the point, which is this: an array of variants, as returned
by GetRows consumes more memory, row for row, than does a recordset, in many
if not most cases. How you twist the scenario is of no consequence
whatsoever. (And your apparent "make GetRows array work at all costs"
mentality hardly seems indicative of an open, objective mindset, which
likely means I'm wasting my time writing this... but oh well....)


>> 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.

As you wish... I had to rewrite it. Watch the wrap, the entire script is
indented at least 1 space (which is what pasting from Dev Studio yielded in
place of tabs) so if it starts at the beginning of the line, it wrapped.
You will need to adjust the connection string to fit your local resources.
DDL for SQL 2000 will follow.

''''''''''''' begin rstest.vbs script
Const adUseClient = 3

Dim rs, cn, a, i, bEnoughFirstLast
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

bEnoughFirstLast = False

cn.CursorLocation = adUseClient
cn.Open
"Provider=SQLOLEDB;Server=(local);Database=misc;Trusted_Conn ection=Yes;",
adUseClient
Set rs.ActiveConnection = cn

MsgBox "Before loop",, "M0"

i = 1

Do While i < 130
rs.Open "select top " & CStr(i * 1000) & " * from rstest"
MsgBox "recordset rows:" & CStr(rs.RecordCount),, "M1"

if (bEnoughFirstLast = False) then
rs.MoveLast
MsgBox "recordset.MoveLast"
rs.MoveFirst
bEnoughFirstLast = (MsgBox("recordset.MoveFirst (click cancel to skip
MoveFirst/Last)", vbOKCancel) = vbCancel)
End If
a = rs.GetRows()
MsgBox "array(" & CStr(ubound(a, 1)) & "," & CStr(ubound(a, 2)) & ")",,
"M2"
redim a(0,0)
MsgBox "array(0,0)",, "M3"
rs.Close
if MsgBox("recordset closed (click cancel to abort the test)", vbOKCancel,
"M4") = vbCancel then exit do
i = i * 2
Loop

cn.Close

''''''''''''' end rstest.vbs script


Note the msgbox titles. When the title says "M2" (just after calling
GetRows) use TaskMan to observe the memory allocated to cscript.exe -- that
value I'll call M2. When the title says "M3" (just after redim a(0,0)) use
TaskMan to observe the memory allocated to cscript.exe -- that value I'll
call M3.... etc, etc.

For each iteration, M2 - M3 is the approximate amount of memory consumed by
the array returned by GetRows. M3 - M4 is the approximate amount of memory
consumed by the recordset.

The calls to MoveLast/MoveFirst, and pauses inbetween were merely to
illustrate that all rows in the recordset are in memory, and that no paging
is taking place. (The script allows for these to be skipped.)


>> 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?

At 1000 rows in my tests, based on fluctuations in memory allocated to the
cscript.exe process the array took almost 75% more than the recordset. At
2000 rows the array consumed twice as much memory. Note that results are
dependent on table schema used by the tests.

My choice of field types may have been purposely designed to yield dramatic
results (but was by absolutely no means worst case); your mileage may vary.
Specifically, I openly admit that a table could be constructed that would
make the two storage constructs come out even (a single nvarchar column
would do it) but that is not a matter of consequence, it does not detract
from my point.


> 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.

And my counter-argument is that even if you do that, no matter how small the
pieces are, you are *still* using more memory than you would without any
calls to GetRows, plus you incur additional overhead for each successive
call, plus multiple small allocations are often less efficient than fewer
large allocations, *plus* your source code becomes increasingly convoluted,
toward no really useful end as far as I can tell.


-Mark


DDL:

-- DROP TABLE [dbo].[rstest]

CREATE TABLE [dbo].[rstest] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[r] [float] NOT NULL ,
[d] [varchar] (2000) NOT NULL ,
[i1] [int] NOT NULL ,
[i2] [int] NOT NULL ,
[i3] [int] NOT NULL ,
[i4] [int] NOT NULL ,
[i5] [int] NOT NULL
) ON [PRIMARY]


ALTER TABLE [dbo].[rstest] WITH NOCHECK ADD
CONSTRAINT [PK_rstest] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]


ALTER TABLE [dbo].[rstest] ADD
CONSTRAINT [DF_rstest_r] DEFAULT (rand(convert(int,
SUBSTRING(convert(binary(8),getdate()), 7, 2)))) FOR [r],
CONSTRAINT [DF_rstest_d] DEFAULT (replicate(convert(varchar(30),getdate(),
121),25)) FOR [d],
CONSTRAINT [DF_rstest_i1] DEFAULT (0) FOR [i1],
CONSTRAINT [DF_rstest_i2] DEFAULT (0) FOR [i2],
CONSTRAINT [DF_rstest_i3] DEFAULT (0) FOR [i3],
CONSTRAINT [DF_rstest_i4] DEFAULT (0) FOR [i4],
CONSTRAINT [DF_rstest_i5] DEFAULT (0) FOR [i5]



INSERT INTO rstest (i1) Values(1)
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest
INSERT INTO rstest (i1) SELECT i1 FROM rstest

-- this will result in 128K rows in this test table