INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Improve Append Query Performance

Improve Append Query Performance

(OP)
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:

CODE -->

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.

RE: Improve Append Query Performance

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

CODE

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Improve Append Query Performance

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

RE: Improve Append Query Performance

(OP)
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.

RE: Improve Append Query Performance

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.

RE: Improve Append Query Performance

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close