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

What he is needing to do is retrieve values from one or more columns from
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
data retrieved from the other six tables with a column corresponding to each
of the columns returned by his queries against the six tables. Think of it
as being similar to the Composite pattern, which is used in Microsoft's
Composite UI Application Block.

"Cor Ligthert [MVP]" wrote:

Hexman,

In my idea is composite records not a name used by Microsoft, my best search
on that gave me links to Sebastian Bach and Beethoven,

Can you give us in in other words a describtion what you want to accomplish
and why that is so different from methods as standard at AdoNet?

That you use Access or whatever is not the question in this.

Cor

"Hexman" <Hexman@xxxxxxxxxx> schreef in bericht
news:v9i6h21r2bbnakp0n1n1nrl6o9j8cu0e6d@xxxxxxxxxx

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??? (Bill Vaughn)
    ... I am more a dotNet guy than a SQL server guy, this seems for me typical a ... 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 ... procedure to setup command parameters and retrieve a master record, ...
    (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.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)
Loading