In a client/server application, the general rules of thumb for reducing network traffic and optimizing application performance are: Do your data processing work on the server, minimize roundtrips to the server to access the data, and limit the data you retrieve from the server. This topic provides reference information about:
Understanding OLE DB and the client/server environment
Limiting data retrieved from the server
Optimizing the Microsoft SQL Server database
A Microsoft Access project uses OLE DB to connect and communicate with an SQL Server database. Microsoft designed the OLE DB architecture to work efficiently in a client/server environment.
When you access data through a form or datasheet, OLE DB retrieves an updateable snapshot recordset from the SQL server database (up to the maximum record limit) and caches the data on the client — making one roundtrip to the server. Access retrieves the data asynchronously, which means that you can continue to interact with Access while the data is being loaded. When you work with the data on the form or datasheet, whether browsing, filtering, sorting, finding and updating data, you are working with the data that is cached on the client.
Return to top
To process data on the server, you can use stored procedures and triggers, and you can sort data on the server before retrieving it.
Stored procedures are a precompiled collection of one or more SQL statements and optional control-of-flow statements that can accept parameters and that are processed as a single unit by Microsoft SQL Server. A stored procedure resides in the SQL Server database. Because a stored procedure is compiled on the server when it is created, it executes faster than individual SQL statements. And because a stored procedure runs on the server, it takes advantage of the extra processing power of the server. Learn about working with stored procedures.
Triggers are a special kind of stored procedure that automatically execute when you update, insert, or delete data. Use triggers to enforce business rules and restrictions that are more complex than those defined with check constraints. Unlike check constraints, triggers can reference columns in other tables. For example, a trigger can roll back updates that attempt to apply a discount (stored in a discounts table) to books (stored in a titles table) with a price of less than $10. Learn about working with triggers.
Sort records on the server by using a stored procedure, or SQL statement stored in the RecordSource property of a form or report (which executes on the server). Sorting records on the server is another way to take advantage of the extra processing power of the server. Learn about ways to sort.
Return to top
As much as possible, limit the amount of data your application retrieves from the SQL Server database. Use views, stored procedures, server filters, and SQL statement WHERE clauses to limit the data you or your users see in a form or datasheet to only what is needed. In general, avoid designing application scenarios where users browse the database in unrestricted fashion.
It's usually a good idea to use the maximum record limit button or the MaxRecords property to limit the data you or a user can see in a form or datasheet.
You can also use server filters to limit records before they are retrieved from the database on the server, and filters to further limit records on the client. Learn about creating filters. You can define a filter in the ServerFilter property of a form or report. You can also optimize the performance of lists of values in Server Filter by Form and Filter by Form windows by modifying the FilterLookup property to control the number of values displayed if the values are retrieved from either the server or client. Learn about optimizing filter performance.
Return to top
The following are general guidelines for optimizing SQL Server database performance. For detailed information on improving SQL Server database performance, see the SQL Server documentation.
Return to top
In general when you design a form, determine the data and functionality needed, and delay asking the server for this data and functionality until the information is requested by the user. For example, create a form so that it doesn't require data to be retrieved from the server during the form-opening process. For example, you can add a command button to the form to load data on the form.
Display fields that take a long time to retrieve data from the server, such as text and image fields, only when requested. You can use the following techniques:
Use SQL UPDATE and DELETE statements with the appropriate WHERE clause to update or delete multiple records that meet the same criteria. This is more efficient than opening a recordset and performing the updates or deletions one record at a time.
Return to top
Instead of printing a long report, break up reports into smaller subsets of data, by using views, stored procedures, and SQL statements as your record source, or by using filters.
Use layout preview instead of print preview when you design your reports. You can take a quick look at the layout, which includes just a sample of the data in layout preview. When you are done working on the layout of the report, verify the data by opening the report in print preview.
You typically create and distribute reports on a schedule according to regular business cycles, such as weekly stock-level reminders, monthly sales reports, or quarterly financial statements. Create and distribute these reports as report snapshots in a batch file. Users can use the Snapshot Viewer to view and print the reports. When your users need up-to-the-minute reporting, use grouped data access pages.
Return to top
Tips on optimizing performance in a data access page
Optimizing Visual Basic code performance
Return to top