Solved How to efficiently design a modular query (1 Viewer)

Saphirah

Member
Local time
Today, 06:30
Joined
Apr 5, 2020
Messages
131
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.
 

Minty

AWF VIP
Local time
Today, 05:30
Joined
Jul 26, 2013
Messages
8,600
Generally speaking, nesting queries is quite a dangerous means to an end, but sometimes inevitable in Access.
It is certainly frowned upon in SQL server as the execution plans aren't always optimised, so performance suffers.

So modularising them probably isn't the way to go, and re-writing them on the fly won't get you good performance.

As you have also discovered performing calculations should always be done in the last possible step, normally, as you have the smallest dataset (the final one) to work with.

As I work almost entirely with SQL server backends I let that do all the heavy lifting and create views for any complex queries. I also avoid custom functions in queries where possible, as either, they are massively inefficient in a SQL backend environment, or they can be better placed on the server.

Perhaps others can chime in with more Access BE suggestions.
 

Saphirah

Member
Local time
Today, 06:30
Joined
Apr 5, 2020
Messages
131
Generally speaking, nesting queries is quite a dangerous means to an end, but sometimes inevitable in Access.
It is certainly frowned upon in SQL server as the execution plans aren't always optimised, so performance suffers.

So modularising them probably isn't the way to go, and re-writing them on the fly won't get you good performance.

As you have also discovered performing calculations should always be done in the last possible step, normally, as you have the smallest dataset (the final one) to work with.

As I work almost entirely with SQL server backends I let that do all the heavy lifting and create views for any complex queries. I also avoid custom functions in queries where possible, as either, they are massively inefficient in a SQL backend environment, or they can be better placed on the server.

Perhaps others can chime in with more Access BE suggestions.
Yes, this fits with my current knowledge. I also avoid custom functions, that is a good point!
The question is now, how do you manage your queries. Are you not using any modularity at all?
So for my example are you rewriting the customer name in every query? What if you want to change the layout of the customer name globally?
Are you editing every query by hand?
 

Minty

AWF VIP
Local time
Today, 05:30
Joined
Jul 26, 2013
Messages
8,600
No, I would have the customer name as a parameter and use a parameterised query object.
Or if it was a complicated query for a form or report recordsource I would build it in VBA with the parameters extracted from the loading form and then set the forms recordsouce on load. It's whatever takes your fancy really at the time.

I very rarely have to make global changes to field definitions or names, and if I do I use some tools to perform a global search and replace.
I have created many queries that work on temp tables rather than other queries where the sub-query performance was impractically slow.

In most cases, however, I pass those type of queries back to SQL Server and use that to return a specific set of data.
I frequently use tables to store parameters and user selection lists, then link them back to queries for efficiency.
 

Saphirah

Member
Local time
Today, 06:30
Joined
Apr 5, 2020
Messages
131
Okay. So if the performance of subqueries gets too bad one can use temp tables to cache data, so it does not need to be recalculated.
That is a great idea!

What tools do you use for a global search and replace?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 14:30
Joined
Jan 20, 2009
Messages
12,327
Like Minty I mostly work in SQL Server. I have a small number of summary Views that I sometimes tack onto queries to add extra information through simple joins. For example, various things about the value of the total customer, information about accounts, loans, securities etc. Generally it will have a single join on the CustomerID or AccoutId etc. This works fine but I write the core query from scratch, sometimes by pasting parts from another query.

I don't think the situation with building queries on views is always quite as bad as Minty might suggest. It was frowned on more years ago than it is now but it really depends a lot on the nature of the view and the complexity of the nested structure. It works fine if it is simple enough. SQL server is quite capable of not bothering to do some of the work in a subquery if the columns involved are not in the outer query. I don't know about Access though. I don' think its optimiser is quite as adept.

It is definitely better to avoid functions in Access if the process can be done fully in the engine. The call out to the function is slow. However SQL Server has the ability to include compiled custom functions written in .NET. These can be very efficient.

In any database it is vitally important to think in terms of the whole data set rather than processing sequentially. Avoid stepping through recordsets wherever the job can be done as a whole.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 00:30
Joined
Feb 19, 2002
Messages
32,221
I use nested queries frequently. I generally use a base query that includes any calculated/formatted values that do not make the query not updateable along with all the standard fields. My tables rarely have more than 20 fields so more likely than not, I'' include all the columns.. I create other queries based on the base that do things I commonly need in a report such as calculate the customer's open balance etc.
 
Last edited:

Isaac

Lifelong Learner
Local time
Yesterday, 21:30
Joined
Mar 14, 2017
Messages
5,380
Generally speaking, nesting queries is quite a dangerous means to an end, but sometimes inevitable in Access.
It is certainly frowned upon in SQL server
I assume (hope?) your comment was mostly intended to pertain to correlated subqueries.

Not necessarily just something like this:

select * from table1 inner join (select query here) q on table1.something=q.something
 

Minty

AWF VIP
Local time
Today, 05:30
Joined
Jul 26, 2013
Messages
8,600
I assume (hope?) your comment was mostly intended to pertain to correlated subqueries.

Not necessarily just something like this:

select * from table1 inner join (select query here) q on table1.something=q.something
@Isaac - Yes - sorry I have realised I didn't phrase that very well.
An inline subquery as per above will be handled well by the query optimiser in SQL server.
 

arnelgp

once i caught a fish alive...
Local time
Today, 12:30
Joined
May 7, 2009
Messages
13,192
An inline subquery as per above will be handled well by the query optimiser in SQL server.
the OP is partifularly using MarianDb server.
 

Users who are viewing this thread

Top Bottom