yet another: which is faster table or query ?

[bobg.hahc]

using A2k;

seen LOTS of posts about this topic, but none seem to address the
heart of my question.

Let's break this into 2 parts: queries (stand-alone); and form data
sources (and control data sources)

queries - stand alone:
Let's say I have a query (#1) that relates information in 3 tables.
Now I want another query (#2) that uses that same information, and
relates it to more tables.

Would query #2 be faster if it uses query #1, and relates that to more
tables;
OR would it be faster if it had all the table relations in query #1
built into it (not use a query inside a query) ?
---

form (and control) data sources:
Is a form going to be faster if it references a table directly, OR if
it uses a query (select * from table) that does the same thing?

What about a control on a form? faster if it uses a query or a table?

I've been creating queries for my control data sources, and it appears
to allow forms to open faster... just want to know the truth....

TIA - Bob

 

[bobg.hahc]

using A2k;

seen LOTS of posts about this topic, but none seem to address the
heart of my question.

Let's break this into 2 parts: queries (stand-alone); and form data
sources (and control data sources)

queries - stand alone:
Let's say I have a query (#1) that relates information in 3 tables.
Now I want another query (#2) that uses that same information, and
relates it to more tables.

Would query #2 be faster if it uses query #1, and relates that to more
tables;
OR would it be faster if it had all the table relations in query #1
built into it (not use a query inside a query) ?
---

form (and control) data sources:
Is a form going to be faster if it references a table directly, OR if
it uses a query (select * from table) that does the same thing?

What about a control on a form? faster if it uses a query or a table?

I've been creating queries for my control data sources, and it appears
to allow forms to open faster... just want to know the truth....

TIA - Bob

As a followup question: what about sub-queries? Which of these 2
examples would be faster?

Let's say i've got 2 tables:
names (which contains field for first and field for last names)
fullnames (which contains a field with first and last name
concatenated with a space)

If I want to relate these tables, I could:
A) use 2 queries:
Q1 = SELECT T_names.*, [first] & " " & [last] AS full_name FROM
T_names;
Q2 = SELECT Q1.*, T_fullnames.* FROM Q1 INNER JOIN T_fullnames ON
Q1.full_name = T_fullnames.full_name;
OR
B) use 1 query with a sub-query:
Q1 = SELECT Q_names_plus.*, T_fullnames.* FROM
(SELECT T_names.*, [first] & " " & [last] AS full_name FROM
T_names) AS Q_names_plus
INNER JOIN T_fullnames ON Q_names_plus.id = T_fullnames.id;

I'm guessing that sub-queries would be slower, but they sure are A LOT
cleaner... would I be right?

 

[Michel Walsh]

Well, ... eh... in this case ... use one query, without any sub-query.



SELECT *, T_names.[first] & " " & T_names.[last] AS full_name
FROM T_fullnames INNER JOIN T_names
ON T_names.id = T_fullnames.id;


(note that technically, your two solutions were not, in general, producing
the same result, since one join on id and the other solution join on a
computed expression. So, it can be that the required solution is:

SELECT *, T_names.[first] & " " & T_names.[last] AS full_name
FROM T_fullnames INNER JOIN T_names
ON T_names.[first] & " " & T_names.[last]= T_fullnames.fullName;

if the join has to be on the fullName, rather than on the id.



In general, when you ask yourself which solution is faster... you can simply
test the alternatives and see if there is any human-perceptible difference
at all.




Vanderghast, Access MVP

using A2k;

seen LOTS of posts about this topic, but none seem to address the
heart of my question.

Let's break this into 2 parts: queries (stand-alone); and form data
sources (and control data sources)

queries - stand alone:
Let's say I have a query (#1) that relates information in 3 tables.
Now I want another query (#2) that uses that same information, and
relates it to more tables.

Would query #2 be faster if it uses query #1, and relates that to more
tables;
OR would it be faster if it had all the table relations in query #1
built into it (not use a query inside a query) ?
---

form (and control) data sources:
Is a form going to be faster if it references a table directly, OR if
it uses a query (select * from table) that does the same thing?

What about a control on a form? faster if it uses a query or a table?

I've been creating queries for my control data sources, and it appears
to allow forms to open faster... just want to know the truth....

TIA - Bob

As a followup question: what about sub-queries? Which of these 2
examples would be faster?

Let's say i've got 2 tables:
names (which contains field for first and field for last names)
fullnames (which contains a field with first and last name
concatenated with a space)

If I want to relate these tables, I could:
A) use 2 queries:
Q1 = SELECT T_names.*, [first] & " " & [last] AS full_name FROM
T_names;
Q2 = SELECT Q1.*, T_fullnames.* FROM Q1 INNER JOIN T_fullnames ON
Q1.full_name = T_fullnames.full_name;
OR
B) use 1 query with a sub-query:
Q1 = SELECT Q_names_plus.*, T_fullnames.* FROM
(SELECT T_names.*, [first] & " " & [last] AS full_name FROM
T_names) AS Q_names_plus
INNER JOIN T_fullnames ON Q_names_plus.id = T_fullnames.id;

I'm guessing that sub-queries would be slower, but they sure are A LOT
cleaner... would I be right?

 

[mopnkohe thibaut arthur]

salut je veux de l'aide

 

[bobg.hahc]

Hi Michel;

TX so much for your reply...

Granted, the examples I gave are vastly over simplified queries,
intended to communicate the concepts.
In fact, the application they represent is not able to join on the
ID's, because they differ between tables.

My question was partly intended as a "which is truly the fastest
solution" type of question.

However - your second query, (access wouldn't accept for syntactical
reasons), was a new idea to me - I didn't know you could join like
that. I will explore that further....

But on the "big picture" side of my question - all other things being
equal - do you know:
Which would be faster - 2 separate queries, or a single query with a
sub-query?
(clearly, with your example, that query would easily be fastest, but
still curious)

as it relates to a form's performance - controls may reference a table
directly, or a query -
is 1 faster than the other? (considering, time to open form, as well
as time for pulldown to access information)

I've heard conflicting arguments, especially for how to get the best
form performance....

TIA! - Bob

Well, ... eh... in this case ... use one query, without any sub-query.

SELECT *, T_names.[first] & " " & T_names.[last] AS full_name
FROM T_fullnames INNER JOIN T_names
ON T_names.id = T_fullnames.id;

(note that technically, your two solutions were not, in general, producing
the same result, since one join on id and the other solution join on a
computed expression. So, it can be that the required solution is:

SELECT *, T_names.[first] & " " & T_names.[last] AS full_name
FROM T_fullnames INNER JOIN T_names
ON T_names.[first] & " " & T_names.[last]= T_fullnames.fullName;

if the join has to be on the fullName, rather than on the id.

In general, when you ask yourself which solution is faster... you can simply
test the alternatives and see if there is any human-perceptible difference
at all.

Vanderghast, Access MVP

As a followup question: what about sub-queries? Which of these 2
examples would be faster?

Let's say i've got 2 tables:
names (which contains field for first and field for last names)
fullnames (which contains a field with first and last name
concatenated with a space)

If I want to relate these tables, I could:
A) use 2 queries:
Q1 = SELECT T_names.*, [first] & " " & [last] AS full_name FROM
T_names;
Q2 = SELECT Q1.*, T_fullnames.* FROM Q1 INNER JOIN T_fullnames ON
Q1.full_name = T_fullnames.full_name;
OR
B) use 1 query with a sub-query:
Q1 = SELECT Q_names_plus.*, T_fullnames.* FROM
(SELECT T_names.*, [first] & " " & [last] AS full_name FROM
T_names) AS Q_names_plus
INNER JOIN T_fullnames ON Q_names_plus.id = T_fullnames.id;

I'm guessing that sub-queries would be slower, but they sure are A LOT
cleaner... would I be right?

 

[bobg.hahc]

Hi Michel,

Again tx for your comments...

I can certainly see your point; i guess there is no real answer until
you look at a specific usage...
OK; I'll have to play with that in live benchmarking...

How about on a very basic note though -
limit scope to a pulldown control on a form.
Do you have an opinion on which is faster for that?
1) referencing a table directly
2) referencing a query which does a select * from that table
3) specifying the select statement directly as the source; (not
reference a saved query).

TIA - again!
Bob

 

[Michel Walsh]

Again, you are out of luck mainly if you have a WHERE clause, which you
should have in most of the cases. Let me explain: you can have the whole
table, but that could easily be slower than to pump only few records with a
nice WHERE clause. So, if you have a small table, (1) will be better, but
else, one of the other solutions WITH a WHERE clause could easily be faster.
Now, if you have a generic WHERE clause, as saved query, (2), that query can
be so generic that the optimizer could only find a poor execution plan.
Typical example:

SELECT * FROM myTable WHERE (myParam IS NULL) OR fieldName=myParam

The OR is just a killer, so if you know your parameter is not null, it could
be faster to assign "SELECT * FROM myTable WHERE fieldName=myParam" , as an
unsaved/un-compiled query, than to use the saved query with its ugly OR.
After all, today, compiling a query is a matter of milliseconds, but a poor
execution plan can differ by many seconds in comparison with a good plan,
with fresh up-to-date statistics.



Vanderghast, Access MVP

(...)

 

[bobg.hahc]

well, Michel;

again something good for me to think about!

appreciate the info!
TX!