[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





>From: "Drew Wutka" <DWUTKA at marlow.com>
>
>A little clarification on the 'callback' issue.  I am NOT referring to the 
>callback capabilities of Access list and combo boxes.  What I am referring 
>too is API call backs.  For example, if you want to enumerate all of the 
>windows on your machine, you call the EnumWindows API, and in that call, 
>you give it the address of your callback routine, using AddressOf.  In 97, 
>there was not a native AddressOf function, there was one available on the 
>web that worked great.  In 2000 and later (with VBA being a subset of VB 6, 
>instead of VB 5), AddressOf became available as a native function.
>
>However, if you have a function that uses this, and it has run (doesn't 
>need to still be running), if you go into the code in debug mode.....zap, 
>everything locks up.  I know that was the case in 2000, and I'm 99% sure it 
>does the same thing in 2003 (never had 2002).
>
>As for what you posted about simultaneous data 'editing', here's my take on 
>this.  I know there are times when an application may run into this, but 
>with good developing, this situation can be very easily avoided.  I can't 
>think of a single application I have written that would have two people 
>updating the same record.  First, I normalize out the data.  So, in your 
>example, one person may be changing an address, while another edits a 
>'contract value' field, in both cases, the users would be using different 
>tables.  Also, when I have a class structure built, if I am even remotely 
>worried that two people can edit a record at the same time, I have the 
>class only update the fields that have been changed.  That is something you 
>can't do with a bound found.  So if one person changes someone's first 
>name, and one person changes someone's last name, my class will only change 
>the first name for the first person, and last name for the second person.  
>Smooth as silk.  In that same process, data integrity can be checked, so if 
>there is a clash on the exact same field, it can warn the user.
>
>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 7:26 PM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Performance tips anyone?
>
>I don't understand the comment about 'goofiness' with callbacks and debug  
>I
>use callbacks more often than not and have never had a problem with 
>breaking
>into code.
>
>I've said before what I say again below, and it needs to be repeated.
>
>My concern with unbound forms is the scenario where two users open the same
>record and each performs an edit.  Generally, unbound edits are written 
>when
>the form closed or unloads.  In such a case, the last user to close 'wins'
>and the first user's eits are lost without notice.  In the environments
>where I have worked, we frequently have multiple users working in a single
>record.  User 'A' will open the record to update an address.  User 'B' will
>open the record to update a contract value field.  User 'B' will close the
>record and his edit is writen to the table.  User 'A' answers the phone and
>closes the record a moment later and the database is updated with the
>current unbound form data.  The record has the updated address but the
>change to the contract value is lost and no one is warned.
>
>Sure you can write code to handle the situation.  For example, you could
>update only edited fields and the overwrite will only happen if two users
>concurrently edit the same field to different values.   Or you could add an
>edit flag and update it anytime a user has a pending edit and clear it when
>the record closes.  But I believe Drew said earlier that since it's a Jet
>BE, there is no difference bound or unbound in regard to locking. With a
>bound form, you can get a notification that another user has edited the
>record and gives 'A' the option to abandon the edit.  My users see this
>message with a degree of regularity and they have been taught always to 
>drop
>their edits, reopen the record and then redo any required edits.
>
>I have seen many unbound databases created by reasonably competent Access
>developers who blithely ignore this issue.  They don't mention the
>additional things that need to be done in order to ensure data integrity.  
>I
>am not adverse to unbound forms and use them where appropriate.  If a 
>person
>is in a web driven database where a user may have a personal shopping cart
>and need to log in to access his cart, no problem with unbound.  If you 
>take
>appropriate steps to ensure the integirty of the data, no problem.
>
>My primary reason for working unbound has been the formatting limitations 
>of
>bound controls in continuous forms and my environment has never permitted
>3rd party controls.  I've heard reference to 'boundaries' or limitations of
>bound forms but still manage to use them without compromising the user
>interface.  You can even populate a bound form with a series of values that
>did not exist in any table.
>
>Some people assume that a bound form contains as many records as some table
>to which a form is bound, but all of my parent forms are populated by a
>single record determined by a primary key parameter.  The recordset is
>determined in the open event and the recordsource property is empty.  It is
>just as fast as unbound, and faster than unbound that includes the setting
>of edit flags.  I have 47 concurrent users today and among the only unbound
>forms I use today are a few that allow drag and drop of categories of
>employees (4 categories, 4 colours in 4 columns, each its own subform) onto
>and from a list of projects, and the dragged employees drop in to a subform
>of projects containing sublists of employees that retain their formatting
>where dropped.  I counln't figure out how to intersperse the types of
>records with their various colours in a bound form.  And of course forms 
>for
>search, report selection, navigation, calculations, calendars and such
>ancilliary forms that do not need to update their own values.
>
>47 users, Access 2003, Access 2000 format, nearly entirely bound and no
>reports of data conflicts that shouldn't be reported.  I anticipate more
>users in the coming months and see no difference between the bound and
>unbound approach in terms of locked data pages, indexes or corruptions
>because my recordsets are tiny compared to the tables and rarely, but
>occasionally, involve concurrent edits.
>
>Ciao
>Jürgen Welz
>Edmonton, Alberta
>jwelz at hotmail.com
>
>
>
>
>
> >From: "Drew Wutka" <DWUTKA at marlow.com>
> >
> >But Access is just as well suited for unbound solutions too.  The only
> >exception to that rule is it's goofiness with callback routines. (Can't
> >go into debug if you have a callback routine ANYWHERE.  Goes haywire).
> >
> >Drew

_________________________________________________________________
New Windows Live Hotmail is here. Upgrade for free and get a better look. 
www.newhotmail.ca?icid=WLHMENCA150