Best practice questions for query speed (1 Viewer)

Tskutnik

Registered User.
Local time
Yesterday, 20:34
Joined
Sep 15, 2012
Messages
228
All, Here are 2 best practice question for sequencing queries.

Question 1)
Is it best to create queries off queries so they run in sequence (Flow #1 below) or "Parallel" queries from the same source and then merge them later?
The logic in Union Query 1 & 2 is similar but not the same, and not complex, so (basically) replicating logic in Queries 1 & 2 is not an issue

This is purely a general best practice and speed question.

Flow #1
1585482319469.png


FLOW #2
1585482353918.png



Question 2
is it generally better to create a series of "smaller" queries that run in sequence or one "larger" and more complex query?
This is more of a straight question of speed.


As always, thanks. Your help is always appreciated
 

Attachments

  • 1585482092787.png
    1585482092787.png
    3.1 KB · Views: 58

CJ_London

Super Moderator
Staff member
Local time
Today, 01:34
Joined
Feb 19, 2013
Messages
13,245
Don't think there is a real answer - every query is different and other factors come into play such as indexing (or lack of), use of domain and udf's. Further the first time a query is run a plan is created which is 'optimised' based on the data volumes at the time - if that data changes that optimised plan may not be optimal.

For example you have developed a query which includes a table with 20 records. Down the line the number of records grows to several thousand - the plan may no longer be optimal.
 

Tskutnik

Registered User.
Local time
Yesterday, 20:34
Joined
Sep 15, 2012
Messages
228
CJ - thanks.
Not sure if this helps, but... for my application the table data will grow each day as it is accounting data.

Appreciate the help. I really want to learn how to do this the right way.

I'll try to find some articles to tell me how/when performance is impacted.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:34
Joined
Feb 19, 2013
Messages
13,245
I would point out that union queries are generally not efficient and imply a sub optimal table design. The reason they are not efficient is because they can lose the benefit of indexing (not sure if you use the All clause since that returns records without grouping, something to test). Occasionally they can't be avoided, but if you are say recording stock movements and have one table for goods in (such as supplier good in notes) and another for goods out (such as sales invoices), then your design is sub optimal since these transactions can be in the same table

My personal preference is to use queries created in VBA and executed or assigned to a form/report recordset - each time the query is run a plan is created. OK it takes a fraction of a second to create each time but overall it keeps the query 'fresh'.
 

Tskutnik

Registered User.
Local time
Yesterday, 20:34
Joined
Sep 15, 2012
Messages
228
Unfortunately I'm stuck with a few union queries because of the source data structures - which are not optimized.
I'm using the ALL clause to simply merge all related records in one query result, the writing the logic from there.
I'm not a VBA guy so that part I'm a bit lost on. If your general point is to run the queries when a form/report is requested - I Agree. That is my design. I'm trying not to create queries that then store the results... I'd rather all the results are created when they are needed.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 20:34
Joined
Jan 23, 2006
Messages
13,839
I'm not aware of best practice, but the links I provided in post #2, the posts from CJ and some subquery info 1 and 2 from Allen Browne and Google should get you some practical guides.
Sometimes you have to take a sample and test it using different approaches and see what the speed difference is --then see if you can generalize the finding.
Samples re query speed -trial and error
-Top N
-Cartesian
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:34
Joined
Feb 28, 2001
Messages
20,660
When considering a query combination that is presented as such, remember that there is something going on behind the scenes. Here is a discussion of the steps in execution of a SELECT query, which seems relevant to your question.


My take is that even for layered queries, we have to remember that NOTHING exists (except the SQL statement and possibly a query plan) until the query is opened. Considering the article, the first thing to be evaluated is the FROM and JOIN clauses. If you use layering of queries, I would think that a recursive evaluation would be involved such that you have to eventually visit every query in the chain of queries no matter how you layer them. SQL will need to evaluate ALL of its data sources so that it can gather the data to be able to apply the next step, which is WHERE clauses.

Therefore, I would think that the difference in speed between case 1 and case 2 would be less than you might have wanted. I believe that what gets you the best result is judicious use of indexing and trying to use the shortest keys possible (i.e. index on a LONG rather than indexing on an eight-byte string when joining). The faster that you can reduce the size of the total data set, the better.
 

ebs17

Member
Local time
Today, 02:34
Joined
Feb 7, 2020
Messages
71
is it generally better to create a series of "smaller" queries that run in sequence or one "larger" and more complex query?
Here are a few points:

- A query contains no data, but only the definition of how data is to be retrieved from tables and processed. Therefore, a compound query does practically the same thing as when a query executes a saved query and does so in series.

- Index performance is an essential point for performance. Any use of the index ends with a UNION query in subsequent use. This is also why UNION queries are a rather bad choice.

- Amount of data does work. Therefore, you should reduce the amount of data as early as possible before more complex processing takes place. Example: A JOIN has about three times the weight of a filter (WHERE clause). Therefore, it makes sense to first filter and thus reduce tables before JOIN record duplication or other complex actions (grouping, integration of external objects such as recordsets via function) take place.

So it is not a question of whether a composite query or a series of individual queries, but the question of the order in which the individual steps are carried out.

A composite query would give me an overview of what is being done. You can see superfluous actions such as sorting in intermediate steps or the use of superfluous fields.
 

namliam

The Mailman - AWF VIP
Local time
Today, 02:34
Joined
Aug 11, 2003
Messages
11,696
General rule of thumb, never duplicate logic. Even if its slightly different in the base it is the same then use one query.

Using union query's too often is a sure sign of bad design, either in tables or in queries.
 

isladogs

CID VIP
Local time
Today, 01:34
Joined
Jan 14, 2017
Messages
15,281
I would also advise against using union queries. There is usually a better approach.

Here's another article from my website that may be useful regarding best practice: Optimise Queries
 

Users who are viewing this thread

Top Bottom