After modifying my database my response time is very slow. Can you help me to optimize the response time. The database contains 1-table city,state,zipcode; 1-table employee info including Territory # and Desc.; 1-table territory # and zipcode. The user presses a command button that takes them into a form. The user types in a zipcode and presses enter. The main form displays the city, state, zipcode as well as 3 additional subforms. Subform1 - Division A, responsible sales rep, voice mail number, territory #, description, their manager. Subform2 - Divison B, responsible sales rep, voice mail number, territory #, description, their manager. Subform3 - Managers employee record showing their voice mail extension. The response time is much slower doing the database this way rather than having two separate forms and choosing to go into Division A form or selecting to go into Divison B form. How can I speed up the response time in my revised database. Also, I will be adding another division or two. Thanks, Karen
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Optimize Response Time
- Thread starter Haleigh
- Start date
If the problem really did happen as a result of adding the Division B subform (as opposed to it being a coincidence), then you probably have a problem with how your Record Source properties are set up. A first guess would be that your main form's Record Source is a query, and the query contains more tables than you need. You only need your city/state/zip table in the record source for the main form.
If you put one of your subform tables in the main form's record source, and particularly if you don't have any join lines connecting the tables, the query will produce one row for each possible combination of rows in the two tables. If the city/state/zip table had 1500 records and the other table had 100 records, the query would produce 150,000 records! That will slow you down a bit!
If you put two of the subform tables in the main form's record source, it gets geometrically worse!
So why don't you tell us what you've got in the record source for the main form and each subform, and for each query used as a record source tell us the SQL statement (available by choose View>SQL View from the menu when you're looking at a query). If any of your queries contain multiple tables, tell us how they are connected by join lines, if any. And finally, tell us how many rows there are in each of the tables involved.
If I'm wrong, and this isn't the problem, then you probably just need to set up some indexes, which is easy. But I'll still need to know the above information to tell you how to add the indexes. Rick Sprague
If you put one of your subform tables in the main form's record source, and particularly if you don't have any join lines connecting the tables, the query will produce one row for each possible combination of rows in the two tables. If the city/state/zip table had 1500 records and the other table had 100 records, the query would produce 150,000 records! That will slow you down a bit!
If you put two of the subform tables in the main form's record source, it gets geometrically worse!
So why don't you tell us what you've got in the record source for the main form and each subform, and for each query used as a record source tell us the SQL statement (available by choose View>SQL View from the menu when you're looking at a query). If any of your queries contain multiple tables, tell us how they are connected by join lines, if any. And finally, tell us how many rows there are in each of the tables involved.
If I'm wrong, and this isn't the problem, then you probably just need to set up some indexes, which is easy. But I'll still need to know the above information to tell you how to add the indexes. Rick Sprague
- Status
Similar threads
- Locked
- Question
- Locked
- Question