Improve performance of tables linked to SQL Server

If the data in your front-end/back-end application consists only of Microsoft SQL Server tables, you can use either a Microsoft Access project or a Microsoft Access database as the front end of your application. However, by using an Access project, you will benefit from the advantages of projects such as a larger number of tables and larger database size. If you want the data in your front-end/back-end application to consist of both Microsoft Access tables and Microsoft SQL server tables, you must use an Access database as the front end.

If you're connecting to an external SQL database table, you can achieve the best performance results by linking to the SQL tables instead of opening the tables directly. You can open external SQL tables directly only by using Microsoft Visual Basic code. Linked tables are considerably faster, more powerful, and more efficient than directly opened tables.

Additional performance tips

• Retrieve only the data you need. Design your queries to limit the number of records that you retrieve, and select only the fields you need, so that Microsoft Access can transfer as little data as possible over the network.
• Don't use updatable result sets (dynasets) if you're not retrieving many records, are not updating the data, and don't need to see changes made by other users. To prevent result sets from being updated in a form, set the form's RecordSetType property to Snapshot. Snapshots are faster to open and scroll through than dynasets.
• If you need to retrieve a large number of records, a dynaset is faster and more efficient than a snapshot. For example, moving to the end of a snapshot requires the entire result set to be downloaded to the local computer, but with a dynaset only the last screen of data is downloaded to the local computer. In addition, the fastest way to add new records to a table, form, or query is to click Data Entry on the Records menu. (Data Entry isn't available if the RecordsetType property is set to Snapshot.)
• Use cache memory. If you will reuse the most recent data from the server while the application is running, it's faster to retrieve a single large chunk of data (many rows) and store it in a cache than it is to retrieve many individual rows. Microsoft Access forms and datasheets automatically use a cache. If you are retrieving data by using a Recordset object created in Microsoft Visual Basic, you can use the CacheSize property to specify how many records to retrieve at one time into local memory.
• For bulk update queries against ODBC data sources, optimize performance on the server by setting the FailOnError property to Yes.
• Avoid using queries that cause processing to be done on a local client computer. When accessing external data, the Jet database engine processes data locally only when the operation can't be performed by the external database server. Query operations performed locally (as defined by the SQL commands used to implement them) include:
  • JOIN operations between tables from different remote data sources. (For example, assume that the join involves a local table or query with few records and a remote table with many more records, and the remote table's join field is indexed. In this case, Access returns only the records that match the local table or query, thus greatly improving query performance.)
  • JOIN operations based on a query with the DISTINCT predicate or a GROUP BY clause.
  • Outer joins containing syntax not supported by the server.
  • DISTINCT predicates containing operations that can't be processed remotely.
  • The LIKE operator used with Text or Memo fields (may not be supported by some servers).
  • Multiple-level GROUP BY arguments and totals, such as those used in reports with multiple grouping levels.
  • GROUP BY arguments based on a query with a DISTINCT predicate or a GROUP BY clause.
  • Crosstab queries that have more than one aggregate, that have field, row, or column headings that contain aggregates, or that have a user-defined ORDER BY clause.
  • TOP n or TOP n PERCENT predicates.
  • User-defined functions, or operators or functions that aren't supported by the server.
  • Complex combinations of INNER JOIN, LEFT JOIN, or RIGHT JOIN operations in nested queries.