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: 22
• 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?

 

[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.

 

[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.