Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Control Source property
35 views
Skip to first unread message
KW
Sep 28, 2012, 12:40:41 PM9/28/12
to
I try to make it a rule to set my form and report's control source
property to a query rather than a table. I am wondering if there is
an advantage/disadvantage if the control source is a query or a select
statement.
For example,
Control Source = qryTable1
vs.
Control Source = Select * from qryTable1 WHERE status = "Y"
Are both of these acceptable in terms of performance?
property to a query rather than a table. I am wondering if there is
an advantage/disadvantage if the control source is a query or a select
statement.
For example,
Control Source = qryTable1
vs.
Control Source = Select * from qryTable1 WHERE status = "Y"
Are both of these acceptable in terms of performance?
Access Developer
Sep 28, 2012, 1:02:13 PM9/28/12
to
There's been discussion on this subject in the past, because a saved query
is "prepared" (optimized), whereas the same SQL executed directly must be
"prepared" by the db engine before executing. However, SQL used in a
property such as Record or Row Source is "prepared" and saved behind the
scenes as a temporary query. (You can see these, if you use VBA to list all
queries but they don't show up in the user interface.
On the other hand, unless you are executing and re-executing SQL on *very*
large amounts of data, most of us cannot detect much difference in actual,
real-world performance when it is neither in a saved Query nor in a
property. Thus we don't worry if we need to construct the SQL right before
executing it, thus missing out on Access' automatic preparing/optimizing.
I would want to experiment with a query as the data source for another
query, as you show, with the environment and distribution of data it will
use.
--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010
"KW" <noemailaddress@....> wrote in message
news:6nkb68l3q3lb1s6gn...@4ax.com...
is "prepared" (optimized), whereas the same SQL executed directly must be
"prepared" by the db engine before executing. However, SQL used in a
property such as Record or Row Source is "prepared" and saved behind the
scenes as a temporary query. (You can see these, if you use VBA to list all
queries but they don't show up in the user interface.
On the other hand, unless you are executing and re-executing SQL on *very*
large amounts of data, most of us cannot detect much difference in actual,
real-world performance when it is neither in a saved Query nor in a
property. Thus we don't worry if we need to construct the SQL right before
executing it, thus missing out on Access' automatic preparing/optimizing.
I would want to experiment with a query as the data source for another
query, as you show, with the environment and distribution of data it will
use.
--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010
"KW" <noemailaddress@....> wrote in message
news:6nkb68l3q3lb1s6gn...@4ax.com...
KW
Sep 28, 2012, 2:00:37 PM9/28/12
to
On Fri, 28 Sep 2012 12:02:13 -0500, "Access Developer"
<accd...@gmail.com> wrote:
>There's been discussion on this subject in the past, because a saved query
>is "prepared" (optimized), whereas the same SQL executed directly must be
>"prepared" by the db engine before executing. However, SQL used in a
>property such as Record or Row Source is "prepared" and saved behind the
>scenes as a temporary query. (You can see these, if you use VBA to list all
>queries but they don't show up in the user interface.
>
>On the other hand, unless you are executing and re-executing SQL on *very*
>large amounts of data, most of us cannot detect much difference in actual,
>real-world performance when it is neither in a saved Query nor in a
>property. Thus we don't worry if we need to construct the SQL right before
>executing it, thus missing out on Access' automatic preparing/optimizing.
>
>I would want to experiment with a query as the data source for another
>query, as you show, with the environment and distribution of data it will
>use.
Thanks Larry! Something I've been curious about for years!
<accd...@gmail.com> wrote:
>There's been discussion on this subject in the past, because a saved query
>is "prepared" (optimized), whereas the same SQL executed directly must be
>"prepared" by the db engine before executing. However, SQL used in a
>property such as Record or Row Source is "prepared" and saved behind the
>scenes as a temporary query. (You can see these, if you use VBA to list all
>queries but they don't show up in the user interface.
>
>On the other hand, unless you are executing and re-executing SQL on *very*
>large amounts of data, most of us cannot detect much difference in actual,
>real-world performance when it is neither in a saved Query nor in a
>property. Thus we don't worry if we need to construct the SQL right before
>executing it, thus missing out on Access' automatic preparing/optimizing.
>
>I would want to experiment with a query as the data source for another
>query, as you show, with the environment and distribution of data it will
>use.
Good stuff!
-kw (via pw)
Patrick Finucane
Sep 30, 2012, 7:22:17 AM9/30/12
to
0 new messages