Front End Form Opens Slowly across Network

On Wed, 5 Nov 2008 14:46:00 -0800 (PST), prakashwadhwani@gmail.com
wrote:

You could check the Performance FAQ on Tony Toews' website.

Also realize that a dropdown list with 15,000 items is about 100 times
too many. Be creative, and come up with a better interface.

-Tom.
Microsoft Access MVP




>I have an app containing 9 forms which have approximately 2 to 3 combo
>boxes and 7 to 8 text boxes + a few labels (in each form).
>
>On the same PC the forms open very fast. However when I load the front
>end of the app on another PC across the network (100mbps), each form
>takes about 30 to 40 seconds to open the first time & subsequently
>approx 5 to 6 secs to open.
>
>The rowsource of the combo boxes are SQL statements as follows:
>combo1 pulls approx 1200 rows of data
>combo2 pulls approx 15000 rows of data
>combo3 runs a SQL distinct across a table containing approx 15000
>rows.
>
>Waiting for the forms to open is unacceptable to me & subjecting my
>clients to this would be really mean on my part. So far none of them
>have complained (and I don't plan to wait for them to do so either :-)
>
>I'm certain the delay is caused by all these rows of data being pulled
>across the wire. Access not being a true client-server app must be
>pulling all these rows to process on the client pc & my guess is this
>is what's causing the delay.
>
>Would some of you gurus please post some pointers as to how I should
>go about speeding up this process. I'd really like for the forms to
>open "BANG" as when run on a "local" pc.
>
>Thx & Best Rgds,
>Prakash.

On Nov 6, 8:54*am, Tom van Stiphout wrote:
> On Wed, 5 Nov 2008 14:46:00 -0800 (PST), prakashwadhw...@gmail.com
> wrote:
>
> You could check the Performance FAQ on Tony Toews' website.
>
> Also realize that a dropdown list with 15,000 items is about 100 times
> too many. Be creative, and come up with a better interface.
>
> -Tom.
> Microsoft Access MVP
>
> >I have an app containing 9 forms which have approximately 2 to 3 combo
> >boxes and 7 to 8 text boxes + a few labels (in each form).
>
> >On the same PC the forms open very fast. However when I load the front
> >end of the app on another PC across the network (100mbps), each form
> >takes about 30 to 40 seconds to open the first time & subsequently
> >approx 5 to 6 secs to open.
>
> >The rowsource of the combo boxes are SQL statements as follows:
> >combo1 pulls approx 1200 rows of data
> >combo2 pulls approx 15000 rows of data
> >combo3 runs a SQL distinct across a table containing approx 15000
> >rows.
>
> >Waiting for the forms to open is unacceptable to me & subjecting my
> >clients to this would be really mean on my part. So far none of them
> >have complained (and I don't plan to wait for them to do so either :-)
>
> >I'm certain the delay is caused by all these rows of data being pulled
> >across the wire. Access not being a true client-server app must be
> >pulling all these rows to process on the client pc & my guess is this
> >is what's causing the delay.
>
> >Would some of you gurus please post some pointers as to how I should
> >go about speeding up this process. I'd really like for the forms to
> >open "BANG" as when run on a "local" pc.
>
> >Thx & Best Rgds,
> >Prakash.


Tom, thanks for taking the time to reply. Most of my Access databases
have been for single-users so far & as such I have not encountered
this problem before. However I aim to correct this asap.

I went over to Tony's site but all I could find was this:

=== snipped from Tony's site =================
This is the tip: Load the form, subform, combobox and listbox record
sources at run-time. That's it. You will achieve dramatic performance
improvement and reduced database size.

Here's the technique: Delete the SQL from the RecordSource and
RowSource properties of the form, subforms, comboboxes and listboxes.
Now in the Form_Load event load the appropriate SQL as follows ...

Private Sub Form_Load()
Me.RecordSource = "qryLargeTable"
Me.txtSomeField.RowSource = _
"SELECT SomeField " & _
"FROM qryLargeTable " & _
"GROUP BY SomeField " & _
"ORDER BY SomeField;"
End Sub

==== End Snip ====================================

Is this all that's required ?

If there's more I'd appreciate a little more detailed help or pointers
to sites directly addressing my problem as I'm very new to Network
Programming.

Also ... I have a similar app running at another client's location and
the forms open fairly quickly there ... hardly any delay. Could you
possibly throw some more light on this ?

Best Rgds,
Prakash.

On Wed, 5 Nov 2008 22:27:28 -0800 (PST), prakashwadhwani@gmail.com
wrote:

I'm not sure what you were reading. There are many suggestions here:
http://www.granite.ab.ca/access/performancefaq.htm

-Tom.
Microsoft Access MVP


>On Nov 6, 8:54*am, Tom van Stiphout wrote:
>> On Wed, 5 Nov 2008 14:46:00 -0800 (PST), prakashwadhw...@gmail.com
>> wrote:
>>
>> You could check the Performance FAQ on Tony Toews' website.
>>
>> Also realize that a dropdown list with 15,000 items is about 100 times
>> too many. Be creative, and come up with a better interface.
>>
>> -Tom.
>> Microsoft Access MVP
>>
>> >I have an app containing 9 forms which have approximately 2 to 3 combo
>> >boxes and 7 to 8 text boxes + a few labels (in each form).
>>
>> >On the same PC the forms open very fast. However when I load the front
>> >end of the app on another PC across the network (100mbps), each form
>> >takes about 30 to 40 seconds to open the first time & subsequently
>> >approx 5 to 6 secs to open.
>>
>> >The rowsource of the combo boxes are SQL statements as follows:
>> >combo1 pulls approx 1200 rows of data
>> >combo2 pulls approx 15000 rows of data
>> >combo3 runs a SQL distinct across a table containing approx 15000
>> >rows.
>>
>> >Waiting for the forms to open is unacceptable to me & subjecting my
>> >clients to this would be really mean on my part. So far none of them
>> >have complained (and I don't plan to wait for them to do so either :-)
>>
>> >I'm certain the delay is caused by all these rows of data being pulled
>> >across the wire. Access not being a true client-server app must be
>> >pulling all these rows to process on the client pc & my guess is this
>> >is what's causing the delay.
>>
>> >Would some of you gurus please post some pointers as to how I should
>> >go about speeding up this process. I'd really like for the forms to
>> >open "BANG" as when run on a "local" pc.
>>
>> >Thx & Best Rgds,
>> >Prakash.
>
>
>Tom, thanks for taking the time to reply. Most of my Access databases
>have been for single-users so far & as such I have not encountered
>this problem before. However I aim to correct this asap.
>
>I went over to Tony's site but all I could find was this:
>
>=== snipped from Tony's site =================
>This is the tip: Load the form, subform, combobox and listbox record
>sources at run-time. That's it. You will achieve dramatic performance
>improvement and reduced database size.
>
>Here's the technique: Delete the SQL from the RecordSource and
>RowSource properties of the form, subforms, comboboxes and listboxes.
>Now in the Form_Load event load the appropriate SQL as follows ...
>
>Private Sub Form_Load()
> Me.RecordSource = "qryLargeTable"
> Me.txtSomeField.RowSource = _
> "SELECT SomeField " & _
> "FROM qryLargeTable " & _
> "GROUP BY SomeField " & _
> "ORDER BY SomeField;"
>End Sub
>
>==== End Snip ====================================
>
>Is this all that's required ?
>
>If there's more I'd appreciate a little more detailed help or pointers
>to sites directly addressing my problem as I'm very new to Network
>Programming.
>
>Also ... I have a similar app running at another client's location and
>the forms open fairly quickly there ... hardly any delay. Could you
>possibly throw some more light on this ?
>
>Best Rgds,
>Prakash.