To Use an Array or Not?

[blyxx86]

Hello everyone,
I am wondering what the best choice would be for an ASP page to run quickly and efficiently.

I'm not sure whether or not to use an array to store and loop through all the products for a certain group while building the page or to loop through each record in the database individually.

I see the two options, but am unsure which is more beneficial.

Option one is building the array (roughly 100+ records) before building the page and then looping through the array to populate the information on the page.

Option two is looping through the database to build the list of 100+ records without building an array.

Which option would be the best and most efficient method?

Thanks everyone!

[jhardman]

It's really a matter of preference. I generally just loop through the db. The only exception is when I have to pull data from two different places: I prefer to only have one open db connection at a time so I will usually dump one into an array and loop through the other one.

Putting everything into an array seems like it might be faster in some cases, but it would require additional coding steps, and that's the bottom line for me.

Jared

[blyxx86]

Storing in an array when pulling from multiple sources seems ideal. I have a single connstr.asp that I use that contains the single database I use. So it makes connecting a breeze. It even stores a bit of information in a webhistory table. I was quite proud of myself when I created that. :)

I imagine storing directly to an array makes more sense when the connection may be opened/closed/opened/closed/opened/closed. I could be wrong, as it is essentially reading the information twice. Is there any general rule of thumb when it comes to when to use an array?

[Nicodemas]

Using the GetRows method in ADO is the solution you are after. It will dump your recordset into a two-dimensional array. You are then free to release the recordset from memory.

Accessing information from an array is faster than accessing it from a recordset because of how the array is stored, compared to how the recordset is stored.

For efficiency, go with the 2d array.

set rs = dbconn.execute("select * from myTable")

if not rs.eof then
   aRows = rs.getrows '// throws your recordset into 2d array
   set rs = nothing '// frees system resources
   ibound = ubound(aRows, 2) '// gets number of records returned by query

   '// now you can do anything with the array
   '// the first dimension of the array represents the column or field
   '// the second dimension represents the row
else
   set rs = nothing
end if

[trynt799799]

I'd opt for the database option, although creating a small sized array of size 100 does not noteably reduce page-loading time the database approach might be more desireable.

<snipped>

[blyxx86]

I'd opt for the database option, although creating a small sized array of size 100 does not noteably reduce page-loading time the database approach might be more desireable.

<snipped>

So taking this information... Should I create arrays for larger pieces of information to be displayed?

I have a series of tables that are generated daily. Each table has approximately 10 columns and 100-500 rows. This equates to 1000-5000 individual values being pulled from the database. Should something like this be stored to an array first and then displayed?

[Nicodemas]

Yes! Because getRows dumps all of that into an array. If you cycled through all of them with an active connection, that's one connection per record. ouch.

[blyxx86]

Awesome!

Thank you for the advice. I have been super busy and haven't been able to come on here for a while.

I appreciate the help, and from now on will be using arrays more liberally. I always thought they were used only for small amounts of data, however the opposite seems true.

It's too bad that my newest project is using PHP and not ASP. However, since it is a MySQL backend, the functionality is nearly identical to the GetRows function.

Thank you,
Kyle

[CroCrew]

Hello Everyone,

I have to give credit where credit is due. Nicodemas is COMPLETELY RIGHT!!! After reading his post I decided to created a test to see if there is a difference in efficiency between the two (RecordSet –vs- Array) when looping thought data. 90% of the time looping thought an Array of data is 50% more faster!

What I did was created a database with 12800 records (grows by 100 records after every test) and created five runs of each looping type and then averaged the time of the five runs.

If you’re interested in seeing the test PM me and I will send you a link to it so, you can see for yourself.

Again, Nicodemas thanks for inspiring me to create this test. I myself will be dumping data in arrays more; especially when dealing with large amounts of data.

Thanks,
CroCrew~