Best Performance / Speed way of loading data

[Arg81]

Since starting my new project in VS2005, CJard has helped me and I've learnt about iterating rows.
In terms of performance and speed, is iteration the way to go?

I have a report that is pretty much static - all that gets passed to it is the ID field and it loads the data into my dsReport dataSet.

I have Customer, CustomerSite and CustomerContact. Each table has fields that appear on the report.
CustomerSite and CustomerContact are both related to Customer via a CustomerID field.
At the moment, I'm just calling .fill() so ALL the rows are loading into my dataTables. Thats about 200 customer rows, 350 customer contact rows and 250 customer site rows.

Would it be better to iterate through all my main table rows, "capture" the customerID field, then load the 3 customer tables based on this ID? Or would this impact performace and speed on the loading of the report?
Therefore I would only get 1 customer row loaded, 1 customer site loaded and 1 customer contact loaded - the report displays the correct name for each ID in the tables, but all the data loaded is kept to a bare minimum....

I can't really justify loading all the customer rows into their tables for a "1 row" static report!!!!!

Advice welcome

 

[clweeks]

I'm not groking enough of what you're doing to offer advice. It sounds like you're selecting all data instead of using clever queries and then processing it in your app, is that the gist? How do you feel about the performance that you currently have? How much will the database scale upward?

 

[cjard]

Would it be better to iterate through all my main table rows, "capture" the customerID field, then load the 3 customer tables based on this ID?

Yes, it would

TA1.Fill(DT1, param)
 
For Each ro as DT1Row in DT1
  TA2.Fill(DT2, ro.ID)
  TA3.Fill(DT3, ro.ID)
Next ro

if DT3 hangs off DT2:

TA1.Fill(DT1, param)
 
For Each ro as DT1Row in DT1
  TA2.Fill(DT2, ro.ID)
Next ro
 
For Each ro as DT2Row in DT2
  TA3.Fill(DT3, ro.ID)
Next ro

 

[Arg81]

excellent.

Wasn't sure whether this was a more complicated and thus underperforming way of loading data.

regards,