Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Speed/Performance
60 views
Skip to first unread message
zucke...@gmail.com
May 22, 2012, 9:05:38 PM5/22/12
to
When opening a recordset, is it signficantly faster to use a saved query instead of a select statement?
I'm using Access2000 & FrontEnd/BackEnd with linked tables
Option 1:
Dim rst as DAO.Recordset
Set rst = Currentdb.OpenRecordset("Select * From tblInvoices Where Cust=" & txtCust & " Order By InvoiceNum")
Option 2:
Dim rst as DAO.Recordset
Set rst = Currentdb.OpenRecordset("qryInvoices")
(where qryInvoices is a saved query with the appropriate criteria & sort)
I'm using Access2000 & FrontEnd/BackEnd with linked tables
Option 1:
Dim rst as DAO.Recordset
Set rst = Currentdb.OpenRecordset("Select * From tblInvoices Where Cust=" & txtCust & " Order By InvoiceNum")
Option 2:
Dim rst as DAO.Recordset
Set rst = Currentdb.OpenRecordset("qryInvoices")
(where qryInvoices is a saved query with the appropriate criteria & sort)
Patrick Finucane
May 23, 2012, 9:40:27 AM5/23/12
to
On May 22, 8:05 pm, "zuckerm...@gmail.com" <zuckerm...@gmail.com>
wrote:
Significantly faster? No. Maybe if you got down to a computer's
nanosecond there'd be a huge difference. To human perception,
negligable.
wrote:
nanosecond there'd be a huge difference. To human perception,
negligable.
Bob Barrows
May 24, 2012, 6:58:20 AM5/24/12
to
course - there is likely to be no perceptible difference as Patrick says.
If the queries were more complex, it could very well take the optimizer a
lot more time to create an execution plan than it would to simply use the
cached plan that was already created for the saved query.
PS. I assume your "select *" (selstar) was for the sake of simplifying the
example. Hopefully you realize the potential for problems that can be caused
by using selstar in production code.
(PeteCresswell)
May 24, 2012, 11:52:48 AM5/24/12
to
Per zucke...@gmail.com:
But, for me, the documentation value of a well-named query has
considerable value. Saved queries also make it easier when it
comes time to make changes bc it's easier to eyeball a list of
queries than it is to go searching through all the code modules
for SQL.
--
Pete Cresswell
>When opening a recordset, is it signficantly faster to use a saved query instead of a select statement?
>I'm using Access2000 & FrontEnd/BackEnd with linked tables
Dunno if anybody could notice the diff.
>I'm using Access2000 & FrontEnd/BackEnd with linked tables
But, for me, the documentation value of a well-named query has
considerable value. Saved queries also make it easier when it
comes time to make changes bc it's easier to eyeball a list of
queries than it is to go searching through all the code modules
for SQL.
--
Pete Cresswell
Access Developer
May 24, 2012, 4:20:32 PM5/24/12
to
Just an aside: remember that, if there have been significant changes to the
input data, the query may automatically be re-optimized behind the scenes,
anyway.
On tables (or input data from other query) of up to multiple tens of
thousands of records, I've never been able to discern a difference, sitting
in front of the screen while the query or SQL runs. That doesn't mean there
are not SOME circumstances where it would be enough difference to perceive.
--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010
"Bob Barrows" <reb0...@NOSPAMyahoo.com> wrote in message
news:jpl46f$jhb$1...@dont-email.me...
input data, the query may automatically be re-optimized behind the scenes,
anyway.
On tables (or input data from other query) of up to multiple tens of
thousands of records, I've never been able to discern a difference, sitting
in front of the screen while the query or SQL runs. That doesn't mean there
are not SOME circumstances where it would be enough difference to perceive.
--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010
"Bob Barrows" <reb0...@NOSPAMyahoo.com> wrote in message
news:jpl46f$jhb$1...@dont-email.me...
Bob Barrows
May 24, 2012, 6:27:27 PM5/24/12
to
Good point. I should have said that I've never come across a case where it
made a difference, either. I was mainly speaking in hypotheticals.
Access Developer wrote:
> Just an aside: remember that, if there have been significant changes
> to the input data, the query may automatically be re-optimized behind
> the scenes, anyway.
>
> On tables (or input data from other query) of up to multiple tens of
> thousands of records, I've never been able to discern a difference,
> sitting in front of the screen while the query or SQL runs. That
> doesn't mean there are not SOME circumstances where it would be
> enough difference to perceive.
>
made a difference, either. I was mainly speaking in hypotheticals.
Access Developer wrote:
> Just an aside: remember that, if there have been significant changes
> to the input data, the query may automatically be re-optimized behind
> the scenes, anyway.
>
> On tables (or input data from other query) of up to multiple tens of
> thousands of records, I've never been able to discern a difference,
> sitting in front of the screen while the query or SQL runs. That
> doesn't mean there are not SOME circumstances where it would be
> enough difference to perceive.
>
Access Developer
May 24, 2012, 10:44:47 PM5/24/12
to
Reminds me of the first version that allowed "lightweight forms", that is,
forms without an attached class module. All the "great gurus of the Access
world" (yes, the ones whose names you remember) were touting the "faster
load times" for "lightweight modules" (They had obviously been reading the
"reviewer's guide" for that release.)
The late Michael Groh was either Editor or Tech Editor of Access Advisor at
that time, and did a test which he published. He loaded up a form with the
largest possible module (I think that was 32,767 bytes) and another with no
class module at all. He used program timers to determine the load times...
the difference, averaged across several tests, was a STAGGERING 15
milliseconds... yes, that is 0.015 seconds.
And, frankly, I never was able to determine whether having egg all over
their faces for blindly swallowing the Redmond hype was embarrassing to
those gurus or not. I never read, saw, nor heard any mention of it from any
of them. Maybe they thought, "just ignore it and it'll go away".
--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010
forms without an attached class module. All the "great gurus of the Access
world" (yes, the ones whose names you remember) were touting the "faster
load times" for "lightweight modules" (They had obviously been reading the
"reviewer's guide" for that release.)
The late Michael Groh was either Editor or Tech Editor of Access Advisor at
that time, and did a test which he published. He loaded up a form with the
largest possible module (I think that was 32,767 bytes) and another with no
class module at all. He used program timers to determine the load times...
the difference, averaged across several tests, was a STAGGERING 15
milliseconds... yes, that is 0.015 seconds.
And, frankly, I never was able to determine whether having egg all over
their faces for blindly swallowing the Redmond hype was embarrassing to
those gurus or not. I never read, saw, nor heard any mention of it from any
of them. Maybe they thought, "just ignore it and it'll go away".
--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010
"Bob Barrows" <reb0...@NOSPAMyahoo.com> wrote in message
news:jpmcjt$oun$1...@dont-email.me...
zucke...@gmail.com
Jun 4, 2012, 8:18:38 PM6/4/12
to
Bob Barrows,
could you explain further about why "Select *" is undesireable? Assuming that all of the fields are needed for the recordset purpose.
Thanks.
Gene Wirchenko
Jun 4, 2012, 10:09:19 PM6/4/12
to
On Mon, 4 Jun 2012 17:18:38 -0700 (PDT), "zucke...@gmail.com"
<zucke...@gmail.com> wrote:
[snip]
>Bob Barrows,
>could you explain further about why "Select *" is undesireable? Assuming that all of the fields are needed for the recordset purpose.
Putting on my Bob hat:
It is a fairly general bit of advice.
If the table is changed latter, is "select *" still required? It
is a bit of documentation to specify precisely the columns needed.
That stated, I use "select *" myself, but in limited
circumstances:
1) in a context where all columns will always be needed (as in a
debugging context for what is in a table).
2) when specifying all of the columns makes the code hairier
(complicated joins that are subject to change). In this case, when
the code settles down, I generally replace the "*" with the exact
columns needed. If I do not do this, it is because I have created a
cursor with the exact columns needed and I am selecting from such a
cursor.
Sincerely,
Gene Wirchenko
<zucke...@gmail.com> wrote:
[snip]
>Bob Barrows,
>could you explain further about why "Select *" is undesireable? Assuming that all of the fields are needed for the recordset purpose.
It is a fairly general bit of advice.
If the table is changed latter, is "select *" still required? It
is a bit of documentation to specify precisely the columns needed.
That stated, I use "select *" myself, but in limited
circumstances:
1) in a context where all columns will always be needed (as in a
debugging context for what is in a table).
2) when specifying all of the columns makes the code hairier
(complicated joins that are subject to change). In this case, when
the code settles down, I generally replace the "*" with the exact
columns needed. If I do not do this, it is because I have created a
cursor with the exact columns needed and I am selecting from such a
cursor.
Sincerely,
Gene Wirchenko
Bob Barrows
Jun 5, 2012, 6:45:59 AM6/5/12
to
zucke...@gmail.com wrote:
>> PS. I assume your "select *" (selstar) was for the sake of
>> simplifying the example. Hopefully you realize the potential for
>> problems that can be caused by using selstar in production code.
>
> Thank you everyone for the replies. I'll keep looking in other
> sections of my form to increase speed.
>
>> PS. I assume your "select *" (selstar) was for the sake of
>> simplifying the example. Hopefully you realize the potential for
>> problems that can be caused by using selstar in production code.
>
> Thank you everyone for the replies. I'll keep looking in other
> sections of my form to increase speed.
>
> could you explain further about why "Select *" is undesireable?
> Assuming that all of the fields are needed for the recordset purpose.
>
1. As Gene says - self-documentation
> Assuming that all of the fields are needed for the recordset purpose.
>
2. Defensive programming - using selstar says you are assuming that nobody
will change the structures of your tables in the future, not usually a wise
assumption. If new columns are added, will your code be able to use them or
will the query engine be wasting time and memory returning them needlessly.
Since you're worried about the performance impact of saved vs ad hoc
queries, one can assume you're using the performance boost of referring to
the fields in the recordset by ordinal index number rather than by name.
What happens if someone decides he'd like the fields in all the tables to be
displayed alphabetically? Do you want your code to break just because
someone reorders the columns in the tables?
3. If tables are being joined in the query, selstar guarantees that you will
ge returning the same data multiple times, once for each table in the joins.
Unless it's an outer join you should only be returning the columns used in a
join from one of the tables in the join.Why return ID twice when the two
columns are guaranteed to hold the same data?
4. You're concerned about performance - why make more work for the query
engine? Using selstar forces the engine to access the table twice: once to
get the column names, and then to get the data. Yes, it's not likely to have
a noticeable effect on performance, but it is there. Executing a query from
an external application will exacerbate this.
One time selstar is "acceptable" in production code is in an EXISTS
subquery: WHERE EXISTS (select * from sometable where <some conditions
apply>). Most database engines will optimize that construction
appropriately, given that it's not really returning any data when EXISTS is
used.
Another is when using Count(*) to allow counting rows where columns contain
Nulls. If col contains Nulls in some rows, Count(*) will count all the rows;
Count(col) will only count the rows where col contains non-Null values.
Gene Wirchenko
Jun 5, 2012, 12:21:57 PM6/5/12
to
On Tue, 5 Jun 2012 06:45:59 -0400, "Bob Barrows"
<reb0...@NOSPAMyahoo.com> wrote:
>zucke...@gmail.com wrote:
[snip]
>> could you explain further about why "Select *" is undesireable?
>> Assuming that all of the fields are needed for the recordset purpose.
>>
>
>1. As Gene says - self-documentation
>2. Defensive programming - using selstar says you are assuming that nobody
>will change the structures of your tables in the future, not usually a wise
>assumption. If new columns are added, will your code be able to use them or
I just remembered something else that can get messed up here.
SQL is stupid about how it handles joins where the two tables in
the join each has a column of the same name. Suppose this is not the
case originally, but then someone adds a column to one of the tables
and it has the same name as a column in the other table being joined,
now there is an ambiguity and the code will break. (To avoid this,
you could make sure to specify all columns with <table>.<column>, and
you probably should.)
[snip]
Sincerely,
Gene Wirchenko
<reb0...@NOSPAMyahoo.com> wrote:
>zucke...@gmail.com wrote:
[snip]
>> could you explain further about why "Select *" is undesireable?
>> Assuming that all of the fields are needed for the recordset purpose.
>>
>
>1. As Gene says - self-documentation
>2. Defensive programming - using selstar says you are assuming that nobody
>will change the structures of your tables in the future, not usually a wise
>assumption. If new columns are added, will your code be able to use them or
SQL is stupid about how it handles joins where the two tables in
the join each has a column of the same name. Suppose this is not the
case originally, but then someone adds a column to one of the tables
and it has the same name as a column in the other table being joined,
now there is an ambiguity and the code will break. (To avoid this,
you could make sure to specify all columns with <table>.<column>, and
you probably should.)
[snip]
Sincerely,
Gene Wirchenko
Patrick Finucane
Jun 5, 2012, 12:28:25 PM6/5/12
to
Which is faster, calling a function or calling an IIF() from hell in a
query
TF :
IIF(True,True,IIF(True,True,IIF(True,True,IIF(True,True,False)))))
or
TF:ReturnTF(True,True,True,True,True)
Personally, I prefer a function call since it is easier to read and
debug. Plus, the Builder's font has always been a PITA if your
eyesight isn't that of a buzzard.
Gene Wirchenko
Jun 5, 2012, 2:02:55 PM6/5/12
to
Visual FoxPro, I came up with a function icase(). It took a variable
number of parameters:
parameter pair (0 or more of these)
parm 1: the condition
parm 2: the value to return if the condition is true
maybe a final odd parameter
and go sequentially through the pairs. If none of the pair conditions
matched, then if there were an odd parameter, return its value;
otherwise return null. Example:
icase(a>0,"positive",a<0,"negative","zero")
Some years later, Microsoft added an icase() to VFP.
Sincerelyk,
Gene Wirchenko
zucke...@gmail.com
Jun 5, 2012, 7:42:01 PM6/5/12
to
Great explanations!
Fred
0 new messages