Optimizing complex queries and reports - Emmanuel Katto

[emmanuelkatto23]

Hi All, I am Emmanuel Katto from Uganda. I'm currently working on a database project in Microsoft Access and facing some challenges with optimizing complex queries and reports. My database involves multiple related tables with a considerable amount of data. I need to generate detailed reports that require joining several tables and applying complex criteria. Some queries are running slowly, especially when generating reports with aggregated data.

What are the most efficient ways to handle data aggregation in queries and reports? Are there any specific functions or methods in Access that are particularly useful for this purpose?

Thanks!
Emmanuel Katto

 

[ebs17]

tables with a considerable amount of data

This is where the art of the database developer begins. The processor can handle small amounts and cover up any built-in weaknesses.

Tasks:
- Reduce the amount of data as quickly as possible. Lots of data = lots of work = lots of time needed, this connection will quickly become clear.
- Have and use indexes.
- Queries can be formulated in different ways. Find optimal formulations.
If you use an active DBMS as a backend, let it work for you with its usually better performance (link views instead of tables).

Since you're talking about aggregations: They exist in connection with grouping. It makes a big difference whether you group using two indexed fields or a huge field list. Grouping is a comparative operation across all the bytes entered, and that in a query for each record against each record.

 

[CJ_London]

This link lists reasons for slow performance - do any apply to you?

C

Thread 'addressing performance issues'

Jan 3, 2017

As your db expands and there are more active users, it is quite possible you will experience performance issues and may be considering upsizing to SQL Server or MySQL.

Before considering such a transition, you should review your current design:

Overview
From a performance perspective, Access should be considered to be more like a web application than an Excel - and should be designed with the same considerations in mind - primarily to minimise network traffic.


Tables
1. are the tables properly normalised? If not, queries will be more convoluted and...

• CJ_London
• Replies: 18
• Forum: Theory and practice of database design

• 

 

[mike60smart]

Hi All, I am Emmanuel Katto from Uganda. I'm currently working on a database project in Microsoft Access and facing some challenges with optimizing complex queries and reports. My database involves multiple related tables with a considerable amount of data. I need to generate detailed reports that require joining several tables and applying complex criteria. Some queries are running slowly, especially when generating reports with aggregated data.

What are the most efficient ways to handle data aggregation in queries and reports? Are there any specific functions or methods in Access that are particularly useful for this purpose?

Thanks!
Emmanuel Katto

Hi
Can you upload an example of the problems you are having in a stripped down database?

 

[Pat Hartman]

What are the most efficient ways to handle data aggregation in queries and reports?

It starts with looking at the tables and relationships and ensuring that all foreign keys are indexed. Then we need to look at the aggregation queries and how you are joining to them. Hopefully you are not using domain functions. And hopefully you are not using HAVING clauses when you should be using WHERE to control what data is selected.

You also need to do regular maintenance on the BE database. Access, like all relational databases keeps statistical information regarding row counts, etc. It is important to run C&R on the BE at least monthly to ensure that Access has accurate statistics because it needs to use these when determining an execution plan.

As the others have suggested, we can be more helpful if you can upload the BE. If you need to obfuscate the data, that is fine, we don't realy care what the data is, only that we have something similar to what you have. Of course your queries are in the FE so it might just be easier to upload both.

 

[gemma-the-husky]

Sometimes it can make things easier if you create a temporary table part way through a complex process, then continue with that table.

 

[Pat Hartman]

I don't use temp tables due to the bloat they cause. If you decide to go that route, I suggest using a separate database to hold the temp data. That way you get around the bloat by simply creating a new, empty database (with the same name) every time you need to rebuild the temp table. As long as you keep the same db name and table name, your links won't break.

 

[ebs17]

complex queries

Before using temporary tables, you should first look at the queries. A blanket statement "complex" is meaningless. For some, complexity occurs when more than two tables are linked or the total length of the SQL statement exceeds 35 characters.