Re: Most efficient way to process this set of records??? (Bill Vaughn)
- From: "Cor Ligthert [MVP]" <notmyfirstname@xxxxxxxxx>
- Date: Fri, 22 Sep 2006 18:41:54 +0200
Hexman,
I am more a dotNet guy than a SQL server guy, this seems for me typical a
question for Bill Vaughn if this is possible using direct the SQL server, if
not that I can maybe find a solution with tables, but that will be for sure
in this case much slower than direct processing on the Server.
It is not impossible he has this in one of his books.
Cor
"Hexman" <Hexman@xxxxxxxxxx> schreef in bericht
news:sa07h2h79955890ffrekbp41nh610lmm5j@xxxxxxxxxx
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:
- 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]
- Re: Most efficient way to process this set of records???
- From: Hexman
- Most efficient way to process this set of records???
- Prev by Date: Re: ADO.NET 2.0 Love it or die from it!!!
- Next by Date: Re: ADO.NET 2.0 Love it or die from it!!!
- 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
|
