Re: Most efficient way to process this set of records???
- From: Hexman <Hexman@xxxxxxxxxx>
- Date: Fri, 22 Sep 2006 00:03:45 -0700
Yes, Cor,
What I mean by "Composite" record is a group of columns from several tables to create a new table. In this particular task, there are 6 tables to
get columns from to create approximately 60 data columns plus about 10 fields used to create keys to select from those 6 tables. This newly created
table is to be used for analysis. Each of the columns will contain data that will allow the user to get a feel for his operation. For example, one
of the columns will be "days since last order". From that column (along with others) he will be able to tell if his customer base is stretching out
their ordering time frame - maybe that will suggest to offer incentives to the customers.
In short, what I am calling "Composite" is nothing more than columns in a detail record that come from several "Master" records (as in a Master-Detail
relationship). I didn't want to say "Master-Detail" because that may give someone the idea that these would have to be linked as an end result.
I ask the question to find out how best to create this new table.
1. Do procedurally as I have already done (probably not)
2. Use datareaders only to select and update the new table.
3. Use datarelation between a master to the new detail (as opposed to retrieve each master)
4. Use SQL commands only
I've noticed that when updating say 30,000 records in Access (and SQL EE) (setting one column to zero) it takes very little time. And when I add the
procedure to setup command parameters and retrieve a master record, the time increases significantly (much, much more than I would have thought). Now
adding 7 more retrieves especially against a large database (I consider 1.7m records large), becomes unbearable.
Are you aware of any books that talk about and give direct examples on performance enhancement?
I don't think what I'm trying to do is different - just getting data from 6 different tables and updating a single table.. (In my mainframe days we
would setup a new table with the columns we wanted and made passes with each of the master tables filling the new table. Maybe not the most
efficient, but it worked.)
Any help would be appreciated.
Hexman
On Fri, 22 Sep 2006 05:41:50 +0200, "Cor Ligthert [MVP]" <notmyfirstname@xxxxxxxxx> 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
- Follow-Ups:
- Re: Most efficient way to process this set of records??? (Bill Vaughn)
- From: Cor Ligthert [MVP]
- Re: Most efficient way to process this set of records??? (Bill Vaughn)
- References:
- Most efficient way to process this set of records???
- From: Hexman
- Re: Most efficient way to process this set of records???
- From: Cor Ligthert [MVP]
- Most efficient way to process this set of records???
- Prev by Date: Re: Most efficient way to process this set of records???
- Next by Date: Re: Most efficient way to process this set of records???
- Previous by thread: Re: Most efficient way to process this set of records???
- Next by thread: Re: Most efficient way to process this set of records??? (Bill Vaughn)
- Index(es):
Relevant Pages
|
Loading
