[AccessD] Performance tips anyone?

This is starting to degrade into a full blown bound/unbound debate.  There's no point.  There is a time and a place for both.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Monday, July 09, 2007 10:02 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Performance tips anyone?

Drew:  I'm familiar with the 'use at your own risk' 'AddressOf' function 
since the days of Access97 at 'The Access Web' of Dev Ashish, but this is 
not the sense in which most Access programmers ordinarily are familiar with 
callback functions.  Of couse the whole callback thing has little bearing on 
the initial question about performance tips or on the question of bound or 
unbound.  I still haven't seen an explanation of how unbound is inherently 
faster than bound.  I've heard discussion about being able to do certain 
kinds of things unbound that can't be done bound and have given a couple 
examples myslef.  I still don't see how it provides additional performance 
so don't understand in what sense proposing it is an answer to the question. 
  There is also little sense in making the assumption that everyone is a 
good developer and is aware of the issues that may come with losing record 
locking.  It is irresponsible to say that jet looks after record locking, no 
difference, bound or unbound.  You can't advocate something that will 
undoubtedly hurt people if you don't mention the caveats.

What constitutes 'good developing' depends on a number of factors.  In terms 
of performance, a degree of denormalization may be good thing as it can cut 
down the number of joins to be processed.  In the database from which I drew 
the contract value/address example, we never had identical addresses for 
multiple projects.  Sure we have addresses for various kinds of entities and 
they could be stored in a single table, but placing all the addresses in a 
separate table would have resulted in a table with a much larger number of 
records that would have to be hit to populate a record fore each kind of 
entity.  Querying for any kind of related record by address, that would be 
possible by normalizing, was of no advantage in this database.  Worse yet, 
the physical address of a project never changes, but if you join that 
address to a company and some bright bulb changes the address of the company 
when it moves rather than adding a new address and 'deactivating' or date 
ranging the validity of the address, the edit to the address changes the 
AddressOf (pun intended) the project.  In the example I gave, both the 
contract value and the addresses were appropriate characteristics of the 
project.

I've also gotten performance gains out of mapping data to bit flags; another 
normalization no no.

Callback functions are very useful to me in cases where I do use foreign 
keys as I frequently fill a combo or list from an array and avoid a hit on 
the database.

In addition to what Colby calls JIT sub forms, together with limiting the 
data to only needed data, you can do the same thing with combos, displaying 
a text value in a delimited list and converting to an SQL rowsource on 
GotFocus provided you store the text value in the table together with the 
FK.

One of the slower built in functions in Access is the CurrentDb function and 
it can crawl when called in a loop.  I use a function that keeps a static 
variable that points to a database object and returns a reference to that 
object.  The database for which this question was asked is large in terms of 
numbers of objects.  CurrentDb forces a refresh of all the database objects 
so I use an optional parameter that allows me to specify should I need to 
refresh the collection.

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com





The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI BusinessSensitve material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.