Slowwwww form

[JonoB]

I have a front-end back-end database that works very well, except for one form that takes over a minute to open. The form is bound to a qry that is based on 4 linked tables, although the majority of data (about 90%) comes from only one table.

There are 4 combo's on the form (2 of which are linked to about 150 records each and the other 2 to about 5 records each). There is also about 15 text fields on the form.

Even opening the form in design mode takes over a minute.

I have 4 domain functions on the form, which I deleted, but that didnt improve performance at all. The form does not display any data when first opened, until such time as a selection is made in the first combo box. Changing the recordset type to snapshot (just to test) also makes no difference.

All other forms in the database open relatively quickly - around 1-3 seconds or so.

Any ideas or suggestions much appreciated.

 

[jebry]

Hi!

Sounds like the queries are just taking a long time to run. One possibility is to open the form (hidden) when the application opens and then just make it visible and invisible at the appropriate times.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[JonoB]

OK, but then it would take a long time for the application to open, right?

 

[JonoB]

OK, but then it would take a long time for the application to open, right?

Oh, and by the way, if I move the back end to my local machine and update links, the form opens pretty quickly (about 3-5 seconds or so). Which means that it is probably a network issue, more than anything else, right?

 

[scriverb]

Yes, there could be a network issue with that performance change. Try running just the query that is the RecordSource for the form alone. See how much time it takes for this query to run. Is it fast or slow. If slow then you know the culprit.

Also, is there a Subquery in the query. And are you using an In (Select. . . type of match? If so these are notoriously slow. You see the subquery has to run independtly for each row of the query. So if you have 10,000 records the subquery has to run 10,000 times. You can change that by changing the subquery to a saved query and performing an INNER JOIN to it. This way it only runs once.

Just some ideas for you. Waiting for your response.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jebry]

Hi!

Another option if it is possible is to always open the form filtered. Ask the user to put in search parameters before opening the form. If it is a network traffic issue, which it sounds like it could be, then the filtered form will open much more quickly. If you have a main menu (switchboard) form then you can use my original idea by opening this form in the main form's current event. Then it shouldn't interfere with the application opening.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[JonoB]

Hi Bob, thanks for the comments.

Here is the sql for my qry...I looked for the things that you mentioned and it seems to be OK. The qry on its own takes about 10 second to open, so not too bad. What I dont get is that tblFund only has about 150 records, and the generated qry only has about 30 fields for each record - doesnt seem to be that demanding.

Although, there are 4 other queries to run the combo boxes, although once again, these are simple queries.

SELECT tblFund.FundID, tblFund.FundName, tblFund.RXM, tblFund.FundRating, tblFund.Blocked, tblFund.AdvisorID, tblAdvisor.AdvisorName, tblFund.DateAdded, tblFund.BusinessYears, tblFund.Principals, tblFund.AssetsA, tblFund.StyleID, tblFund.DomicileID, tblFund.Master, tblFund.NAVCov, tblFund.Settlement, tblFund.Pricing, tblFund.Margin, tblFund.MarginOrThresh, tblFund.NrvThresh, tblFund.MarginorDep, tblFund.MarginCallLevel, tblFund.CloseOutLevel, tblFund.MinTransAmt, tblFund.Remarks, tblFund.Lines1, tblFund.Lines2, tblFund.Lines3, tblFund.Lines4, tblFund.Lines5, tblFund.YearEnd, tblFund.AFS, tblStyle.StyleName, tblDomicile.DomicileName, tblFund.Memo
FROM ((tblAdvisor INNER JOIN tblFund ON tblAdvisor.AdvisorID = tblFund.AdvisorID) INNER JOIN tblStyle ON tblFund.StyleID = tblStyle.StyleID) INNER JOIN tblDomicile ON tblFund.DomicileID = tblDomicile.DomicileID
WHERE (((tblFund.FundID)=[Forms]![frmAdvisorFund]![ListFund]));

 

[AlienRay]

Just curious; does the form HAVE to be bound to the query? Could the form be unbound, with the query results displayed in a table?

 

[scriverb]

How large is tblAdvisor? That is the primary table and the one that starts the join process. Also, the fields that are used in the join process, are they indexed? If not then indexing will speed up this query tremendously.

What about the SQL being used for the other comboboxes. Try posting those also. They each have to run upon opening.

Also, look for calculated fields. DLookups, DSums, DCounts, etc. These all are being run against the tables during opening of the form.

Nothing looks particular out of line in your query. Just check out the above items and see if that helps.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[bbrendan]

Hi,

You could try this.
tools/Options/General

Untick the Checkboxes under "Name AutoCorrect".
Exit out then open the database and try and open the form.

This should speed up things hopefully...

cheers