I have an extremely complicated form which allows a uses to create quotations.
On the form is a tab control with 8 pages, with sub forms and sub sub forms on each page.
It runs fine(ish) on my machine (i5 + 6gb ram) but I am concerned about performance on the clients network.
I just need to squeeze every bit of performance out of it.
When it loads, the filter is QuoteID = -1 so there are no records for the main form - this filter is amended by a combo afterupdate.
Is there any difference in performance when having the form / subform and sub sub forms record sources as save queries rather than embedded sql??
Any suggestions most welcome.
John
PS - NO, i can't simplify it - it has 100+ hours of time input so far and has been developed for the clients requirements - I have already reduced the functionality to the minimum!
Hi John,
No, I don't think there is a difference in performance whether you use a saved query object or an SQL statement as your form's record source.
Do you have proper indexing among your tables? If not, then creating indices for the fields that you commonly search should give you a little boost in speed.
Just my 2 cents...
QUOTE (john_willmott @ May 20 2012, 12:03 PM) 
Is there any difference in performance when having the form / subform and sub sub forms record sources as save queries rather than embedded sql??
No, I don't think there is a difference in performance whether you use a saved query object or an SQL statement as your form's record source.
Do you have proper indexing among your tables? If not, then creating indices for the fields that you commonly search should give you a little boost in speed.
Just my 2 cents...
Thanks for your reply.
If there is no difference in performance I will use embedded as the query window is now up to 100+ queries and is becoming a little cluttered.
Will check on indices - when you say search - do you mean as a criteria for retrieve also? eg a subform dealing with a particular type of activity will retrieve all the records where OperationTypeID = 2 so presumable this should be indexed?
A quote may have upto a few hundred records at the sub sub level, but there is a huge amount of specialist calculation at all three levels so there are many custom functions, many involving dao recordsets.
I have used option explicit and defined everything I can so we will have to wait and see!!
Any other suggestions most welcome!!
John.
If there is no difference in performance I will use embedded as the query window is now up to 100+ queries and is becoming a little cluttered.
Will check on indices - when you say search - do you mean as a criteria for retrieve also? eg a subform dealing with a particular type of activity will retrieve all the records where OperationTypeID = 2 so presumable this should be indexed?
A quote may have upto a few hundred records at the sub sub level, but there is a huge amount of specialist calculation at all three levels so there are many custom functions, many involving dao recordsets.
I have used option explicit and defined everything I can so we will have to wait and see!!
Any other suggestions most welcome!!
John.
What I usually do is do not load the sub forms on form load but load them when the tab becomes active
CODE
Private Sub TabControlName_Change()
Select Case Me.TabControlName.Value
Case 0
'Load and populate Subform
Case 1
'Load and populate Subform
Case 2
'Load and populate Subform
Case 3
'Load and populate Subform
End Select
End Sub
Select Case Me.TabControlName.Value
Case 0
'Load and populate Subform
Case 1
'Load and populate Subform
Case 2
'Load and populate Subform
Case 3
'Load and populate Subform
End Select
End Sub
Thanks for your reply.
Brilliant! - One of those very annoying 'Why didn't I think of that?' moments!
A dozen lines of code and it is refreshing at the blink of an eye.
Thanks again
John
Brilliant! - One of those very annoying 'Why didn't I think of that?' moments!
A dozen lines of code and it is refreshing at the blink of an eye.
Thanks again
John
QUOTE (john_willmott @ May 20 2012, 03:36 PM)
Brilliant! - One of those very annoying 'Why didn't I think of that?' moments!
I agree!
The value of this method has to be weighed up when you think about using it...
I wouldn't necessarily use it for something like a wizard where the user is going to have to hit every tab every time they open the form.... but if you have a form where a user is going to open the form and only go to 1 tab... then why load data into the others.
You could also expand the code to stop it refilling the sub form if it has already been filled (i.e. if the user has clicked away from the tab and then back onto it)
I wouldn't necessarily use it for something like a wizard where the user is going to have to hit every tab every time they open the form.... but if you have a form where a user is going to open the form and only go to 1 tab... then why load data into the others.
You could also expand the code to stop it refilling the sub form if it has already been filled (i.e. if the user has clicked away from the tab and then back onto it)
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.