Yesterday someone posted that you could use GetRows to move the recordset directly into an array. After checking their link
|
I read about using GetRows. On the surface it looked like this would be a time/resource saver, but now I'm not sure and I'm hoping someone could answer a few questions.
|
When I use GetRows the recordset still has to access all the requested records in the DB anyway in order to put them into the array. Then I still have to go through the array in order to create my output page. Doesn't this seem like I'm doing the same thing twice rather than just creating the output directly from the recordset?
|
RESPONSE: I believe i read somewhere, regarding GETROWS...it does not loop through the recordset but instead it does a straight mem-copy from the recorsdet format into the array format. Thus no expensive loop on the ADO Object
|
I would think that even though you destroy the recordset/connection after moving the data to the array that you still have to use server resources to hold all the data in the array. Is the only advantage to GetRows in the fact that you release the connection to the pool when you have a site with heavy traffic?
|
RESPONSE: there is overhead with the array, but significantly less. in an array, it is 2 simple a double (single ?) link lists in memory to hold each item. With a recordset you have all of the additional meta-information like properties, fiields collection, etc... One significant advantage of using getrows is the ability to dump the RS object as soon as possible (late binding, early release)...thus freeing up server useage. The connection object can or cannot be freed..(again it should be freed ASAP in order to take full advantage of connection pooling, and removing the object from memory)
|
Although you release the connection on your busy site you still have a large number of arrays in memory on the server. Isn't this trading one problem for another?
|
RESPONSE: Trading one problem for another? Not really, its is essentially using the least possible resources for the job. Why get a jackhammer to push a pin into your wall when you can just use your thumb?
|
If your recordset had a large number of records your array would consume a lot of resources. At what point (I realize this depends on hardware too) do you risk a server crash by using server resources to hold too many records in the array? 10, 000? 100,000?. . .etc.
|
RESPONSE: No matter if it is the array or the recordset, there will be a certain amount of overhead associated with larger amounts of data. Either way, think about the deployment. Your pushing content out to the browser, thus huge recordsets are not a feasible option, you would logically (ideally) normalize/cut down the data (summarize, total, etc..) in some offline processing.
|
Do you have to use any particular type of recordset (forwardonly, static, etc.) when you use GetRows? A fellow developer wondered if a forwardonly recordset could be used since he thought that the GetRows method would first have to get a recordcount in order to properly redimension the array to the correct size and usually you have to move the end of the recordset before you can get a count on the records.
|
RESPONSE: No particular type of recordset cursor type or location is needed. The defaults work just fine.
|
Long list of questions, but I wanted to fully understand the implications before I start using this extensively in my code. Any links to additional info on this would be appreciated as well. Thanks in advance!
|
RESPONSE: All the links I have are at the posted URL below. That URL (aspfree.com/devlinks) uses GETROWS extensively. (pratical example)
|
|
<Page updated Jan. 25, 2000>
|
I was wondering how do I find out how many items were returned in the recordset after I have placed the recordset in an array using getRows()?
|
What does UBOUND(arrayname,1) and UBOUND(arrayname,2) tell me??
|
RESPONSE: the second parameter in the UBOUND method is to indicate which dimension of the array you want the size of
|
when using GETROWS, you get a 2 dimensional array back.
|
arrayname(columns, rows)
|
Thus, ubound(arrayname,1) will return the number of columns, and ubound(arrayname,2) will return the number of rows.
|
</Page updated Jan. 25, 2000>
|
|
<Page updated March. 13, 2000>
|
Doing GetRows() on a recordset with only one field returned still returns a TWO-dimensional
|
array. I have puzzled over why the code like this didn't work...
|
Set rs = db.Execute("SELECT UserID FROM Users;")
|
arData = rs.GetRows()
|
For i = LBound(arData) to UBound(arData)
|
' blah blah blah
|
Next
|
I tripped over every time I made a one-field recordset, forgetting each time that it's *still* 2-d
|
RESPONSE: Yes, no matter how many columns or rows you have in the resulting recordset, it will always be 2 dimensional.
|
</Page updated March. 13, 2000>
|
Credit goes to Brian.Martens@wcom.com for writing up the list of questions, and to Tom Kelleher for additional questions.
|