Solved

How to Speed up opening a form

Posted on 2011-02-16
21
1,020 Views
Last Modified: 2013-11-28
I have a form with two subforms: A and B. The SourceObject of each subform is set in the Open event of the main form. The first SourceObject assignmnet statement (for subform A) is executed in <1ms, but the second one (for subform B) takes 3-4 seconds.

If I switch the SourceObject assignment statements so that B is executed first and A is executed second, I get the same result; ie, the first (now for B) takes <1ms to execute, and the second (now for A) takes 3-4 seconds.

How can I make then both quick?
0
Comment
Question by:Milewskp
  • 10
  • 5
  • 3
  • +1
21 Comments
 
LVL 11

Expert Comment

by:RgGray3
ID: 34910793
Interesting...

Do they both retrieve large recordsets?  (Approx # or records)

It sounds like the first Query/filter must completely return its records before the second line of code assigning the second Source object is executed...

With the understanding that Percieved speed is as good as actual speed...
Do they both need to be viewed at the same time...  SIDE BY SIDE...

Could you place the two objects on seperate pages of a tab control...
It would HIDE the speed lag
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34910915
What happens if you hardcode the SourceObject's for each subform?
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34910949
Hi RgGray3,
<Do they both retrieve large recordsets?>
Both subforms have the same recordsource (q500_Project), which has 833 records.
NOTE: the LinkChildFields and LinkMasterFields of both subforms is "" (zls).

<Do they both need to be viewed at the same time...  SIDE BY SIDE...>
Yes.

<Could you place the two objects on seperate pages of a tab control...>
No.
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34910995
If I delete the Recordsource property of both subforms, the main form opens in a jiffy.
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34911026
What is the reason for assigning the SourceObject at runtime  
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34911292
Hi RgGray3,
<What is the reason for assigning the SourceObject at runtime?>
It's a practice of mine for all subforms.
I tried settign the SourceObject for both subforms in the design view of the main form, and got the same speed issue.
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34911393
I do the same thing on Subforms on Pages of a tab control...
only the subform on the first page is hard coded...
This way I only load data when it is needed...   (JIT)
but that does not seem to be a solution for this problem

You stated that you have tried to connect the forms in design time and connect the data with similar results...

is it the visual lag that bothers you/your users or is the time really excessive?

Going back to percieved speed...
it is possible to open the form hidden or "freeze" the screen when you load the main form
Put up a little distraction "Loading Data"
After it is loaded unfreeze the screen and display the completely loaded form

What I suspect is your problem is that due to the way Access manages forms and their data...
It displays a form after the first page of data (screenfull) is loaded ...  but it continues to load the rest of the data before returning control to the user and program.

Hence your lag.
 Are you working on a slow network, is the data large and complex

Are you loading fields in your datasource that are not needed for display..   (Select * from ...  instead of Select Field1, Field2,Field3
or are their calculations in the query that could be performed by the form

Just a few thoughts
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34912030
If the first subform is driven by a query and the query takes 'some' time to complete, again a reason for the delay.  You can get an appreciation of the delay by running the query and noting the time it take for the record count to appear.
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34913177
Hi GrayL,
<again a reason for the delay>
So why does the first subform take <1ms but the second one takes 3-4 sec?

<noting the time it take for the record count to appear. >
I'll try that and report back.
0
 
LVL 75
ID: 34913956
"A and B. The SourceObject of each subform is set in the Open event of the main form."
Have you tried using the On Load event of the main form ?

And ... did you answer the question @ http:#a34910915 ?

Because unless you employee that Tab control, with two tabs and a corresponding subform ... and then set the Source Object for the tab (page) that is not initially visible ... on-the-fly ... I'm not seeing any advantage of using the On Open or On Load events on the main form to set the Source Object of the subforms.  In fact, I can almost envision this as slowing down the process.

mx
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:Milewskp
ID: 34917890
Hi mx,
<Have you tried using the On Load event of the main form ?>
No. why would it make a difference?

<I can almost envision this as slowing down the process.>
I  tried settign the SourceObject for both subforms in the design view of the main form, and got the same speed issue.

Hi GRayL
<What happens if you hardcode the SourceObject's for each subform?>
I  tried settign the SourceObject for both subforms in the design view of the main form, and got the same speed issue.
(Sorry, I didn't see your post before.)





0
 
LVL 75
ID: 34918150
"I  tried settign the SourceObject for both subforms in the design view of the main form, and got the same speed issue."

"why would it make a difference?"
My point is ... since this is no difference, why bother with code to set in Open event ?

mx
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34920364
Given the query takes some time to execute, you might improve the processing speed if large tables are involved by making sure you have a primary key on each table and the appropriate fields are indexed.  
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34920386
Hi GrayL,
<noting the time it take for the record count to appear. >
I tried that; the record count appears in about 2-2.5 seconds.
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34920419
Hi GaryL,
When I open the subforms directly, it takes about 3-4 seconds for the record count to appear.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34920482
I believe that is why the delay in opening the second subform.  If you are running a query with an ORDER BY clause, make sure the fields in the clause are indexed if one or more large tables are involved.  Depending on the complexity of the query, large could be as few as 10,000 to 100,000 records.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34920499
In addition, if you are running off a server,  your network speed is going to impact your query processing time.
0
 
LVL 1

Accepted Solution

by:
Milewskp earned 0 total points
ID: 34922337
Hi all,
It turns out it was network issues. In the course of running tests during the day, I found the speed would vary widely from minute to minute and hour to hour. The database has several tables that are linked to three or four different mdb files over the LAN. When I converted all of then to local tables, teh form opened in a jiffy. The interesting thing is that teh form is not dependent on any of these tables - I can delete then from teh database and the form still opens just fine.

This form became slow a week or two ago, and very likely will become fast again if the IT guys restore the network to its previuos configuration - I'm at their mercy.

The silver lining is that during my investigation I optimized the form's code which has helped.

Moral of my story:
If a form runs a lot faster when all linked tables have been converted to local tables, the network the tables are linked over is likely to blame. Linked tables can even affect the performance of a form that's not depend or related to them in any way.
The best you can do is optimize your form to run as fast as possible when all linked tables are converted to local tables. After that, you're at the mercy of the network.




0
 
LVL 1

Author Comment

by:Milewskp
ID: 35115894
Thanks everyone for trying to help. I will chose my answer as the solution.
0
 
LVL 75
ID: 35117440
"The best you can do is optimize your form to run as fast as possible when all linked tables are converted to local tables"
Not exactly a revelation ...
0
 
LVL 1

Author Closing Comment

by:Milewskp
ID: 35154470
.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Direct Mail software 4 46
Create Form using Wizard 14 39
access 7 27
Create a trap for no records found when user filters a form 11 0
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now