Which way is better (arrays & db)

[davem]

I use an SQL query to get certain records from my database (using DAO w/ jet). Then I copy the data returned into a user-defined type array.

For example:
My database contains these columns...

"Name", "Age", "Height", "Weight", "Sex"

I define my user-defined type:

  Type myType
    Name as String
    Age as Integer
    Height as Double
    Weight as Double
    Sex as String
  End Type

Thus the "Name" column would be assigned to myType(1).Name for example.

I have a lot of columns (approx 20) in my real project. I also have about 9,000 records. To make things faster I chose to only "pull" data out of five columns (instead of all twenty) and assign them to my array.

THE QUESTION: (finally!) would there be a large speed/memory usage differential if I just dumped all 20 columns into my array (so I could work with all the values at my leisure?). Note, as of right now I use the data I got (the five columns) to access the database again (later on) to get any other info I need (say the user's "Origin for example).

To sum up, I know that getting all the records right off the bat would be faster and just to give you an idea of my data, the other 15 columns are basically double precision numbers or booleans. I just wanted to get an expert opinion.

[Ianpbaker]

Hi davem

you are right in saying that it would be quicker to get them on the fly but you could also make a class to handle all the database work, then a collection of the class to store each record.

Hope this helps

Ian

[paulw]

The way to answer this question is to examine the whole process. What is the sloweset thing you are doing? A. Accessing your db (v. slow). Storing and retrieving data in memory is quick (unless you use virtual memory). So optimise your data fetch.

Ensure that you only get the data you really need to transfer (i.e. limit the columns and limit the number of records).

Actual performance of an app is not the same as apparent performance. It may be quicker to fetch 9000 records than do twenty fetches of 450 records, but your users won't see it that way. If you can fetch data while the user is processing the first batch, so much the better.

Cheers,

P.