Appending to existing table painfully slow on network and 'out ofmemory' error

[Bri]

Hi,

First let me explain the process I have going on, then I'll address the
problems I'm having:
1) Insert records in a temp table using a query
2) Using a query that joins the temp table with TableA Insert records
into TableB
3) Delete records from temp Table
4) Table specs;
temp Table - 4 fields, 3 indexes, gets 100-4500 records inserted
TableA - 53 fields, 21 Indexes, 11 Relationships, ~25,000 records
TableB - 17 Fields, 10 Indexes, 5 Relationships, ~130,000 records
5) Environment Specs;
Development PC - P4-1.6Ghz, 512 Mb RAM, WinXP Pro SP1
Production PC - Celeron 1Ghz, 256 Mb RAM, WinXP Pro SP1, Networked
to an older server specs not available but its not that speedy. Both FE
and BE on Network (not ideal, but it hasn't caused any slowdowns until
this one).

Heres the problem; using the extreme end of this process (ie 4500
records in the temp Table) this takes 9 seconds to process on my
development PC, but takes ~15 minutes on the Production PC. I tried
enclosing the process in a Transaction to try and speed it up. This
results in no noticable increase in speed, but it does cause an 'out of
memory' error to occur if the user has any other applications running at
the same time. The Insert to the temp Table takes only ~30 secs and the
Inserts into the existing table take the rest. Same number of records,
but vastly different process times.

I've been reading the recommondations in the MS KB
(http://support.microsoft.com/default...b;en-us;889588)
and see that appends with a WinXP client below sp2 can be slow, but then
shouldn't both appends be slow? Is it maybe all the indexes and
relationships (15 total) that are slowing it down? Many of the items
mentioned in the KB article are changes on the server side and it could
be a hassle to convince the SA to play with the registry. Does using the
Transaction force the data to not be allowed to go into the swap file?
It really doesn't seem like it should be a lot of data to hold in RAM
(Task Manager - Network tab shows ~6 MB of data transfered to the
workstation in the first 2 min, then almost no network traffic for the
rest of the process).

Any ideas on what I should try next?

--
Bri

[jimfortune]

Bri wrote:

Hi,

First let me explain the process I have going on, then I'll address the
problems I'm having:
1) Insert records in a temp table using a query
2) Using a query that joins the temp table with TableA Insert records
into TableB
3) Delete records from temp Table
4) Table specs;
temp Table - 4 fields, 3 indexes, gets 100-4500 records inserted
TableA - 53 fields, 21 Indexes, 11 Relationships, ~25,000 records
TableB - 17 Fields, 10 Indexes, 5 Relationships, ~130,000 records
5) Environment Specs;
Development PC - P4-1.6Ghz, 512 Mb RAM, WinXP Pro SP1
Production PC - Celeron 1Ghz, 256 Mb RAM, WinXP Pro SP1, Networked
to an older server specs not available but its not that speedy. Both FE
and BE on Network (not ideal, but it hasn't caused any slowdowns until
this one).

Heres the problem; using the extreme end of this process (ie 4500
records in the temp Table) this takes 9 seconds to process on my
development PC, but takes ~15 minutes on the Production PC. I tried
enclosing the process in a Transaction to try and speed it up. This
results in no noticable increase in speed, but it does cause an 'out of
memory' error to occur if the user has any other applications running at
the same time. The Insert to the temp Table takes only ~30 secs and the
Inserts into the existing table take the rest. Same number of records,
but vastly different process times.

I've been reading the recommondations in the MS KB
(http://support.microsoft.com/default...b;en-us;889588)
and see that appends with a WinXP client below sp2 can be slow, but then
shouldn't both appends be slow? Is it maybe all the indexes and
relationships (15 total) that are slowing it down? Many of the items
mentioned in the KB article are changes on the server side and it could
be a hassle to convince the SA to play with the registry. Does using the
Transaction force the data to not be allowed to go into the swap file?
It really doesn't seem like it should be a lot of data to hold in RAM
(Task Manager - Network tab shows ~6 MB of data transfered to the
workstation in the first 2 min, then almost no network traffic for the
rest of the process).

Any ideas on what I should try next?

--
Bri

This is a total shot in the dark. How about breaking 2) into two
parts. Create the records that are to be inserted into TableB, then
insert them. Compare the durations of those two operations to the
duration of doing it all at once. That should give you a better idea
of where the bottleneck is. Also, make a copy of TableB without
relationships. See if inserting into that table takes about the same
time.

James A. Fortune

[Bri]

ji********@compumarc.com wrote:

Bri wrote:

Hi,

First let me explain the process I have going on, then I'll address the
problems I'm having:
1) Insert records in a temp table using a query
2) Using a query that joins the temp table with TableA Insert records
into TableB <snip>Any ideas on what I should try next?

--
Bri

This is a total shot in the dark. How about breaking 2) into two
parts. Create the records that are to be inserted into TableB, then
insert them. Compare the durations of those two operations to the
duration of doing it all at once. That should give you a better idea
of where the bottleneck is. Also, make a copy of TableB without
relationships. See if inserting into that table takes about the same
time.

James A. Fortune

I've figured out a change that eliminates TableA entirely (added the PK
for it to the temp Table as that was really all I needed) so the Insert
only involves the Temp Table and some fixed values. Made no difference
to the timing. Still very fast on my PC, very slow on production PC and
'out of memory error' with a Transaction. :(

I'll try the no indexes/relationships test on Monday or Tuesday and see
if it makes any difference. If it does, then I guess I've got to walk
the Index collection ad delete the indexes, do the insert, then recreate
the indexes. Sounds like a pain.

Any other ideas? Anyone?

--
Bri

[accesspro4u]

>> Still very fast on my PC, very slow on production

PC and 'out of memory error' with a Transaction.

at first I would think that the indexes may be slowing it down.. IMHO
10 indexes on a 17 field table seems like overkill, but since it only
takes 9 seconds on your machine, then this is likely not the issue...
your machine does have twice the memory as the production machine...
also, users are more likely to be running additional apps that are
eating up valuable memory as well... it may be worth a try figuring out
exactly what apps are running via Task Manager... there may be a
process that's running on the user's machine, that's interfering with
the update process.. I would disable any start-up programs, and clear
out the Start Up folder as well, as a test... also, is it possible that
an anti-virus scan is interfering? Is it possible that this user is
running into locking issues?

Mark
ac*********@aol.com

[lylefair]

Over Sunday morning coffee I cannot help but speculate on the notion of
a join between a local temp table with a remote (permanent) table.
How does JET effect this?
Does it use indexes?
If there are no existing indexes for the two tables does it create
temporary indexes?
Does it use a table scan?
Does it bring all the relevant data (index or table) from the remote
table to the local machine across the network and effect the join
there? (If so are there problems of concurrency; could such a join be
updateable?).
Or does it go search across the network for the relevant record(s) in
the remote table each time the local table pinter is moved?
Although the remote table is probably static in the join, does JET feel
it's required to check its relationships?

It seems that JET either has to move bunches of data before the append,
or worse, during the append.

Oh wait! Both the FE and the BE are on the network? So how would that
work? The datasets required for the join would both have to come to the
local machine? Or the whole mechanics of the join would come from the
FE to the local machine to the BE?

I've never found a situation where it is necessary to use a temp table.

Here are some things I would try:

1) Ditch the temp table and insert the records directly into Table B.
or
2) Use an ADO disconnected recordset based on Table B. Update and
append the required records. Do a batch update. (You can even make this
a form's recordset).
or (ugh)
3)
Copy the temp table to the BE. Put a query in the BE that does the join
and append you want. Open another instance of Access programatically;
within that open the BE and run the query and delete the temp table.
(Need to address compacting the BE too). Better still do it with ADO
and don't open another instance of Access; use VB to create an
executable that does it, or, my favourite, Windows Script and JScript.