Solved General Question: Are subqueries efficient? (1 Viewer)

Saphirah

Active member
Local time
Today, 13:46
Joined
Apr 5, 2020
Messages
163
Hello everyone,

Coming from a programming background i like having independent systems, and reusing old "functions". Same applies for SQL queries.

i created multiple subqueries to show data in the format i need. For example i have a query which shows all customers in the format [Name] & [Postcode] & [City], and in various other formats. Now my question is, when i append the subquery to a form query, and only select one field, does access have to calculate all fields or just this one?
When creating complex queries it often happens that i reuse previously created subqueries. This results in a depth of 3 or more subqueries being used inside each other.
If you have the choice, should you rather link the tables directly? Or is it fine to use the subqueries like this?

Oh and one more thing. Should i avoid using calculated fields and iif's in subqueries?

Thank you very much for your time!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:46
Joined
Feb 19, 2013
Messages
16,555
all depends on the subquery. The principle should be to apply criteria as soon as possible in your query to minimise the amount of data to be worked on this. So in principle this

SELECT *
FROM (SELECT * FROM myTable) AS complexQuery
WHERE A=123

will be less efficient

than

SELECT *
FROM (SELECT * FROM myTable WHERE A=123) AS complexQuery

however chances are the query engine would optimise the above but hope you get the idea

when i append the subquery to a form query, and only select one field, does access have to calculate all fields or just this one?
it will do all of them. The most significant factor will be indexing (and not using udf's and domain functions) - all fields which are linked on or sorted should be indexed (exception is those fields with a limited range of values such as booleans)

Overall better to connect the tables rather than using subqueries, but sometimes subqueries are the only option.

Are you aware of non standard joins? These will be more efficient than subqueries. This is a query with a subquery

Code:
SELECT A.ID, (select count("*") FROM myTable B WHERE B.ID<=A.ID) AS numRecs
FROM myTable A

The same with a non standard join

Code:
SELECT A.ID, Count(B.ID) as numrecs
FROM myTable A INNER JOIN myTable B ON B.ID<=A.ID
GROUP BY A.ID

it should be faster because the query is run once, with the subquery, it is run for each A.ID

non standard joins cannot be represented in the query builder, to create, use a standard join, then go to the sql window and change it.

not sure what you mean by using calculated fields in a subquery - if you mean your table has calculated fields which you are using then no, don't use calculated fields at all, calculated as required. If you mean doing a calculation in a subquery, that is fine in principle.
 
Last edited:

Saphirah

Active member
Local time
Today, 13:46
Joined
Apr 5, 2020
Messages
163
Thank you very much CJ, this was very insightfull :D I will mark the post as solved.

Now i know why my search tables are so slow, because it is combining too many subqueries. Let me see if this can be optimized...
 

Isaac

Lifelong Learner
Local time
Today, 05:46
Joined
Mar 14, 2017
Messages
8,738
I think you're using the term "subquery" in a very generic sense, as in, you create a query and then create a new query & join to the first one. That's not necessarily inefficient at all. It might even be more efficient, as you're limiting the amount of stuff being joined to (IF the first query is efficient to begin with)

It's correlated subqueries that are inefficient.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:46
Joined
Sep 12, 2006
Messages
15,614
In most cases, a query is better than iterating a recordset in code, though.
In general, the answer IS a query.
 

Saphirah

Active member
Local time
Today, 13:46
Joined
Apr 5, 2020
Messages
163
I think you're using the term "subquery" in a very generic sense, as in, you create a query and then create a new query & join to the first one. That's not necessarily inefficient at all. It might even be more efficient, as you're limiting the amount of stuff being joined to (IF the first query is efficient to begin with)

It's correlated subqueries that are inefficient.
Interesting... But i never use any data from the outer query, so this should be fine. Thank you for the information!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:46
Joined
Feb 19, 2002
Messages
42,981
Access (Jet/ACE) does not optimize subqueries efficiently. You would usually be better off by creating the subquery as a querydef and joining to it in the main query.

However, if the BE is SQL Server or some other RDBMS, they should be fine.
 

Users who are viewing this thread

Top Bottom