Saphirah
Active member
- Local time
- Today, 03:17
- Joined
- Apr 5, 2020
- Messages
- 163
Hey everyone,
as a programmer i always try to keep my code modular, so that i can reuse it later.
Because my Access SQL Skills are self-taught and i did not know any better, i tried to apply the same in access using subqueries.
This resulted in queries i can reuse in any part of the program. But this also created a lot of problems.
A lot of queries have calculated fields. Be it an Aggregate Function or just appending and Formatting Strings. This can of cause become really performance intensive the more entries you are dealing with.
It gets even worse when you need f.E. the Formated Customer Name from a Query in another Query. I often used a lot of subqueries.
Sometimes the depth of these Queries reach 4-5 Subqueries.
Queries might have multiple fields, but a lot of the time i only need one field.
Please correct me if i am wrong. But to my knowledge when you append a Subquery, Access calculates every field of the subquery. This means Access will calculate a lot of fields i do not even need.
Combine this with the problem from before, where you have 4-5 layers of subqueries, access needs to do a WHOLE LOT of calculations that are unnecessary.
I did multiple tests and calculating the field directly in the query where i need it is always more performant than using subqueries.
But this will prevent me from easily changing the format of f.E. the Customer Name on all Forms.
So what is the right approach here? What is the best way to keep your SQL Queries Modular but fast?
Should i maybe only create specialized Queries who output only one field?
How do you typically design your queries?
Thank you very much for your help.
as a programmer i always try to keep my code modular, so that i can reuse it later.
Because my Access SQL Skills are self-taught and i did not know any better, i tried to apply the same in access using subqueries.
This resulted in queries i can reuse in any part of the program. But this also created a lot of problems.
A lot of queries have calculated fields. Be it an Aggregate Function or just appending and Formatting Strings. This can of cause become really performance intensive the more entries you are dealing with.
It gets even worse when you need f.E. the Formated Customer Name from a Query in another Query. I often used a lot of subqueries.
Sometimes the depth of these Queries reach 4-5 Subqueries.
Queries might have multiple fields, but a lot of the time i only need one field.
Please correct me if i am wrong. But to my knowledge when you append a Subquery, Access calculates every field of the subquery. This means Access will calculate a lot of fields i do not even need.
Combine this with the problem from before, where you have 4-5 layers of subqueries, access needs to do a WHOLE LOT of calculations that are unnecessary.
I did multiple tests and calculating the field directly in the query where i need it is always more performant than using subqueries.
But this will prevent me from easily changing the format of f.E. the Customer Name on all Forms.
So what is the right approach here? What is the best way to keep your SQL Queries Modular but fast?
Should i maybe only create specialized Queries who output only one field?
How do you typically design your queries?
Thank you very much for your help.