ChrisO
Registered User.
- Local time
- Tomorrow, 04:26
- Joined
- Apr 30, 2003
- Messages
- 3,202
Cache user properties for speed.
Forward:
For far too long it has been said that Global variables lose their value on unhandled errors. Well, quite frankly, that is a load of rubbish but is still being said today. They don’t and never have under any error circumstance.
What can happen is that a VBA Quit command will Reset variables before code execution stops. The code can then run-on and execute with Reset variables.
Under the misguided pretext, that Global variables are lost on error, people start to invent unnecessary workarounds. They do not consider that a VBA Quit command Resets variables because they don’t know it happens.
So in versions, later than Access2003, we have TempVars to protect against ‘Global lose on error’ but the lose never happened on error. I have never used them but apparently TempVars can be called directly from a Query and that is a good thing. But no one seems to want to do the work to see if TempVars are Reset on a VBA Quit command. I have asked many times but no one wants to do the work to find out and post the results.
----------
But there has been an alternative to Globals being Reset on a VBA Quit command.
That alternative is to store Global information in the database properties. That has been known for a long time but there are two problems; database properties are slow to retrieve and they are not available directly in Queries.
Being available directly in Queries is not difficult to solve; write a Public Function to retrieve the property you want.
The speed angle is important though. A database property can take around 0.4 milliseconds to get. That is far too long.
What we can do is cache, or mirror, database properties in a dictionary to improve speed. If done correctly it will reduce the retrieval time to around 2 to 3 microseconds. But because times a relative that means greater than 100 times faster to get the database property from the cache, if it is indeed available in the cache. If the property is not available in the cache we attempt to get it directly from the database properties. A failure to get from either returns a Variant which can be set to whatever you choose. The default return is Empty.
To keep things simple, the dictionary (cache, mirror) Keys (Index, Name) must be limited to vbTextCompare. The database property names are case insensitive and for direct comparison so to should be the dictionary (cache, mirror) Keys. In essence then, we are emphatically associating the cache key to the database property name.
So we do the work to cache the database properties in a dictionary on Append.
When it is time for retrieval we hit the cache first or, if not found, go for the database property directly. If not available in either we return the default, Empty.
----------
As said before, a VBA Quit will Reset Global variables and that includes the dictionary object in Class clsCacheUserProperties.
However, the Prop object is re-initialised even if called after a VBA Quit command.
It is done anytime the Prop object is Nothing and is done with the line of code:-
Public Prop As New clsCacheUserProperties
What that means is that the methods of the Prop object are valid even if the dictionary object in it has been Reset. In particular, the Item method checks if the required value is available in the dictionary and, if the dictionary object has been reset, then it gets the value from the database properties.
----------
I hope the above description is clear. Another way to look at it is that we have a fast retrieval cache with a slow speed persistent backup if the cache is Reset.
----------
Attached is a small Access 2003 demo which is totally late bound.
It is far more difficult to test than it is to describe.
If you have any questions, please ask them in the appropriate forum.
Chris.
Forward:
For far too long it has been said that Global variables lose their value on unhandled errors. Well, quite frankly, that is a load of rubbish but is still being said today. They don’t and never have under any error circumstance.
What can happen is that a VBA Quit command will Reset variables before code execution stops. The code can then run-on and execute with Reset variables.
Under the misguided pretext, that Global variables are lost on error, people start to invent unnecessary workarounds. They do not consider that a VBA Quit command Resets variables because they don’t know it happens.
So in versions, later than Access2003, we have TempVars to protect against ‘Global lose on error’ but the lose never happened on error. I have never used them but apparently TempVars can be called directly from a Query and that is a good thing. But no one seems to want to do the work to see if TempVars are Reset on a VBA Quit command. I have asked many times but no one wants to do the work to find out and post the results.
----------
But there has been an alternative to Globals being Reset on a VBA Quit command.
That alternative is to store Global information in the database properties. That has been known for a long time but there are two problems; database properties are slow to retrieve and they are not available directly in Queries.
Being available directly in Queries is not difficult to solve; write a Public Function to retrieve the property you want.
The speed angle is important though. A database property can take around 0.4 milliseconds to get. That is far too long.
What we can do is cache, or mirror, database properties in a dictionary to improve speed. If done correctly it will reduce the retrieval time to around 2 to 3 microseconds. But because times a relative that means greater than 100 times faster to get the database property from the cache, if it is indeed available in the cache. If the property is not available in the cache we attempt to get it directly from the database properties. A failure to get from either returns a Variant which can be set to whatever you choose. The default return is Empty.
To keep things simple, the dictionary (cache, mirror) Keys (Index, Name) must be limited to vbTextCompare. The database property names are case insensitive and for direct comparison so to should be the dictionary (cache, mirror) Keys. In essence then, we are emphatically associating the cache key to the database property name.
So we do the work to cache the database properties in a dictionary on Append.
When it is time for retrieval we hit the cache first or, if not found, go for the database property directly. If not available in either we return the default, Empty.
----------
As said before, a VBA Quit will Reset Global variables and that includes the dictionary object in Class clsCacheUserProperties.
However, the Prop object is re-initialised even if called after a VBA Quit command.
It is done anytime the Prop object is Nothing and is done with the line of code:-
Public Prop As New clsCacheUserProperties
What that means is that the methods of the Prop object are valid even if the dictionary object in it has been Reset. In particular, the Item method checks if the required value is available in the dictionary and, if the dictionary object has been reset, then it gets the value from the database properties.
----------
I hope the above description is clear. Another way to look at it is that we have a fast retrieval cache with a slow speed persistent backup if the cache is Reset.
----------
Attached is a small Access 2003 demo which is totally late bound.
It is far more difficult to test than it is to describe.
If you have any questions, please ask them in the appropriate forum.
Chris.
