What will execute faster?

  • Thread starter Thread starter JohnC
  • Start date Start date
J

JohnC

What will execute faster?


strSQL = "UPDATE usysAppProperty "
strSQL = strSQL & "SET usysAppProperty.FIELD_VALUE = "
strSQL = strSQL & "Format(Date(),'Short Date') & ', ' & "
strSQL = strSQL & "Format(Time(),'hh:nn AM/PM') "
strSQL = strSQL & "WHERE
(((usysAppProperty.FIELD_NAME)='IMPORTCOSTACCOUNT'));

db.Execute strSQL, dbFailOnError


OR

strSQL = "UPDATE usysAppProperty "
strSQL = strSQL & "SET [FIELD_VALUE] = "
strSQL = strSQL & "Format(Date(),'Short Date') & ', ' & "
strSQL = strSQL & "Format(Time(),'hh:nn AM/PM') "
strSQL = strSQL & "WHERE [FIELD_NAME]='IMPORTCOSTACCOUNT';"

db.Execute strSQL, dbFailOnError


John C.
 
JohnC said:
What will execute faster?


strSQL = "UPDATE usysAppProperty "
strSQL = strSQL & "SET usysAppProperty.FIELD_VALUE = "
strSQL = strSQL & "Format(Date(),'Short Date') & ', ' & "
strSQL = strSQL & "Format(Time(),'hh:nn AM/PM') "
strSQL = strSQL & "WHERE
(((usysAppProperty.FIELD_NAME)='IMPORTCOSTACCOUNT'));

db.Execute strSQL, dbFailOnError


OR

strSQL = "UPDATE usysAppProperty "
strSQL = strSQL & "SET [FIELD_VALUE] = "
strSQL = strSQL & "Format(Date(),'Short Date') & ', ' & "
strSQL = strSQL & "Format(Time(),'hh:nn AM/PM') "
strSQL = strSQL & "WHERE [FIELD_NAME]='IMPORTCOSTACCOUNT';"

db.Execute strSQL, dbFailOnError


John C.
Click to expand...

I don't know, why don't you test, and tell us ;-)

Not to be rude, but I'm not sure your sample even works, as you're
converting a valid date and a valid time to string, assigning it to
a field without any delimiters, shouldn't that give RT 3075?

And, where I live,

?Format(Date, "Short Date") -> 18.08.2007

which is also a sure RT 3075 creator, you'd need more international
dates, at least if you wish your app to be able to work with
different date settings than US. You'd need for instance the
ISO 8601 "yyyy-mm-dd" or "mm\/dd\/yyyy". See for instance
http://allenbrowne.com/ser-36.html for more detailed information.

Perhaps you could be a bit more specific about what it is you really
wish to know (not sure I'd be able to answer, though)?

I'll usually use something close to your second version, where I'm
not overly concerned about speed of execution, at least not for one
column update in one row, but about readability with regards to later
maintenance. When using more than one table, I'll use one, two or
three letter alias.

The difference in speed between those two, will probably never ever
make any difference to a human operator, so unless I'm doing something
substantially more demanding, and speed of execution becomes an issue,
I'm more inclined to prioritize maintainability over unmeasurable
milliseconds gain in performance ;-)

Perhaps the following would be even faster, easier to read... and
should work regardless of locale, as long as you're operating on
a Date/Time field.

strSQL = "UPDATE usysAppProperty "
strSQL = strSQL & "SET [FIELD_VALUE] = Now() "
strSQL = strSQL & "WHERE [FIELD_NAME]='IMPORTCOSTACCOUNT'"

or with alias

strSQL = "UPDATE usysAppProperty AP "
strSQL = strSQL & "SET AP.[FIELD_VALUE] = Now() "
strSQL = strSQL & "WHERE AP.[FIELD_NAME]='IMPORTCOSTACCOUNT'"

If you're not using Date/Time field, then that's also one place
where you could probably gain performance.
 
My guess is that they would probably execute with almost no difference
(if any at all). The difference in time would probably be on the level
of nano-seconds while the SQL interpreter decides that the field belongs
to the table. Once the text statement is compiled the should be no
difference in execution speed.

Any time saved by fully qualifying the field names would probably never
be recovered by the amount of time spent typing in the fully qualified
field names.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top