Globals and speed

[bkdobs]

After reading the Access 2003 Bible I have been attempting to speed up a generic global set/get module. What is frustrating is that there appears to be no way to quickly do a get/set other than create a unique set/get function for each global.

I have an application with 32 globals ... I think I am stuck on wanting to simplify code and having 64 functions instead of 2 seems a bit much. Am I missing something ie arrays, Queries (understanding that 64 functions are probably the fastest solution)?

In another db language there are indexable Temp Tables (only in memory) which can be accessed to the extreme ... haven't found a way to do this in Access.

Did some speed tests to quantify some of the suggestions in chapter 38 ...

function setglb(ch_fld as string va_val as variant)
function getglb(ch_fld as string) as variant

With 32 Globals;

Using Case statements a set/get on the 30th fld called 1,000,000 takes 15 seconds

Using IF Elseif took 14 seconds (not a great improvement)

function setfld(va_val as variant)
function getfld() as variant

Using setfld/getfld 1,000,000 set/get's took <= 1 second

Trying a table with a DAO query took forever
using FindFirst

 

[bkdobs]

Anyone reading this may have wondered WHY I don't just define the variables GLOBAL or PUBLIC and access them directly ... two reasons ... one is that for whatever reason Queries do not appear to be able to resolve variables but they can resolve SUB{}'s ... second is that without knowing the state of data being stored/retrieved Access is not forgiving so all data must be qualified (error free).

Over the last few days I have been experimenting with different constructs basically proving chapter 38's (ACCESS 2003 BIBLE) findings.

Total shock over IIF() being soooo slow ... this is one of the most basic functions at ASSEMBLER level ... <TEST> BRANCH NZ,TRUE Value, False (BRANCH) Value.

Not sure why CASE is slower then IF ELSEIF either although it is very subtle as opposed to the difference between IIF and IF

What I have resolved to do is to eliminate my global set/get modules change all globals to be "GLOBAL" so I can access them directly from anywhere without a "sub()"
for those variables that need to be used by Queries I will add Unique calls and use one line returns;

public sub getMyVariable(l_NotNull as Variant) AS Variant
IF isNull(g_MyVariable) THEN getMyVariable = l_NotNull ELSE getMyVariable = g_MyVariable
end sub()

I did some comparison's on calling and not calling functions ... obviously calling has stack overhead which will slow down a process ... was just curious on how much time VBA spent in call overhead ... its probably not scientific but a call vs no call over 10M iterations was over 95% for these types of simple function

I also concluded that using the built in error trapping is also an issue with speed ... if we know what type of error can occur it is better to test for the error then use the onerror processing ... I received a +50% improvement in speed over 10M iterations using explicit vs VBA error processing.