Slow Open Form 2

[vise]

Hi All,

I'm hoping I can get some help on the following.
I have a form, which has a complex union query that I set only on open form action to a listbox.
The form opens in 2 secs when on my hard drive; however, when I put the database on the network, it takes approximately 1 min 41 secs to open without any other users connected.

I've searched for various ways to improve this speed and I've already done the following:
- Removed Name Auto Correct
- Used Union All in my query join
- Kept a persistent database connection via a hidden form bound to a table

I'm just wondering if there's anything else I can do. It is something to do with the query populating the listbox, as it opens quite fast when I don't set the query as the rowsource.

I am restricted in that I can't separate the BE from the FE and it all has to reside in the MDB.

I appreciate your assistance.

I've included below a portion of my query as well:

SELECT DLookUp("Username","tblRestrictions","username='jjones'") AS Person, tblGeneralPolicyHome.PolicyTerm, tblPoliciesHome.PolID, tblPoliciesHome.SiteID, tblGeneralPolicyHome.[code], ([Buildings Limit]+[Contents Limit])*DLookUp("[Rate to USD]","tblCurrency","[code]=" & [code]) AS [Building + Contents in USD], IIf([PolicyTerm]="NEW",Format(DLookUp("buildings_content_nb","tblRestrictions","username='jjones'"),"#,##0"),Format(DLookUp("buildings_content_rnl","tblRestrictions","username='jjones'"),"#,##0")) AS [YOUR Limit In USD]
FROM tblPoliciesHome INNER JOIN tblGeneralPolicyHome ON tblPoliciesHome.PolID = tblGeneralPolicyHome.PolID
WHERE (((tblGeneralPolicyHome.PolicyTerm)="NEW") AND ((IIf([Cancelled]=-1,"Y",""))<>"Y") AND (([Buildings Limit]+[Contents Limit])>DLookUp("buildings_content_nb","tblRestrictions","username='jjones'")/DLookUp("[Rate to USD]","tblCurrency","[code]=" & [code])) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].PolID)) OR (((tblGeneralPolicyHome.PolicyTerm)="NEW") AND ((IIf([Cancelled]=-1,"Y",""))<>"Y") AND (([Buildings Limit]+[Contents Limit])>DLookUp("buildings_content_nb","tblRestrictions","username='jjones'")/DLookUp("[Rate to USD]","tblCurrency","[code]=" & [code])) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].PolID)) OR (((tblGeneralPolicyHome.PolicyTerm)="RENEWAL") AND ((IIf([Cancelled]=-1,"Y",""))<>"Y") AND (([Buildings Limit]+[Contents Limit])>DLookUp("buildings_content_rnl","tblRestrictions","username='jjones'")/DLookUp("[Rate to USD]","tblCurrency","[code]=" & [code])) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].PolID)) OR (((tblGeneralPolicyHome.PolicyTerm)="RENEWAL") AND ((IIf([Cancelled]=-1,"Y",""))<>"Y") AND (([Buildings Limit]+[Contents Limit])>DLookUp("buildings_content_rnl","tblRestrictions","username='jjones'")/DLookUp("[Rate to USD]","tblCurrency","[code]=" & [code])) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].PolID))
ORDER BY tblPoliciesHome.SiteID DESC;


union all SELECT DLookUp("Username","tblRestrictions","username='jsmith'") AS Person, tblGeneralPolicyHome.PolicyTerm, tblPoliciesHome.PolID, tblPoliciesHome.SiteID, tblGeneralPolicyHome.[code], ([Buildings Limit]+[Contents Limit])*DLookUp("[Rate to USD]","tblCurrency","[code]=" & [code]) AS [Building + Contents in USD], IIf([PolicyTerm]="NEW",Format(DLookUp("buildings_content_nb","tblRestrictions","username='jsmith'"),"#,##0"),Format(DLookUp("buildings_content_rnl","tblRestrictions","username='jsmith'"),"#,##0")) AS [YOUR Limit In USD]
FROM tblPoliciesHome INNER JOIN tblGeneralPolicyHome ON tblPoliciesHome.PolID = tblGeneralPolicyHome.PolID
WHERE (((tblGeneralPolicyHome.PolicyTerm)="NEW") AND ((IIf([Cancelled]=-1,"Y",""))<>"Y") AND (([Buildings Limit]+[Contents Limit])>DLookUp("buildings_content_nb","tblRestrictions","username='jsmith'")/DLookUp("[Rate to USD]","tblCurrency","[code]=" & [code])) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].PolID)) OR (((tblGeneralPolicyHome.PolicyTerm)="NEW") AND ((IIf([Cancelled]=-1,"Y",""))<>"Y") AND (([Buildings Limit]+[Contents Limit])>DLookUp("buildings_content_nb","tblRestrictions","username='jsmith'")/DLookUp("[Rate to USD]","tblCurrency","[code]=" & [code])) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].PolID)) OR (((tblGeneralPolicyHome.PolicyTerm)="RENEWAL") AND ((IIf([Cancelled]=-1,"Y",""))<>"Y") AND (([Buildings Limit]+[Contents Limit])>DLookUp("buildings_content_rnl","tblRestrictions","username='jsmith'")/DLookUp("[Rate to USD]","tblCurrency","[code]=" & [code])) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].PolID)) OR (((tblGeneralPolicyHome.PolicyTerm)="RENEWAL") AND ((IIf([Cancelled]=-1,"Y",""))<>"Y") AND (([Buildings Limit]+[Contents Limit])>DLookUp("buildings_content_rnl","tblRestrictions","username='jsmith'")/DLookUp("[Rate to USD]","tblCurrency","[code]=" & [code])) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].PolID))
ORDER BY tblPoliciesHome.SiteID DESC

 

[PHV]

I'd avoid all those DLookUp ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vise]

Thanks PHV, you're always of great help. I'm going to try and use a custom user function for that and report back

 

[vise]

I replaced the Dlookup with Elookup as per:

http://allenbrowne.com/ser-42.html

This did speed up my time, although it still take ~40 secs to open the form. The query itself runs at 2.6 secs.

I'm just not sure why on the form version runs that much slower.

Any other suggestions? This is an integral query that I can't get rid of.

I tried putting a button that when click would populate the list box, which works well, it does the job in the stated query run time, but when I try to do a programmatic click event on form open, the same 40+ sec issue occurs.

 

[vise]

Hi All,
I'm still having an issue with this slow up, thought I'd bump this up and share what I've learned thus far.

I created a persistent connection to the database with a hidden form over the network, I've also turned off Name AutoCorrect which did speed up the database significantly.

The issue, I believe, now lies with the way that queries are populated to the listbox.

I've tried two methods:
1.) Open the form with the listbox rowsource = to the query.
Performance time of: 40 seconds

2.) Open the form without a populated listbox rowsource = nothing
Performance time of 3 seconds
Then, I added a command button to add the rowsource to the listbox -> which takes about 2-3 seconds to complete once clicked.

I don't understand why the disparity between the two methods. The method of the form event still needs to be in the first method form (i.e. when a user opens up a form it should automatically populate this listbox). I've even tried to call the click even on form open, but this has led to the same performance as when assigning the query directly to the rowsource of the listbox.


Sorry for the long post, and thanks again for any help you can provide.

 

[MajP]

I think you are hitting this with a scalpel and you need an axe.

This query looks painfully inefficient to start with. If I counted correctly you have over 30 VB functions embedded in this query. I think PHV's comment did not mean to replace Dlookup with Elookup, but look at a better query design. However, that is an impressive performance increase using Elookup.
Can't you use some reference tables and join to them to alleviate many of the lookups, iifs, and formats? Those are all slow.

With that said the order of form events is
Open ? Load ? Resize ? Activate ? Current

I would try to load it much later in the sequence. I would set a public variable
public blnIsLoaded as boolean

then in the current event
if not blnIsLoaded then
'load listbox
'set listbox control source
blnIsLoaded = true
end if

That way more resources are available because it will load the listbox at the first occurrence of the on current event.

 

[MajP]

One thing. What is the purpose of
((IIf([Cancelled]=-1,"Y",""))<>"Y")
is that not simply?
Cancelled <> -1

 

[vise]

Thanks MajP.
I completely agree on the revamp of the Query. Unfortunately, I'm not quite sure how to go about it.
The bulk of the lookups are to "tblRestrictions" which contains values that each of the homes are supposed to be checked against. tblRestrictions has a list of users and their restricted limits.

I'll try and somehow join the tables, but there's no common factor other than I'm trying to run these limits for every single person against each home.

I've put the load event in the Current event and it seems to have slowed down even further (~2 mins to open now).. Quite strange.

Tomorrow I'll give it another shot and post back with progress. I'm certainly learning a lot.

Thanks again.

 

[vise]

Thanks MajP and PHV.
I've simplified my query significantly (pasted below for anyone's future reference).

I have three takeaways from this:
1.) Don't use Format functions in a query, Dlookup/Dcount, and make sure that name auto correct is turned off when not doing development.
2.) Simplify your queries as much as possible.
3.) You can do a cross reference of two table, even if they don't have common fields, by just selecting the table. (i.e. Select tblRestrictions.Name, tblOther.*
From tblRestrictions, tblOther)

This helped me a lot and I will use it in future reference.

SELECT tblRestrictions.username AS Person, tblGeneralPolicyHome.PolicyTerm, tblPoliciesHome.PolID, tblPoliciesHome.SiteID, tblGeneralPolicyHome.[ Code], ([Buildings Limit]+[Contents Limit])*[Rate to USD] AS [Building + Contents in USD], IIf([PolicyTerm]="NEW",[buildings_content_nb],[buildings_content_rnl]) AS [YOUR Limit In USD]
FROM tblRestrictions, tblCurrency INNER JOIN (tblPoliciesHome INNER JOIN tblGeneralPolicyHome ON tblPoliciesHome.PolID = tblGeneralPolicyHome.PolID) ON tblCurrency.[ Code] = tblGeneralPolicyHome.[ Code]
WHERE (((tblGeneralPolicyHome.PolicyTerm)="NEW") AND ((tblPoliciesHome.Cancelled)<>-1) AND (([Buildings Limit]+[Contents Limit])>[buildings_content_nb]/[Rate to USD]) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].[polid])) OR (((tblGeneralPolicyHome.PolicyTerm)="NEW") AND ((tblPoliciesHome.Cancelled)<>-1) AND (([Buildings Limit]+[Contents Limit])>[buildings_content_nb]/[Rate to USD]) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].[polid])) OR (((tblGeneralPolicyHome.PolicyTerm)="RENEWAL") AND ((tblPoliciesHome.Cancelled)<>-1) AND (([Buildings Limit]+[Contents Limit])>[buildings_content_rnl]/[Rate to USD]) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].[polid])) OR (((tblGeneralPolicyHome.PolicyTerm)="RENEWAL") AND ((tblPoliciesHome.Cancelled)<>-1) AND (([Buildings Limit]+[Contents Limit])>[buildings_content_rnl]/[Rate to USD]) AND ((tblGeneralPolicyHome.PolID)=[forms]![Home Policy].[polid]))
ORDER BY tblPoliciesHome.SiteID DESC;

 

[MajP]

Yes. I believe this is what PHV was suggesting back in the first post. VB functions in queries like iif,dlookup,format,etc do not come cheap.
Inner joins are very fast, even if it requires a lot of reference tables.

 

[PHV]

I believe this is what PHV was suggesting
Sure ;-)