Very slow append query

[Bill Murphy]

In an Access 2000 append query I am appending about 850 records from a temp
table into another table which contains 250,000 records, both in a backend
mdb. I'm checking for duplicates using a date field and an account number
field, both of which are indexed in the temp and in the permanent table.
This query is taking several minutes to run, and I expected it to take
several seconds. For testing I changed this query to a select query with an
equal join on the two fields, and it ran in about two seconds. Here's the
append query:

INSERT INTO tblBalances ( BatchID, [Date], BankABANumber, Name, Account,
DebitAmount, NumberDebits, CreditAmount, NumberCredits, LedgerBalance,
CollectedBalance, FileName, BankNumber )
SELECT [forms]![frmMain]![frmReportDataContainer].[form]![txtBatchID] AS
BatchID, tblBalancesFWTemp.Date, tblBalancesFWTemp.BankABANumber,
tblBalancesFWTemp.Name, tblBalancesFWTemp.Account,
tblBalancesFWTemp.DebitAmount, tblBalancesFWTemp.NumberDebits,
tblBalancesFWTemp.CreditAmount, tblBalancesFWTemp.NumberCredits,
tblBalancesFWTemp.LedgerBalance, tblBalancesFWTemp.CollectedBalance,
tblBalancesFWTemp.FileName, tblBalancesFWTemp.BankNumber
FROM tblBalancesFWTemp LEFT JOIN tblBalances ON (tblBalancesFWTemp.Account =
tblBalances.Account) AND (tblBalancesFWTemp.Date = tblBalances.Date)
WHERE (((tblBalances.Date) Is Null) AND ((tblBalances.Account) Is Null));

Any ideas on why it's running so slowly would be appreciated.

Bill

 

[Ken Snell [MVP]]

Does tblBalances have a lot of indices in it? Indices can greatly slow down
the appending of data to a table, as the index must be revised for each new
record.

 

[Bill Murphy]

Ken,

Yes it does. I'll see if any can be removed. Also, I need to do some
archiving of records in tblBalances.

Thanks.

Bill

Does tblBalances have a lot of indices in it? Indices can greatly slow down
the appending of data to a table, as the index must be revised for each new
record.

--

Ken Snell
<MS ACCESS MVP>

In an Access 2000 append query I am appending about 850 records from a
temp
table into another table which contains 250,000 records, both in a backend
mdb. I'm checking for duplicates using a date field and an account number
field, both of which are indexed in the temp and in the permanent table.
This query is taking several minutes to run, and I expected it to take
several seconds. For testing I changed this query to a select query with
an
equal join on the two fields, and it ran in about two seconds. Here's the
append query:

INSERT INTO tblBalances ( BatchID, [Date], BankABANumber, Name, Account,
DebitAmount, NumberDebits, CreditAmount, NumberCredits, LedgerBalance,
CollectedBalance, FileName, BankNumber )
SELECT [forms]![frmMain]![frmReportDataContainer].[form]![txtBatchID] AS
BatchID, tblBalancesFWTemp.Date, tblBalancesFWTemp.BankABANumber,
tblBalancesFWTemp.Name, tblBalancesFWTemp.Account,
tblBalancesFWTemp.DebitAmount, tblBalancesFWTemp.NumberDebits,
tblBalancesFWTemp.CreditAmount, tblBalancesFWTemp.NumberCredits,
tblBalancesFWTemp.LedgerBalance, tblBalancesFWTemp.CollectedBalance,
tblBalancesFWTemp.FileName, tblBalancesFWTemp.BankNumber
FROM tblBalancesFWTemp LEFT JOIN tblBalances ON (tblBalancesFWTemp.Account
=
tblBalances.Account) AND (tblBalancesFWTemp.Date = tblBalances.Date)
WHERE (((tblBalances.Date) Is Null) AND ((tblBalances.Account) Is Null));

Any ideas on why it's running so slowly would be appreciated.

Bill

 

[Ken Snell [MVP]]

If you are doing the appending via code, it's possible to remove the indices
via code (store them in a table so that you can restore them), run the
append query, then restore the indices using the values from the table.

I wrote some code to do this in a work-related application about 1.5 years
ago -- if desired, I can probably find it in my "archives".
--

Ken Snell
<MS ACCESS MVP>

Ken,

Yes it does. I'll see if any can be removed. Also, I need to do some
archiving of records in tblBalances.

Thanks.

Bill

Does tblBalances have a lot of indices in it? Indices can greatly slow down
the appending of data to a table, as the index must be revised for each new
record.

--

Ken Snell
<MS ACCESS MVP>

In an Access 2000 append query I am appending about 850 records from a
temp
table into another table which contains 250,000 records, both in a backend
mdb. I'm checking for duplicates using a date field and an account number
field, both of which are indexed in the temp and in the permanent
table.
This query is taking several minutes to run, and I expected it to take
several seconds. For testing I changed this query to a select query with
an
equal join on the two fields, and it ran in about two seconds. Here's the
append query:

INSERT INTO tblBalances ( BatchID, [Date], BankABANumber, Name,
Account,
DebitAmount, NumberDebits, CreditAmount, NumberCredits, LedgerBalance,
CollectedBalance, FileName, BankNumber )
SELECT [forms]![frmMain]![frmReportDataContainer].[form]![txtBatchID]
AS
BatchID, tblBalancesFWTemp.Date, tblBalancesFWTemp.BankABANumber,
tblBalancesFWTemp.Name, tblBalancesFWTemp.Account,
tblBalancesFWTemp.DebitAmount, tblBalancesFWTemp.NumberDebits,
tblBalancesFWTemp.CreditAmount, tblBalancesFWTemp.NumberCredits,
tblBalancesFWTemp.LedgerBalance, tblBalancesFWTemp.CollectedBalance,
tblBalancesFWTemp.FileName, tblBalancesFWTemp.BankNumber
FROM tblBalancesFWTemp LEFT JOIN tblBalances ON (tblBalancesFWTemp.Account
=
tblBalances.Account) AND (tblBalancesFWTemp.Date = tblBalances.Date)
WHERE (((tblBalances.Date) Is Null) AND ((tblBalances.Account) Is Null));

Any ideas on why it's running so slowly would be appreciated.

Bill