Slow Append

[Chutney via AccessMonster.com]

I am looking for a more efficient (read faster) way to append new records to
and update existing records in a master table.

My master table, tblMaster, is primary keyed on ref_no. I download data from
the mainframe to a staging table, tblStaging, that has no indexes. The
downloaded data consists of two type of records: new records that do not
exist in tblMaster (i.e. their ref_no is not in tblMaster) and old records
that exist in tblMaster but have new data.

My requirement, and procedure, is quite simple.
1. I run an update query that relates the two tables on ref_no and replaces
the data in all fields WHERE ref_no is equal. (Most records are in this
category.)
2. I run an append query that attempts to append all records from tblStaging
to tblMaster. The primary key allows only the new records to be appended.
(There are only a few records in this category.)

The update query is slow but acceptable. The append query is unbelievably
slow and not acceptable. A "Find Unmatched Query" created by the Wizard runs
reasonably fast to create a list of the records to be appended. However, when
I apply the same join logic (WHERE tblMaster.ref_no Is Null) to the append
query it does not seem to run any faster.

Can anyone suggest a more efficient way(s) to achieve the update and append?
a. Can I somehow combine the two queries?
b. Can the first query strip out the updated records so the second query
deals only with the remaining new queries?
c. Would indexing tblStaging on ref_no help. I don't see how but I am willing
to try if there is a logical reason. (I have not done so because it will slow
down the import procedure thereby, perhaps, negating any increase in append
speed.)

Thanks and regards.

 

[Ken Snell [MVP]]

Just a guess, but often the cause of a slow append query is because the
table receiving the data has a lot of indices on the fields, and each record
that is appended causes the indices to be updated.

Try removing all but primary key index from the table to which you're
appending the data and see if it goes much faster. You can then put the
indices back on the fields after the append query is done.

This removal and restoration of the indices can be done programmatically as
well.

 

[Van T. Dinh]

1. Can you link to the Table in the main-frame rather than importing the
data into tblStaging? This will save you one time-consuming step.

2. JET is actually fairly quirky on Update Queries: If you use the correct
join, JET actually does updates for Records in tblMaster if there exist
corresponding Records in tblStaging AND append Records into tblMaster if
there exist Records in tblStaging without corresponding Records in
tblMaster.

You need a right join in this case, something like:

UPDATE tblMaster RIGHT JOIN tblStaging
ON tblMaster.Ref_No = tblStaging.Ref_No
SET ...

 

[Chutney via AccessMonster.com]

Ken,

Thank you for your suggestion. I know that indices slow down the append and
tblMaster does have a lot of them. For this trial, however, I did have only
primary key and the append took so long I eventually killed it.

On that matter, I assume it is more efficient to remove the indicies,
append/update the records and then rebuild the indicies, depending upon the
number of records in tblMaster, the number of records to be appended/updated
and the number of indicies. Is my assumption correct and is there a way to
calculate the approximate point that remove/rebuild is more efficient than
leaving the indicies in place?

Regards

Just a guess, but often the cause of a slow append query is because the
table receiving the data has a lot of indices on the fields, and each record
that is appended causes the indices to be updated.

Try removing all but primary key index from the table to which you're
appending the data and see if it goes much faster. You can then put the
indices back on the fields after the append query is done.

This removal and restoration of the indices can be done programmatically as
well.

I am looking for a more efficient (read faster) way to append new records
to

[quoted text clipped - 39 lines]

Thanks and regards.

 

[Chutney via AccessMonster.com]

Van,

Thank you for your reply.

1. I dearly wish I could link directly to the main-frame but unfortunately I
cannot.

2. This is one of the tricks I have been looking for. Just to confirm I
understand correctly, this simple UPDATE...RIGHT JOIN construct performs both
APPEND and UPDATE. Am I correct? I had never heard of that.

Regards.

1. Can you link to the Table in the main-frame rather than importing the
data into tblStaging? This will save you one time-consuming step.

2. JET is actually fairly quirky on Update Queries: If you use the correct
join, JET actually does updates for Records in tblMaster if there exist
corresponding Records in tblStaging AND append Records into tblMaster if
there exist Records in tblStaging without corresponding Records in
tblMaster.

You need a right join in this case, something like:

UPDATE tblMaster RIGHT JOIN tblStaging
ON tblMaster.Ref_No = tblStaging.Ref_No
SET ...

I am looking for a more efficient (read faster) way to append new records
to

[quoted text clipped - 39 lines]

Thanks and regards.

 

[Ken Snell [MVP]]

I don't have any suggestions for determining when it's more efficient to
remove and reapply indices. I can tell you that I used that method to
accomplish an append in 12 seconds that previously was taking 25 minutes --
and the 12 seconds included the removal and reapplication of the indices by
VBA code. There were tens of thousands of records in the table, as I recall.

--

Ken Snell
<MS ACCESS MVP>

Ken,

Thank you for your suggestion. I know that indices slow down the append
and
tblMaster does have a lot of them. For this trial, however, I did have
only
primary key and the append took so long I eventually killed it.

On that matter, I assume it is more efficient to remove the indicies,
append/update the records and then rebuild the indicies, depending upon
the
number of records in tblMaster, the number of records to be
appended/updated
and the number of indicies. Is my assumption correct and is there a way to
calculate the approximate point that remove/rebuild is more efficient
than
leaving the indicies in place?

Regards

Just a guess, but often the cause of a slow append query is because the
table receiving the data has a lot of indices on the fields, and each
record
that is appended causes the indices to be updated.

Try removing all but primary key index from the table to which you're
appending the data and see if it goes much faster. You can then put the
indices back on the fields after the append query is done.

This removal and restoration of the indices can be done programmatically
as
well.

I am looking for a more efficient (read faster) way to append new records
to

[quoted text clipped - 39 lines]

Thanks and regards.

 

[Chutney via AccessMonster.com]

Ken,

Thanks for this. My tblMaster is about 1.3M records and I append about 40K
per week. Right now it has taken over 3 hours (and is still running) to apply
a relationship between tblMaster and one other table (39K records) on pre-
indexed fields! (Two other relationships, on smaller tables, took a few
minutes each - I don't know what is going on.) I can see that I will be
spending a lot of time "watching paint dry" as I try to determine the fastest
procedure.

I don't have any suggestions for determining when it's more efficient to
remove and reapply indices. I can tell you that I used that method to
accomplish an append in 12 seconds that previously was taking 25 minutes --
and the 12 seconds included the removal and reapplication of the indices by
VBA code. There were tens of thousands of records in the table, as I recall.

Ken,

[quoted text clipped - 34 lines]

 

[Van T. Dinh]

Yes. It is unique in JET AFAIK.

You can do a simple test with 2 Tables bwfore applying it to your real
database.

--
HTH
Van T. Dinh
MVP (Access)

Van,

Thank you for your reply.

1. I dearly wish I could link directly to the main-frame but unfortunately
I
cannot.

2. This is one of the tricks I have been looking for. Just to confirm I
understand correctly, this simple UPDATE...RIGHT JOIN construct performs
both
APPEND and UPDATE. Am I correct? I had never heard of that.

Regards.

1. Can you link to the Table in the main-frame rather than importing the
data into tblStaging? This will save you one time-consuming step.

2. JET is actually fairly quirky on Update Queries: If you use the
correct
join, JET actually does updates for Records in tblMaster if there exist
corresponding Records in tblStaging AND append Records into tblMaster if
there exist Records in tblStaging without corresponding Records in
tblMaster.

You need a right join in this case, something like:

UPDATE tblMaster RIGHT JOIN tblStaging
ON tblMaster.Ref_No = tblStaging.Ref_No
SET ...

I am looking for a more efficient (read faster) way to append new records
to

[quoted text clipped - 39 lines]

Thanks and regards.

 

[Chutney via AccessMonster.com]

Van,

I tried your suggestion using exactly the same wording (this is a copy of the
SQL from the Query Designer):

UPDATE tblMaster RIGHT JOIN tblStaging ON tblMaster.ref_no = tblStaging.
ref_no SET ...

(ref_no is the primary key in tblMaster. There are no indexes in tblStaging.)

It correctly updated all of the revised records but the I got the error 3
records not added "due to key violations" for the 3 new records.

Do you know why it didn't work? I am using Access 2000 (9.0.6926 SP-3)

Regards

Yes. It is unique in JET AFAIK.

You can do a simple test with 2 Tables bwfore applying it to your real
database.

Van,

[quoted text clipped - 32 lines]

 

[Van T. Dinh]

Did you include:

tblMaster.Ref_No = tblStaging.Ref_No

in the SET Clause?

I wrote previously that this is *quirky* in JET compared to other database
engines. Personally, I avoid using it. It just doesn't feel logically
sound to me.

 

[Gary Walter]

Hi Chutney,

PMFBI

You said that

" A "Find Unmatched Query" created by the Wizard runs
reasonably fast to create a list of the records to be appended."

Have you tried sending these results to a second table
(say "tblStagingAppends"), then append this table to
tblMaster?

Apologies again for butting in.

good luck,

gary

 

[Chutney via AccessMonster.com]

Van,

Yes, I SET all of the fields in tblMaster = tblStaging equivalent. For
updates, tblMaster.Ref_No = tblStaging.Ref_No is, of course, not necessary.
Should it, or should it not, be there for the APPEND function?

Regards

Did you include:

tblMaster.Ref_No = tblStaging.Ref_No

in the SET Clause?

I wrote previously that this is *quirky* in JET compared to other database
engines. Personally, I avoid using it. It just doesn't feel logically
sound to me.

Van,

[quoted text clipped - 14 lines]

Regards

 

[Gary Walter]

I was going to ask you about that...

w/o it, you would be trying to "append"
records with all Nulls for the pk, which
would raised the violation.

Does that make sense?

good luck,

gary

Van,

Yes, I SET all of the fields in tblMaster = tblStaging equivalent. For
updates, tblMaster.Ref_No = tblStaging.Ref_No is, of course, not
necessary.
Should it, or should it not, be there for the APPEND function?

Regards

Did you include:

tblMaster.Ref_No = tblStaging.Ref_No

in the SET Clause?

I wrote previously that this is *quirky* in JET compared to other database
engines. Personally, I avoid using it. It just doesn't feel logically
sound to me.

Van,

[quoted text clipped - 14 lines]

Regards

 

[Chutney via AccessMonster.com]

Gary,

Thanks for your info. I assumed that was the case. However, since the concept
of an "update" doing an "append" is outside my ken I didn't know if there was
a unique issue here. Still don't know what the requirements are for making
this happen but I welcome anything that reduces the number steps involved.

I was going to ask you about that...

w/o it, you would be trying to "append"
records with all Nulls for the pk, which
would raised the violation.

Does that make sense?

good luck,

gary

Van,

[quoted text clipped - 20 lines]

 

[Chutney via AccessMonster.com]

Gary,

No, I didn't try to use the "Find Unmatched Query" to create a table and then
append from that table. Instead, I used the "Find Unmatched Query" SQL
statements/logic inside the append query. I figured that doing it in one step
should be faster than two. Perhaps I am being too logical for Access? -|

Regards

Hi Chutney,

PMFBI

You said that

" A "Find Unmatched Query" created by the Wizard runs
reasonably fast to create a list of the records to be appended."

Have you tried sending these results to a second table
(say "tblStagingAppends"), then append this table to
tblMaster?

Apologies again for butting in.

good luck,

gary

I am looking for a more efficient (read faster) way to append new records
to

[quoted text clipped - 39 lines]

Thanks and regards.

 

[Gary Walter]

Uhmm...logical...I truly don't know...

I have a 40K table with no index
outer joined to a 1.3M table to
find the records that don't match up,
then try to append those non-indexed
records back to the 1.3M inner table.

If I were appending to any other table
besides the inner table, and I had an
index on the 40K RefNo, I think that might
be "logical."

I wish we could Debug the query step-by-step
but my (maybe wrong) intuition says it might go
something like:

look at first record in 40K table.

does its RefNo match with RefNo
of first record in 1.3M table?

If it doesn't match, I don't have a unique
index to save this record for later, so I
will have to append now (plus do some
maybe inefficient unknown-to-me things,
like rebuild indexes on 1.3M table maybe).
Then go get next record in 40K table and start
over with 1.3M table (which has changed
since append).

If it does match, go to next record
in 1.3M table. Continue checking
for match with this first record of
40K table until do not find a match
(so do process above)
or reach end of 1.3M records.

Then get next record from 40K table
and start process over.

Without an index on 40K RefNo,
I think you are looking at

~40K * 1.3M "looks"
except for the 3 records
which *may* (I don't know
for sure) mean that indexes on
1.3M table are rebuilt 3 times.

When I look at the SQL for the
virtual table, I might think that logically
here is a table of just 3 records so this
append should happen in a timely
fashion.

But because there is no way
to uniquely identify those 3 records
in the 40K record, i.e., "hold them
in my virtual hand," doing the append
means a long coffee break.

That's why I suggested what I did,
but I could be wrong......

good luck,

gary

No, I didn't try to use the "Find Unmatched Query" to create a table and
then
append from that table. Instead, I used the "Find Unmatched Query" SQL
statements/logic inside the append query. I figured that doing it in one
step
should be faster than two. Perhaps I am being too logical for Access?
-|

Regards

Hi Chutney,

PMFBI

You said that

" A "Find Unmatched Query" created by the Wizard runs
reasonably fast to create a list of the records to be appended."

Have you tried sending these results to a second table
(say "tblStagingAppends"), then append this table to
tblMaster?

Apologies again for butting in.

good luck,

gary

I am looking for a more efficient (read faster) way to append new records
to

[quoted text clipped - 39 lines]

Thanks and regards.

 

[Michel Walsh]

Hi,


With a SELECT, the table *can* read differently and more efficiently than
when the table is UPDATEd, reason being, among other, to avoid to update a
record twice, or more, mainly if you play with the primary key: imagine a
cursor (or an "internal recordset", if you prefer that picture) that is
intended to make a simple scan over the table (and without index, that is
likely the strategy that will be used). The first record is updated, but
since it is stored, physically, accordingly to its primarykey, it moves
further down in the table. A simple "cursor" moving down will revisit this
record a second time, and so on, which creates the so call "Halloween
Problem" (see: The Guru's Guide to Transact-SQL, pp108-next, as example).
So, the logic is not the same in an update than in a SELECT (where no update
is performed), mainly if the primary key can be updated, and the extra logic
*may* create a problem of speed of execution. Furthermore, it seems the join
you use create duplicated record (since in one message, you mention Jet was
returning a message telling that some records would not be updated), also a
source of ineffective disk IO. For these reasons, it sounds that the initial
solution proposed by Gary, to make a temp table, could ease the solution, in
terms of speed, by simplifying the "cursors" on (indexed) tables, rather
than on joins of tables.

The source of the problem can be something else too, and it may be the
proposed solution won't do anything... Debugging is as an art based on
science, a little bit like medicine (diagnostics through symptoms, but the
diagnostic is just one possible solution that fits the set of symptoms).


Hoping it may help,
Vanderghast, Access MVP

Gary,

No, I didn't try to use the "Find Unmatched Query" to create a table and
then
append from that table. Instead, I used the "Find Unmatched Query" SQL
statements/logic inside the append query. I figured that doing it in one
step
should be faster than two. Perhaps I am being too logical for Access?
-|

Regards

Hi Chutney,

PMFBI

You said that

" A "Find Unmatched Query" created by the Wizard runs
reasonably fast to create a list of the records to be appended."

Have you tried sending these results to a second table
(say "tblStagingAppends"), then append this table to
tblMaster?

Apologies again for butting in.

good luck,

gary

I am looking for a more efficient (read faster) way to append new records
to

[quoted text clipped - 39 lines]

Thanks and regards.

 

[Gary Walter]

Thank you Michel for looking at this.

To adapt Tolstoy's famous first
sentence in "Anna Karenina":

Fast queries are all alike;
Every slow query is slow in its own way.

I.e., in order for a query to be fast
it must succeed in many respects...
if it fails in any one of those respects,
never mind that it succeeds in all other
respects, it will be slow.

{thought robbed from Jared Diamond's
"Guns, Germs, and Steel"}

Sometimes finding the failing respect(s)
can only be done by formulating a
diagnosis based on the symptoms,
and testing possible solutions.

No offense intended and not meaning
to sound pompous....

gary

With a SELECT, the table *can* read differently and more efficiently than
when the table is UPDATEd, reason being, among other, to avoid to update a
record twice, or more, mainly if you play with the primary key: imagine a
cursor (or an "internal recordset", if you prefer that picture) that is
intended to make a simple scan over the table (and without index, that is
likely the strategy that will be used). The first record is updated, but
since it is stored, physically, accordingly to its primarykey, it moves
further down in the table. A simple "cursor" moving down will revisit this
record a second time, and so on, which creates the so call "Halloween
Problem" (see: The Guru's Guide to Transact-SQL, pp108-next, as example).
So, the logic is not the same in an update than in a SELECT (where no
update is performed), mainly if the primary key can be updated, and the
extra logic *may* create a problem of speed of execution. Furthermore, it
seems the join you use create duplicated record (since in one message, you
mention Jet was returning a message telling that some records would not be
updated), also a source of ineffective disk IO. For these reasons, it
sounds that the initial solution proposed by Gary, to make a temp table,
could ease the solution, in terms of speed, by simplifying the "cursors"
on (indexed) tables, rather than on joins of tables.

The source of the problem can be something else too, and it may be the
proposed solution won't do anything... Debugging is as an art based on
science, a little bit like medicine (diagnostics through symptoms, but the
diagnostic is just one possible solution that fits the set of symptoms).


Hoping it may help,
Vanderghast, Access MVP

Gary,

No, I didn't try to use the "Find Unmatched Query" to create a table and
then
append from that table. Instead, I used the "Find Unmatched Query" SQL
statements/logic inside the append query. I figured that doing it in one
step
should be faster than two. Perhaps I am being too logical for Access?
-|

Regards

Hi Chutney,

PMFBI

You said that

" A "Find Unmatched Query" created by the Wizard runs
reasonably fast to create a list of the records to be appended."

Have you tried sending these results to a second table
(say "tblStagingAppends"), then append this table to
tblMaster?

Apologies again for butting in.

good luck,

gary

I am looking for a more efficient (read faster) way to append new
records
to
[quoted text clipped - 39 lines]

Thanks and regards.

 

[Chutney via AccessMonster.com]

Michel and Gary,

Thank you for some very insightful considerations, not to mention the
literary references. (Good one from "Guns, Germs, and Steel". I don't
remember that statement.) I can see the logic in indexing my tblStaging
(which holds the data downloaded from the mainframe), joining to tblMaster
and then running separate queries to update and append.

I expect this will be very helpful in speeding up the operation.

Regards

Thank you Michel for looking at this.

To adapt Tolstoy's famous first
sentence in "Anna Karenina":

Fast queries are all alike;
Every slow query is slow in its own way.

I.e., in order for a query to be fast
it must succeed in many respects...
if it fails in any one of those respects,
never mind that it succeeds in all other
respects, it will be slow.

{thought robbed from Jared Diamond's
"Guns, Germs, and Steel"}

Sometimes finding the failing respect(s)
can only be done by formulating a
diagnosis based on the symptoms,
and testing possible solutions.

No offense intended and not meaning
to sound pompous....

gary

With a SELECT, the table *can* read differently and more efficiently than
when the table is UPDATEd, reason being, among other, to avoid to update a

[quoted text clipped - 61 lines]

 

[Gary Walter]

(Good one from "Guns, Germs, and Steel". I don't
remember that statement.)

<snip>

looking back it probably *was*
pompous to use that, but...

The "Guns" reference was to his
"Anna Karenina principle" (pg 157)

"Domesticable animals are all alike;
every undomesticable animal is
undomesticable in it own way."

"We tend to seek easy, single-factor
explanations of success. For most important
things, though, success actually requires
avoiding many separate possible causes of failure."


"Humans and most animal species make an
unhappy marriage, for one or more of many
possible reasons: the animal's diet, growth rate,
mating habits, disposition, tendency to panic,
and several distinct features of social organization.
Only a small percentage of wild mammal species
ended up in happy marriages with humans, by
virtue of compatibility on all those separate
counts."