Improve Append Query Performance

[mmogul]

I have a union query that returns about 100,000 records. The query runs in about 20 seconds - I don't have a problem with this performance. But when I create another query that simply reads the records returned by the union query and appends them to an empty temporary table (which will be used for reporting), this append process takes about 15 minutes. I'm looking for any ideas that can improve the performance of this append process.

The sql code is effectively like this:

INSERT INTO tblTemp(F1,F2,F3)
SELECT qryUnion.F1, qryUnion.F2, qryUnion.F3
FROM qryUnion;

There are actually 19 fields written to the table (just not showing all of them to make this easier to read). The target table does not have any indexes (they are applied later).

Thanks for your help.

 

[Andrzejek]

Just a guess here, but I would (maybe....) look at the time and performance of your Select part of your Insert:

SELECT qryUnion.F1, qryUnion.F2, qryUnion.F3
FROM qryUnion

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

It would help if you provided the SQL view of the UNION query? Did you use UNION or UNION ALL?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[mmogul]

dhookom - Good thought. I checked. I did use UNION ALL. I think Andrzejek is onto something when he states to check the Select part of my statement. I now know that the UNION query is not the issue. I tried appending one of the 2 queries that comprise the UNION and still have the same issue. The query runs ok (15-20 seconds) as a Select statement but when used as an Append (INSERT), it takes about 15 minutes to append the 100,000 records.

I believe I found the culprit in the one of the queries that was part of the UNION statement. That query used some IIF statements and a dLookup in the SELECT. While this did slow the query down a bit - it was still acceptable as a simple query. But when it became an Append query, it went from slow to a crawl. I will have to figure out a way to remove these embedded lookup and iif statements.

Thanks for your help.

 

[Andrzejek]

Do you have anything else running at INSERT ?
Do you have any Data macro that captures the After Insert event and runs at INSERT?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

DLookup()s can often be replaced by a subquery. If you need some further assistance with this it might be worth creating a new thread.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016