Basing reports off of temp tables or queries? Details inside.

[ChaseC]

Hi all,



I have a small issue in my database where some of my queries take ages to run and calculate all of their fields. When I base reports off of these queries that time-lag translates to taking a long time to open the report. Is there any inherent problems with having the queries create a temp table and then basing the report off that table instead? My data only gets updated once per quarter and I will have a automatic update function that runs all of the create table queries once per quarter (after data has been entered).

I know inherently you should never store calculate fields in a table however for efficiency sake would this be a viable method?

Thanks!

[JoeM]

While not optimal, I have seen the approach you have taken used before. However, I would recommend using that as a last resort. There are a few things to consider/check to see if can be done that may help:

- Do your data tables have Primary Keys, and are the fields you are querying and linking on Indexed? Adding Indexes can help improve performance.
- Is the data stored directly in this Access database, or is the data linked in from another source? If linked in, consider using Pass Through Queries for better performance.

[RuralGuy]

IMHO there is nothing inherently wrong or bad about using temp tables. JoeM's advice is spot-on but occasionally a temp table is exactly the route to take. Just my $0.02

[ChaseC]

While not optimal, I have seen the approach you have taken used before. However, I would recommend using that as a last resort. There are a few things to consider/check to see if can be done that may help:

- Do your data tables have Primary Keys, and are the fields you are querying and linking on Indexed? Adding Indexes can help improve performance.
- Is the data stored directly in this Access database, or is the data linked in from another source? If linked in, consider using Pass Through Queries for better performance.

(1) Yes, all of my data tables have Primary Keys. I think I may have over-indexed my tables because I'm not 100% certain how the functionality works. Essentially, I indexed any field that I would ever use in a calculation or lookup, i.e., I have table containing:

• quarterlyReportID_PK (autoNumber primary key)
• portfolioID_FK (which joins each quarterly update to it's proper portfolio)
• update date
• reported current value
• reported quarterly distributions
• reported cumulative distributions
• reported quarterly cap calls
• reported cumulative cap calls

And I have them all indexed to Yes (Duplicates Ok) because all of the values are called upon in a query to generate an update report each quarter. I'm not certain if this is proper or if I should only be indexing the portfolioID.

(2) Yes, the data is stored directly in this Access database. I have plans to eventually split into front-end/back-end but as I'm still in the development stages it's all one application for now.

[JoeM]

Yes, over-indexing fields can have an adverse affect on database performance.

I typically only index fields that:
- I am going to use in a Join with another table or query
- I am going to use in Criteria for my query
- I am going to sort by the field

Indexing fields that you will be be doing calculations on seems unnecessary to me, and I think could actually have an adverse affect.

Take a look at this article for indexing tips:
http://www.tek-tips.com/faqs.cfm?fid=1752

[ChaseC]

Yes, over-indexing fields can have an adverse affect on database performance.

I typically only index fields that:
- I am going to use in a Join with another table or query
- I am going to use in Criteria for my query
- I am going to sort by the field

Indexing fields that you will be be doing calculations on seems unnecessary to me, and I think could actually have an adverse affect.

Take a look at this article for indexing tips:
http://www.tek-tips.com/faqs.cfm?fid=1752

Thanks Joe - I'll take a look. I'll update my table indexing and look through my queries for optimization possibilities and then post again if I still have the same issue.

Thanks again!

[JoeM]

As RuralGuy mentioned, there is nothing wrong with using Temp tables. Sometimes, the size of the data or the complexity of the queries may require it, for better performance.

The only caveats are:
- To make sure that you keep that data in there current whenever you want to run that particular Report, which requires deleting the existing data and re-generating fresh data.
- That you run Compact & Repair on your database regularly. Frequently deleting and adding data to tables in Access will cause database bloating, which will require you to Compact and Repair to keep the database size down, and performance optimal (deleting data is actually not purged until you do a Compact and Repair, so simply deleting the data alone does not reduce the size of your database).

[RuralGuy]

As an FYI, I create my temp tables in a separate database to isolate any bloat and then delete this database when the program closes.

[JoeM]

As an FYI, I create my temp tables in a separate database to isolate any bloat and then delete this database when the program closes.

I like it! That's a clever approach I hadn't thought of!

So, would you then link those tables into your "main" database, so you can run your Report off of them, or would you simply put your Report in the Temp database?

[RuralGuy]

The temp tables are linked to the regular database. I have a separate module whose job it is to create the db, link the tables and then unlink the tables and delete the db when requested.

[JoeM]

I have a separate module whose job it is to create the db, link the tables and then unlink the tables and delete the db when requested.

Ah, so you have some VBA code handling all those details. I am guessing if they decide to go that route, they might want to see what that code looks like.