Idea's on seeding up a report/query

Question

I am using a query that contains 3 union-ed queries. This pulls roughly 165,000 + records. This alone takes about 10 seconds at most. However, I'm filtering that data upon things which has to do a calculation of up to 10 times on each one of those records. This ends up making it extremely slow.

What are some good practices to speed up a report that will make it much faster?

I'm not able to give the exact query or the exact data.

---

Dim Blondedude092 as SkypeUser

Answer 1

Is a network involved?

If yes, then run the one query to a local temp table, and process on that – you see fantastic speed.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 2

Apply the WHERE to each of the quries before the UNION.

Answer 3

Hi Dim -

You really can’t speed up a process; it takes whatever time is necessary. Therefore, to spend less time, you must have the computer do less work. To do that, YOU must do more work figuring that out. Whose time is more valuable: yours, or the computer’s? How many times are you going to perform this process? What are you using to measure time? Does the report have to be complete, or can you deliver a page at a time? Bon voyage! ;o)

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 4

Just tossing this out there, since you are unable to post your exact query. 

Query/Report Performance Tips  (Look specifically under the Query and Report headings)

Despite the article's '2007' title, these tips apply universally.

 

---

Miriam Bizup Access MVP

Answer 5

Miriam - Thx for the link!

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 6

Great source. Thanks, Miriam!

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals