I have a form that has two subforms. Right now, the main form opens very slowly. I believe the reason is the two subforms. My question is, is it faster to load all records without filtering them, or to use a query to filter down records so that only "Active" records are shown? Right now, the subforms are based on queries that have no WHERE clause. In each of the tables, there is a field that is filled in last which if populated, would mean that the user won't need to handle that record anymore. I could test to see if that field is null = true. I just don't know if this increases or decreases the load on the database.
-
-
It depends, filtering can slow down if it is an unoptimized filter but can speed it up if optimized. In this situation, it would probably speed it up. The only way to know for sure is to test it. -
I guess I could test it. The only problem is right now I only have test information and not a lot of that so it may be hard to tell the difference.
What is an optimized/unoptimized filter?Comment
-
Well, it's more a scale of optimization.
Matching an exact value is quicker than matching a partial value.
Filtering on numbers is quicker than strings.
In is quicker than not in.
Indexed is faster than not indexed.
Fully covered index is faster than partial index.
Et cetera.
There's a lot of factors that affect the speed of a filter. In your case, you're just filtering on an exact value so I don't expect that to take too long.
However, this may all be moot. You said it's just test data and not a lot at that. I suspect then that it is not the amount of records that's slowing down the loading of the subform. It's likely to be caused by something else.Comment
-
Good point. I guess I was more worried about it slowing down even more once more data was put into it.
I assume thatwould fall into the faster end of "In is quicker than not in"?Code:IsNull(DateCompleted) = True
Comment
-
By in is quicker than not in, I meant this:
What you have there would be better expressed as:Code:field1 In (1,2,3,4) field1 Not In (1,2,3,4)
Calling a function on a field makes things slower. How much slower depends on the performance of the function.Code:DateCompleted Is Null
Comment
-
would be faster than using In() either way Seth (There's really no point is usingCode:[DateCompleted] Is Null
([DateCompleted] Is Null) = True, which is the actual equivalent of your suggestion).
As Rabbit's already indicated, checking fields from an index is going to be faster than those that aren't from an index. This is true regardless of the checks you use.Comment
-
Okay. I will test my database to see which things I can do to speed it up (or keep it from getting slower). Thanks for pointing out things to look for.Comment
-
Let me reiterate that I don't think it's the loading of the records that's causing the speed issue.
I would make a copy of the form and start deleting controls and code until I found the issue.Comment
-
Other than those subforms, there aren't many controls other than a tab control (three tabs).Comment
-
The subforms themselves must have additional controls. Don't forget those.
And it's not always a matter of how many controls there are. It could be one control and be slow if that one control has an unusually complex expression or code.Comment
-
Does code that isn't run make any difference? I have none or almost no code that runs on the opening of the form and subforms.Comment
Comment