In some cases, performance of certain queries degrades to an unacceptable level.The major cause of query performance degradation is when a query involving very large tables requires that all of the data from one or more tables be downloaded to the client.Before you can make productive use of these diagnostic tools, you must understand how Office Access interacts with SQL Server. The question marks are placeholders for parameters.Without that understanding, the SQL statements that you see in Profiler traces and in Sqlout logs can be quite puzzling. MULTI-ROW FETCH indicates that parameter values are submitted, based on values retrieved by the first query, to retrieve up to 10 rows.Instead, it submits multiple queries, often including queries that request all of the rows in a table, and then it combines or filters the data on the client.If the criteria require local processing, even queries that should return only selected rows from a single table can require that all the rows in the table be returned.
From the client side, you can edit a Microsoft Windows registry setting that allows you to see the commands that the Office Access database engine is submitting to ODBC.If there are two columns in the bookmark, 20 values are passed in at a time to specify the next 10 rows.In this example, there are only three rows in the table, so the final bookmark value, 3, which corresponds to the last Shipper ID in the table, is submitted eight times, because the rows are always fetched in sets of 10.There are two tools that you can use to see how Office Access is communicating with SQL Server.To listen in on the conversation from the server side, you can open the SQL Server Profiler and create a new trace.In an application that uses linked SQL Server tables, two different database engines are at work: the Office Access/Jet database engine that runs on the Office Access client and the SQL Server database engine.The interaction of these two engines can sometimes yield results that are inferior to those obtained by using only the Jet database engine with native Office Access tables.This white paper presents techniques for improving performance and updatability in Office Access applications that use tables linked to SQL Server.(22 printed pages) Click here for the Word version of this article.Introduction Understanding and Addressing Performance Issues Understanding and Addressing Updatability Issues Addressing Application Logic and Coding Issues Creating Unbound Office Access Applications Conclusion Microsoft Office Access supports three primary options for connecting to data stored in Microsoft SQL Server databases: This paper focuses on the challenges encountered by Office Access developers who rely on the Office Access (Jet) database engine to connect to SQL Server over ODBC.The most common way this is done is by creating linked tables in Office Access that use the SQL Server ODBC driver to connect to tables in SQL Server databases.