[AccessD] JIT Forms

Dan,

In fact the data store is not the issue, although in my experience an Access
data store is often faster than a SQL Server data store.  It really doesn't
matter however.  What you are trying to do in either case is to reduce the
data pulled.  This discussion is about two strategies, not related.  

1) Pull only one record for the main form
2) Use JIT subforms for the child records for the main form.  

These two strategies increase the speed ("feel") of the system for very
different reasons.  Pulling only one record for the main form obviously
increases the speed simply because the form will not open until a bunch of
records are pulled.  It does NOT wait for all of them but it does wait for
about 100 or so (if there are that many).  Thus by pulling just one, the
form opens as soon as that one record loads.

The JIT subforms increases the speed for a different reason, that being that
subforms populate before the main form.  Thus the main form has to wait for
all the subforms to populate before the main form populates and displays.
Obviously, the more subforms, the larger the gains of going to JIT subforms.

By implementing both of these strategies you can IN SOME INSTANCES decrease
the load time of the main form immensely.  In some cases the load time
decrease will not be noticeable.  It really depends on a number of factors
including the number of people in the database, the speed of the network and
amount of network traffic, the number of records being pulled in the main
form and the number of subforms.  It is therefore impossible to generalize
about how much it will help, but it will always help some amount. 

I can tell you that when both of these strategies were applied I dropped the
form open time of a VERY complex form with 25 users on a slow network on
slow machines from 15-25 seconds down to 3-7 seconds.  That is worth doing!

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, November 09, 2007 3:48 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] JIT Forms

John - I've been briefly following this thread.  Is this being done with an
Access BE or with a SQL Server BE?

If Access, how much improvement did you see?

Dan