Most efficient way to process this set of records???


Hello All,

I'd like to know the most efficient way to process a set of records. I've built a composite record that is to contain columns from 6 other "Master"
tables. I need to update the composite record with the values of those columns and perform some calculations along the way. I coded a vb.net (2005)
using Access (I know it should be SQL Server, but not allowed by client) to accomplish the task, but it takes an extremely long time. The code was
procedural as:

Get composite record
Set up Master-Table-1-Select-Parameters
Get Master-Table-1-Columns and place in Composite

Set up Master-Table-2-Select-Parameters
Get Master-Table-2-Columns and place in Composite

.....and so on with the other 4 Master tables.....

Update the datatable
Accept Changes
Go get another composite record and process until end of composites

It is further complicated by volume:

Composite table - 162,000 records
Master-Table-1 - 18,000 (1)
Master-Table-2 - 77,500 (1)
Master-Table-3 - 5,500 (2)
Master-Table-4 - 15,000 (1)
Master-Table-5 - 130,000 (1)
Master-Table-6 - 1,742,000 (2) (not a typo)
(The number in parens is the number of records I have to retrieve for EACH composite record.)

I tried to create a datarelation between a Master-Table and the Detail-Composite (in code) but ran into problems, which I posted a message to
"microsoft.public.dotnet.languages.vb". (See "DataRelation Woes!!! Need Help (column argument cannot be null)")

So my questions are:

By properly setting up datarelations, does that substantially increase performance over the way I originally coded it? (My guess is that I approached
this in the worst way possible.)

.....OR better yet,

Can anyone provide the process/logic to improve the performance to get this task accomplished??

Thanks,

Hexman

.

Relevant Pages

  • Re: Most efficient way to process this set of records???
    ... each of the six Master tables and then inserting them into a seventh table. ... Each row in the seventh table contains a composite, or aggregation, of the ... I need to update the composite record with the values of those ... (The number in parens is the number of records I have to retrieve for EACH ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Most efficient way to process this set of records???
    ... each of the six Master tables and then inserting them into a seventh table. ... Each row in the seventh table contains a composite, or aggregation, of the ... Can you give us in in other words a describtion what you want to accomplish ... I need to update the composite record with the values of those ...
    (microsoft.public.dotnet.framework.adonet)
  • Most efficient way to process this set of records???
    ... I've built a composite record that is to contain columns from 6 other "Master" ... I need to update the composite record with the values of those columns and perform some calculations along the way. ... I tried to create a datarelation between a Master-Table and the Detail-Composite but ran into problems, which I posted a message to ...
    (microsoft.public.vb.database.ado)
  • Re: Most efficient way to process this set of records???
    ... What I mean by "Composite" record is a group of columns from several tables to create a new table. ... Use datarelation between a master to the new detail ... procedure to setup command parameters and retrieve a master record, the time increases significantly. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Most efficient way to process this set of records??? (Bill Vaughn)
    ... I thought Bill's books were strictly for SQL Server. ... What I mean by "Composite" record is a group of columns from several ... Use datarelation between a master to the new detail (as opposed to ...
    (microsoft.public.dotnet.framework.adonet)