Very slow append query

  • Thread starter Thread starter Bill Murphy
  • Start date Start date
B

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
 
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,

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

Thanks.

Bill


Ken Snell said:
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>

Bill Murphy said:
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
Click to expand...
Click to expand...
 
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>



Bill Murphy said:
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


Ken Snell said:
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>

Bill Murphy said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top