Huge table conversion

[Andy Williams]

I'm developing a purchasing program which will require
access to a large amount of existing data. This data is
stored in a single table. It has 108 fields, and each row
represents a single PO. I've written code to convert this
to seperate header and detail tables, and it works... for
the most part.

In a nutshell, I'm using ADO to open 3 recordsets, I loop
through the source recordset and set the appropriate
fields in the destination recordsets (header and detail).
Problem is, it is horribly slow because its loading the
entire source recordset onto the client. I've never
gotten it to (or allowed it to) complete using the entire
table. Access memory usage gets up over 500mb and I give
up. It works fine when I run the code on a smaller
recordset (1000 records).

So my question is, is there any more efficent way to do
this? By my calculations, the detail table will end up
around 2 million records when I'm done, so we are dealing
with a large amount of data.

I chose not to post the code because it wraps horribly,
but if you'd like to take a look at it, I would be glad to
send it.

Thanks in advance.

Andy

 

[John Nurick]

Hi Andy,

If each row of your wide table represents a single purchase order I
guess it has fields somewhat like this
PO_Number
PO_Date
PO_CostCentre
Supplier_ID
Supplier_Ref
Item1_PartNumber
Item1_Description
Item1_Qty
Item1_UnitPrice
Item1_Tax
Item2_PartNumber
Item2_Description
...
and so on

If so, you don't need to use recordset operations, just a pair of Append
queries: one to put the "header" fields into the "header" table, and
other, run and modified and re-run several times, to append each group
of "detail" fields (e.g. PO_Number and all the Item1_ fields) to the
"detail" table.

If OTOH you have to do complicated things with the data that absolutely
require recordset operations, try somethign like this:

1) Create a query on the table that returns only the primary key
field(s) and open a recordset on this.

2) Iterate through the recordset; for each record have your code build a
SQL SELECT statement to return a single record from the table with the
that primary key, and use that to open a second recordset.

3) Build SQL INSERT INTO ... VALUES ... statements to append fields from
this second recordset to your "header" and "detail" tables. Close the
second recordset and move to the next record in the "primary key"
recordset.

This will still be slow but it avoids having the entire table on the
workstation at any time.

I'm developing a purchasing program which will require
access to a large amount of existing data. This data is
stored in a single table. It has 108 fields, and each row
represents a single PO. I've written code to convert this
to seperate header and detail tables, and it works... for
the most part.

In a nutshell, I'm using ADO to open 3 recordsets, I loop
through the source recordset and set the appropriate
fields in the destination recordsets (header and detail).
Problem is, it is horribly slow because its loading the
entire source recordset onto the client. I've never
gotten it to (or allowed it to) complete using the entire
table. Access memory usage gets up over 500mb and I give
up. It works fine when I run the code on a smaller
recordset (1000 records).

So my question is, is there any more efficent way to do
this? By my calculations, the detail table will end up
around 2 million records when I'm done, so we are dealing
with a large amount of data.

I chose not to post the code because it wraps horribly,
but if you'd like to take a look at it, I would be glad to
send it.

Thanks in advance.

Andy

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Andy Williams]

Thanks, that's a great idea. I'm using ADP and SQL
Server, so I'm not sure the append query thing will work.

-----Original Message-----
Hi Andy,

If each row of your wide table represents a single purchase order I
guess it has fields somewhat like this
PO_Number
PO_Date
PO_CostCentre
Supplier_ID
Supplier_Ref
Item1_PartNumber
Item1_Description
Item1_Qty
Item1_UnitPrice
Item1_Tax
Item2_PartNumber
Item2_Description
...
and so on

If so, you don't need to use recordset operations, just a pair of Append
queries: one to put the "header" fields into the "header" table, and
other, run and modified and re-run several times, to append each group
of "detail" fields (e.g. PO_Number and all the Item1_ fields) to the
"detail" table.

If OTOH you have to do complicated things with the data that absolutely
require recordset operations, try somethign like this:

1) Create a query on the table that returns only the primary key
field(s) and open a recordset on this.

2) Iterate through the recordset; for each record have your code build a
SQL SELECT statement to return a single record from the table with the
that primary key, and use that to open a second recordset.

3) Build SQL INSERT INTO ... VALUES ... statements to append fields from
this second recordset to your "header" and "detail" tables. Close the
second recordset and move to the next record in the "primary key"
recordset.

This will still be slow but it avoids having the entire table on the
workstation at any time.

I'm developing a purchasing program which will require
access to a large amount of existing data. This data is
stored in a single table. It has 108 fields, and each row
represents a single PO. I've written code to convert this
to seperate header and detail tables, and it works... for
the most part.

In a nutshell, I'm using ADO to open 3 recordsets, I loop
through the source recordset and set the appropriate
fields in the destination recordsets (header and detail).
Problem is, it is horribly slow because its loading the
entire source recordset onto the client. I've never
gotten it to (or allowed it to) complete using the entire
table. Access memory usage gets up over 500mb and I give
up. It works fine when I run the code on a smaller
recordset (1000 records).

So my question is, is there any more efficent way to do
this? By my calculations, the detail table will end up
around 2 million records when I'm done, so we are dealing
with a large amount of data.

I chose not to post the code because it wraps horribly,
but if you'd like to take a look at it, I would be glad to
send it.

Thanks in advance.

Andy

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Append queries jolly well ought to do the job. The whole point of SQL
Server (and like products) is to do almost everything on the server
using set operations (SQL) rather than by processing individual records
on the client.

Thanks, that's a great idea. I'm using ADP and SQL
Server, so I'm not sure the append query thing will work.

-----Original Message-----
Hi Andy,

If each row of your wide table represents a single purchase order I
guess it has fields somewhat like this
PO_Number
PO_Date
PO_CostCentre
Supplier_ID
Supplier_Ref
Item1_PartNumber
Item1_Description
Item1_Qty
Item1_UnitPrice
Item1_Tax
Item2_PartNumber
Item2_Description
...
and so on

If so, you don't need to use recordset operations, just a pair of Append
queries: one to put the "header" fields into the "header" table, and
other, run and modified and re-run several times, to append each group
of "detail" fields (e.g. PO_Number and all the Item1_ fields) to the
"detail" table.

If OTOH you have to do complicated things with the data that absolutely
require recordset operations, try somethign like this:

1) Create a query on the table that returns only the primary key
field(s) and open a recordset on this.

2) Iterate through the recordset; for each record have your code build a
SQL SELECT statement to return a single record from the table with the
that primary key, and use that to open a second recordset.

3) Build SQL INSERT INTO ... VALUES ... statements to append fields from
this second recordset to your "header" and "detail" tables. Close the
second recordset and move to the next record in the "primary key"
recordset.

This will still be slow but it avoids having the entire table on the
workstation at any time.

I'm developing a purchasing program which will require
access to a large amount of existing data. This data is
stored in a single table. It has 108 fields, and each row
represents a single PO. I've written code to convert this
to seperate header and detail tables, and it works... for
the most part.

In a nutshell, I'm using ADO to open 3 recordsets, I loop
through the source recordset and set the appropriate
fields in the destination recordsets (header and detail).
Problem is, it is horribly slow because its loading the
entire source recordset onto the client. I've never
gotten it to (or allowed it to) complete using the entire
table. Access memory usage gets up over 500mb and I give
up. It works fine when I run the code on a smaller
recordset (1000 records).

So my question is, is there any more efficent way to do
this? By my calculations, the detail table will end up
around 2 million records when I'm done, so we are dealing
with a large amount of data.

I chose not to post the code because it wraps horribly,
but if you'd like to take a look at it, I would be glad to
send it.

Thanks in advance.

Andy

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.