Filename: OptimizingLinkedAccess-SQLApplications.doc 3
SQL Server Technical Article
Writer: Andy Baron
Technical Reviewer: Gil Lapid Shafriri
Published: October 2006
Applies To: SQL Server 2005
Summary: One way to create applications that use Microsoft Office Access for creating user interfaces and that use SQL Server for data storage is to link Access tables to SQL Server tables. This is the type of application created by using the SQL Server Migration Assistant for Access. This white paper presents techniques for improving performance and updatability in Access applications that use tables linked to SQL Server.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted in examples herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.
Ó 2006 Microsoft Corporation. All rights reserved.
Microsoft, ActiveX, Visual Basic, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
All other trademarks are property of their respective owners.
Introduction 1
Understanding and Addressing Performance Issues 1
Diagnostic tools 2
Understanding dynasets 2
Adjusting dynaset behavior 5
Snapshot recordsets 6
Moving query processing to the server 7
Working with SQL Server views 8
View limitations 9
Working with pass-through queries 10
Using pass-through queries to optimize bulk updates 11
Server-side performance optimizations 11
Forms-based performance optimizations 12
Understanding and Addressing Updatability Issues 13
Specifying a unique index 13
Supporting concurrency checks 13
Overcoming query updatability limitations 14
Addressing Application Logic and Coding Issues 17
Data type incompatibilities 17
Default values 18
DAO code 18
Setting connection properties 18
Creating Unbound Access Applications 21
Populating lookup tables with pass-through queries 22
Query-by-form techniques 23
Caching server data in local tables 24
Managing state 24
Validating data 25
Saving data with stored procedures 26
Conclusion 30
Optimizing Microsoft Office Access Applications Linked to SQL Server 29
Microsoft® Office Access supports three primary options for connecting to data stored in Microsoft SQL Server™ databases:
· Use the Access database engine ¾ originally called the Jet database engine ¾ to communicate with SQL Server over ODBC connections.
· Create Access Project applications that use an OLE DB connection to communicate with SQL Server.
· Write Microsoft Visual Basic® for Applications (VBA) code that uses DAO, ActiveX® Data Objects (ADO), or middle-tier objects to connect and manipulate SQL Server data.
This paper focuses on the challenges encountered by Access developers who rely on the Access (Jet) database engine to connect to SQL Server over ODBC. The most common way this is done is by creating linked tables in Access that use the SQL Server ODBC driver to connect to tables in SQL Server databases.
The SQL Server Migration Assistant (SSMA) for Access enables you to convert an Access database to this type of application by moving your Access data to new SQL Server tables and linking to these tables. Any forms, reports, queries, or code that previously worked with the original Access tables are automatically connected to the new SQL Server tables.
In an application that uses linked SQL Server tables, two different database engines are at work: the Access/Jet database engine that runs on the 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 Access tables. This white paper discusses several of these issues and presents strategies for resolving them. Most of these issues relate to performance or updatability.
Developers often migrate data to SQL Server expecting an improvement in application performance. Although performance does often improve, there are many cases where it remains the same or even degrades. 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. This can happen even when joins or criteria appear to limit the result set to a small number of records. This occurs because sometimes the Access database engine determines that it cannot submit an entire query to SQL Server. 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.
The primary strategy for improving performance is to minimize the amount of data returned to the Access client and maximize the amount of processing that occurs on the server. To accomplish this, you need to be able to analyze the SQL commands that Access is submitting.
There are two tools that you can use to see how 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. Select a template that shows TSQL to see all the statements being processed by the server. From the client side, you can edit a Microsoft Windows® registry setting that allows you to see the commands that the Access database engine is submitting to ODBC.
As always, be very careful when editing the Windows registry. For more information on backing up and editing the registry, see How to Modify the Windows Registry (http://support.microsoft.com/kb/136393).
To enable tracing of all ODBC commands from the Jet database engine
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC
Access 2007 uses a customized version of the Jet database engine, named the Access Connectivity Engine (ACE), which is not shared with other Windows applications. If you are using Access 2007, navigate to the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\ODBC
After making this change in the registry, queries submitted to any ODBC data source are logged in a text file named Sqlout.txt. Unless you delete this file or its contents, it continues to grow as new queries are executed and the tracing activity degrades performance. It is very important to return to the Registry Editor and turn the feature off by changing the TraceSQLMode setting back to 0 when you are done testing. Running SQL Profiler also has a negative impact on performance, so try to avoid using it on a production server and close your Profiler traces when you are done testing.
Before you can make productive use of these diagnostic tools, you must understand how Access interacts with SQL Server. Without that understanding, the SQL statements that you see in Profiler traces and in Sqlout logs can be quite puzzling.
When you observe how Access communicates with SQL Server over ODBC, you will notice that most queries are executed very differently from the way you would expect. For example, if you migrate the Northwind data to SQL Server, link to the tables, and open the Shippers table in datasheet view while tracing is enabled, you probably expect to see a simple query such as SELECT * FROM Suppliers, or perhaps a query that includes the schema name with the table, dbo.Suppliers, and that explicitly names the three columns in the table. Instead, both the Sqlout.txt file and the SQL Profiler trace show that three statements are executed. Here is what is written to Sqlout.txt:
SQLExecDirect: SELECT "dbo"."Shippers"."ShipperID" FROM "dbo"."Shippers"
SQLPrepare: SELECT "ShipperID","CompanyName","Phone"
FROM "dbo"."Shippers"
WHERE "ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ?
OR "ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ? OR
"ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ? OR "ShipperID" = ?
SQLExecute: (MULTI-ROW FETCH)
SQLExecDirect indicates execution of a non-parameterized query. All the quotes that you see around object names are comparable to the brackets that Access uses (and that also can be used in SQL Server) to handle spaces or other illegal characters in names. SQLPrepare is used to define a parameterized query that is then executed with SQLExecute. The question marks are placeholders for parameters. MULTI-ROW FETCH indicates that parameter values are submitted, based on values retrieved by the first query, to retrieve up to ten rows.
A Profiler trace shows the three corresponding Transact-SQL statements that are processed on the server:
SELECT "dbo"."Shippers"."ShipperID" FROM "dbo"."Shippers"
declare @p1 int
set @p1=-1...
P_i_C