Append Query Speed Advice

[Leesha]

Hi,
I have a report that requires quite a few append queries to compile the data. This works fine however it takes forever to run the report. I do not use any queries in the append query, just table data. Ther are four separate tables being used. I built it this way because I "thought" that it would run faster if it didn't have to query the data first and then do the append. I'm wondering now if this is te best way to go since the same append query set to a select query runs faster?
Leesha

[HansV]

Selecting data to a large extent takes place in memory. Appending records requires writing to disk, and that is much slower.

If you have a frontend on the local hard disk and a backend with tables on a network disk, you might consider appending to a (temporary) local table - this will generally be faster than appending to a table in the backend.

[Leesha]

Hi Hans,
That is how I have it set up. The data is being appened to the frontend on the local hard disk.
Leesha

[HansV]

I don't know how many records are involved. If it concerns tens of thousands of records, the append queries will take some time anyway.
It helps if you compact the frontend from time to time.

[Leesha]

Is there a maximum number of records that can be handled? Just wondering if that may be the issue.

[HansV]

Access can handle hundreds of thousands of records if necessary, but performance will suffer. If you have really large tables, it might be worthwhile to migrate the backend to SQL Server.

[Leesha]

You've taught me well Hans. The original data is in sql Server. I'm working that data using Access as the front end for reports etc. Since the original sql tables are part of our company software I can't actually add to or change those.

[Leesha]

I just checked. Temporary table has just shy of 1000,000 rows of data. It's billing data that spans 5 years. The formulas etc. re being run off of that vs the entire sql database which has over 10 years of billing data in it.

[HansV]

That's stretching the capabilities of Access. How often do these reports have to be run? I once had a report with lots of complicated queries, it took more than half an hour to open it. But I had to run it only twice a year, so I didn't really mind. If I'd had to run it daily, or even weekly, it would have been different.

[Leesha]

That is exactly the scenerio here. Its an annual report that is run 2-3 times a year max.

[HansV]

In that situation, perhaps you can live with the long time to generate the report...

[Leesha]

I'm fine with it for our offices. My problem is I have another user who is on the same billing software as we are and they would like to use the dashboard that I built for our agency. They are larger than us and the report just doesn't want to see to finish running. It just hangs. The table structure is identical to ours. The difference is the amount of data. So frustrated.

[HansV]

If performance really becomes an issue, you should consider using SQL Server. But how much difference that would make with a dashboard, I cannot tell.