Form Optimization

Hi,
I have a form with a lot of subforms. This thing takes about 70 seconds
to load on a server. I have a front-end back-end set up in Access
2000/2003. Anyhow I tried to optimize the darn thing. I opened up half
of the forms on form open, and the other half open when the user clicks
on a (tab button). After I relink tables the first part of the form
takes about a minute to open. Then the second part takes about 20
seconds. Then I close database. Reopen it and the first form opens
intantly, and the second takes about 5 seconds. What's more interesting
is that when I take the front end and put it on another PC, the same
thing happens(it opens very fast). Then if I relink tables, again the
first time I open the forms it takes a long time...then fast again.
Anyone have any clue as to why this is?

Thanks in advance.

re: Form Optimization

ken wrote:[color=blue]
> Hi,
> I have a form with a lot of subforms. This thing takes about 70 seconds
> to load on a server. I have a front-end back-end set up in Access
> 2000/2003. Anyhow I tried to optimize the darn thing. I opened up half
> of the forms on form open, and the other half open when the user clicks
> on a (tab button). After I relink tables the first part of the form
> takes about a minute to open. Then the second part takes about 20
> seconds. Then I close database. Reopen it and the first form opens
> intantly, and the second takes about 5 seconds. What's more interesting
> is that when I take the front end and put it on another PC, the same
> thing happens(it opens very fast). Then if I relink tables, again the
> first time I open the forms it takes a long time...then fast again.
> Anyone have any clue as to why this is?
>
> Thanks in advance.[/color]

Do you use the Form.Current event much? A while back I had a lot of
trouble with a slow-loading form with three sub-forms and it wasn't
until I stepped through the code that I realised it was calling the
Form.Current event many times. Can't remember why, though, or what I
did about it ;¬}

Edward

re: Form Optimization

no it goes in the form.current even once.

re: Form Optimization

On 28 Oct 2005 06:14:18 -0700, "ken" <gevayl@gmail.com> wrote:
[color=blue]
>Hi,
>I have a form with a lot of subforms. This thing takes about 70 seconds
>to load on a server. I have a front-end back-end set up in Access
>2000/2003. Anyhow I tried to optimize the darn thing. I opened up half
>of the forms on form open, and the other half open when the user clicks
>on a (tab button). After I relink tables the first part of the form
>takes about a minute to open. Then the second part takes about 20
>seconds. Then I close database. Reopen it and the first form opens
>intantly, and the second takes about 5 seconds. What's more interesting
>is that when I take the front end and put it on another PC, the same
>thing happens(it opens very fast). Then if I relink tables, again the
>first time I open the forms it takes a long time...then fast again.
>Anyone have any clue as to why this is?
>
>Thanks in advance.[/color]

It sounds like the query plans for your queries are getting dropped when you
re-link, then rebuilt the first time the queries are executed. Does the
slow-down occur after a compact/repair of the front-end as well as after
relinking?

re: Form Optimization

YEP! I did a compact repair and it was slow again. Anyway I can prevent
query plans from getting dropped? I tried this in an uncompiled file.
If I use an MDE will I get the same thing after compact/repair?

re: Form Optimization

Steve Jorgensen <nospam@nospam.nospam> wrote in
news:l5h4m1hml7cr2pd9tde9jl5ea2idv58rh4@4ax.com:
[color=blue]
> On 28 Oct 2005 06:14:18 -0700, "ken" <gevayl@gmail.com> wrote:
>[color=green]
>>I have a form with a lot of subforms. This thing takes about 70
>>seconds to load on a server. I have a front-end back-end set up in
>>Access 2000/2003. Anyhow I tried to optimize the darn thing. I
>>opened up half of the forms on form open, and the other half open
>>when the user clicks on a (tab button). After I relink tables the
>>first part of the form takes about a minute to open. Then the
>>second part takes about 20 seconds. Then I close database. Reopen
>>it and the first form opens intantly, and the second takes about 5
>>seconds. What's more interesting is that when I take the front end
>>and put it on another PC, the same thing happens(it opens very
>>fast). Then if I relink tables, again the first time I open the
>>forms it takes a long time...then fast again. Anyone have any clue
>>as to why this is?[/color]
>
> It sounds like the query plans for your queries are getting
> dropped when you re-link, then rebuilt the first time the queries
> are executed. Does the slow-down occur after a compact/repair of
> the front-end as well as after relinking?[/color]

Well, there's also the issue of relinking not updating the stats
stored in the linked table definition. This is a problem A2K, and
when it strikes, it can be corrected by deleting and recreating the
links instead of just updating the connect string.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc