questions about Public Constants

[Terrell Miller]

Two questions:

*I usually use a Globals module in my projects that has a
huge list of either global or public-level constants.

I'm wondering if there are any performance issues when
there are a lot of public variables/constants defined. I
type them all and use Variant rarely, but I'm wondering if
having a lot of String, Integer, etc. publics has a big
effect on the amount of RAM the app chews up or the speed
of the VBA environment.

*Similarly, are there any noticeable performance issues if
I have several subroutines and functions in my form
modules? I try to use only sub/function calls as much as
possible in my handlers, but in cases where a subroutine
is particular to a specific form I've kept the
sub/function on that form's module. These subroutines are
not huge, <100 lines of code each and I might have a total
of ~500 lines of code for all the subs/fxns in a given
form module.

Does this bloat the form size quite a bit or slow
loading/execution?

Thanks for your help,

Terrell

 

[Albert D. Kallal]

Two questions:

*I usually use a Globals module in my projects that has a
huge list of either global or public-level constants.

I'm wondering if there are any performance issues when
there are a lot of public variables/constants defined. I
type them all and use Variant rarely, but I'm wondering if
having a lot of String, Integer, etc. publics has a big
effect on the amount of RAM the app chews up or the speed
of the VBA environment.

Even the suggestion to avoid variants is not much applied today. (unless you
are running large loops...you will not notice the effect of variants).

You have an OBSCENE amount of processing on your computer now. I old crud
600 mhz computer running ms-access can execute
20 million vba instructions per second.

20 MILLION VBA INSTRUCTIONS per second!

Now, I was talking about the last I checked these numbers...and of course,
with a 2ghiz computer...we are key up to 100 MILLION instructions in one
second. Heck, to get 100 million instructions on a computer 15 or 20 years
ago would cost you about 10 or more million dollars.

So, lets assume you have a form "A" with 7000 lines of code to run vs form
"B" with 7 lines of code.

form A 7000 lines of code takes .00035

form B 7 lines of code takes 0.00000035

So, yes...from a technical point of view, the 2nd form has less code, and
that code will run 1000 times faster. You will save 1000 times the amount of
time. You saved 1000 times the amount of processing.

Of course, the amount of time is

0.00035 - 0.00000035 = 0.00034965

Great...we got a 2nd form that is 1000 times faster, and the number of
seconds we save is 0.00034965 of ONE second!!

*Similarly, are there any noticeable performance issues if
I have several subroutines and functions in my form
modules? I try to use only sub/function calls as much as
possible in my handlers, but in cases where a subroutine
is particular to a specific form I've kept the
sub/function on that form's module. These subroutines are
not huge, <100 lines of code each and I might have a total
of ~500 lines of code for all the subs/fxns in a given
form module.

I think you can see, that leaving the code in the eh forms, or moving the
code out to a module is going to make zero difference in terms of form load
time. There is what is called in ms-access "light weight forms", and this
are forms with NO code. However, if you have SOME code in a form, the
performance is nil if the code is in the form...or in a module.

Does this bloat the form size quite a bit or slow
loading/execution?

Sure...as mentioned...you might save 1000 times the time...but the time you
are talking about is so small as to not even be a issue.

This is also why often people wonder why using a macro to open a form in
ms-access, or using VBA to open a form in ms-access is the SAME.

Sure, VBA can execute 20 million instructions per second, and macros are
VERY slow, perhaps a only a few thousand instructions per second.

However, in both cases, VBA, or the macro can execute the command to load
the form. once the command is interpreted, in BOTH cases, the long delay
time is the form loading...and NOT the speed of the code that told the form
to open.


Fact is, our computers are insanely fast to day. however, when you compare
network bandwidth for a typical office today, or one from 10 years ago..the
network speed is about the same. In other words, your MOST precious thing
you have to worry about is data transfer TO THE form. So, our processor
increased, but network bandwidth did NOT!!!
So, while we have all the processing the world today, we DO NOT have all the
bandwidth we need, and we must VERY carefully manage the amount of data
being transferred to the form.

So, worrying about a few extra variables, or a few extra lines of code is
really moot. (you not notice the difference if you have extra variables
defined, or even if you use Variants. However, while our processors are
100's of times faster...our network speeds are not....

So, you are free to use extra vars. however, we all know how silly it would
be to download all the names into a instant bank machine AND THEN ask for
the account number. So, even a old lady sitting at the bus stop would agree
that you don't FIRST load up the bank machine with everyone's account number
and THEN ask for the account number! So, I am sure you can see how silly
it is to bind a ms-access form to a table or query, and then open the form
without some restrictions as to what record the form should load? You need
to ask the person BEFORE the form loads WHAT record to load!!! It this kind
of approach to reducing the data that a form works with that will result in
good performance (and, joking about this..I could say that the old lady at
the bus stop even understands this concept...and she is not a developer!).

So, no..you don't have to worry about a few extra variables here and there..

 

[Terrell Miller]

Thanks much, Albert, this is great advice!

-----Original Message-----


Even the suggestion to avoid variants is not much applied today. (unless you
are running large loops...you will not notice the effect of variants).

You have an OBSCENE amount of processing on your computer now. I old crud
600 mhz computer running ms-access can execute
20 million vba instructions per second.

20 MILLION VBA INSTRUCTIONS per second!

Now, I was talking about the last I checked these numbers...and of course,
with a 2ghiz computer...we are key up to 100 MILLION instructions in one
second. Heck, to get 100 million instructions on a computer 15 or 20 years
ago would cost you about 10 or more million dollars.

So, lets assume you have a form "A" with 7000 lines of code to run vs form
"B" with 7 lines of code.

form A 7000 lines of code takes .00035

form B 7 lines of code takes 0.00000035

So, yes...from a technical point of view, the 2nd form has less code, and
that code will run 1000 times faster. You will save 1000 times the amount of
time. You saved 1000 times the amount of processing.

Of course, the amount of time is

0.00035 - 0.00000035 = 0.00034965

Great...we got a 2nd form that is 1000 times faster, and the number of
seconds we save is 0.00034965 of ONE second!!


I think you can see, that leaving the code in the eh forms, or moving the
code out to a module is going to make zero difference in terms of form load
time. There is what is called in ms-access "light weight forms", and this
are forms with NO code. However, if you have SOME code in a form, the
performance is nil if the code is in the form...or in a module.

Sure...as mentioned...you might save 1000 times the time...but the time you
are talking about is so small as to not even be a issue.

This is also why often people wonder why using a macro to open a form in
ms-access, or using VBA to open a form in ms-access is the SAME.

Sure, VBA can execute 20 million instructions per second, and macros are
VERY slow, perhaps a only a few thousand instructions per second.

However, in both cases, VBA, or the macro can execute the command to load
the form. once the command is interpreted, in BOTH cases, the long delay
time is the form loading...and NOT the speed of the code that told the form
to open.


Fact is, our computers are insanely fast to day. however, when you compare
network bandwidth for a typical office today, or one from 10 years ago..the
network speed is about the same. In other words, your MOST precious thing
you have to worry about is data transfer TO THE form. So, our processor
increased, but network bandwidth did NOT!!!
So, while we have all the processing the world today, we DO NOT have all the
bandwidth we need, and we must VERY carefully manage the amount of data
being transferred to the form.

So, worrying about a few extra variables, or a few extra lines of code is
really moot. (you not notice the difference if you have extra variables
defined, or even if you use Variants. However, while our processors are
100's of times faster...our network speeds are not....

So, you are free to use extra vars. however, we all know how silly it would
be to download all the names into a instant bank machine AND THEN ask for
the account number. So, even a old lady sitting at the bus stop would agree
that you don't FIRST load up the bank machine with everyone's account number
and THEN ask for the account number! So, I am sure you can see how silly
it is to bind a ms-access form to a table or query, and then open the form
without some restrictions as to what record the form should load? You need
to ask the person BEFORE the form loads WHAT record to load!!! It this kind
of approach to reducing the data that a form works with that will result in
good performance (and, joking about this..I could say that the old lady at
the bus stop even understands this concept...and she is not a developer!).

So, no..you don't have to worry about a few extra variables here and there..

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.

 

[Larry Linson]

There is what is called in ms-access
"light weight forms", and this are forms
with NO code.

Albert -- a little sidelight on lightweight forms:

Lightweight forms were highly promoted... even some access "gurus" wrote
about significantly "faster to load". Once they were available, Michael
Groh, then Editor or one of the Tech Editors of Access Advisor magazine did
some testing. He loaded up forms with all the code their module could handle
and identical forms as lightweight forms. He found there WAS, indeed, a
difference: the lightweight forms did load faster -- a whole 5 - 15
milliseconds faster. That is, .005 - .015 seconds, on the slower computers
we used several years ago.

You hear very little from the "gurus", or from Microsoft, about "lightweight
forms" today. And, I have never seen one used in an actual Access
application (other than demo applications back when lightweight were new).

It seemed to be a great idea, until it was put to the test of practicality.

Larry Linson
Microsoft Access MVP