How Jet Interacts with ODBC Data Sources

When you double-click a linked table to open it, Access retrieves the primary key information for the table and then retrieves records from the table a few at a time. For example, after double-clicking a link to the Orders table in the NorthwindCS database on SQL Server, Access would first send the following query:

SELECT "dbo"."Orders"."OrderID" FROM "dbo"."Orders

This gives Access a full list of the unique record identifiers for the data source. Once Access has this information, it can then retrieve the full record data for display or processing. In this case, the next query sent would look similar to the following:

declare @P1 int set @P1=3

exec sp_prepexec @P1 output, N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int', N'SELECT

"OrderID","CustomerID","EmployeeID","OrderDate","RequiredDate","ShippedD ate","ShipVia"/"Freight"/"ShipName"/"ShipAddress"/"ShipCity"/"ShipRegion ","ShipPostalCode","ShipCountry" FROM "dbo"."Orders"

Datasheet View

Figure 17-11

Datasheet View

Figure 17-11

WHERE "OrderlD" = @P1 OR "OrderlD" = @P2 OR "OrderlD" = @P3 OR "OrderlD" = @P4 OR "OrderlD" = @P5 OR "OrderlD" = @P6 OR "OrderlD" = @P7 OR "OrderlD" = @P8 OR "OrderlD" = @P9 OR "OrderlD" = @P10', 10249, 10251, 10258, 10260, 10265, 10267, 10269, 10270, 10274, 10275 select @P1

The sp_prepexec stored procedure prepares a SQL statement for use by subsequent queries as well as accepts parameter input to retrieve the first few rows (those with OrderlD 10249-10275). After this statement is run, Access can then just use the sp_execute procedure to retrieve small batches of rows at a time.

exec sp_execute 3, 10280, 10281, 10282, 10284, 10288, 10290, 10296, 10309, 10317, 10323

The exact query text used is specific to the back-end database server and is handled by the ODBC driver for the DSN. Although the specifics are different, Access (Jet) uses the same overall process of getting the primary key information first and then retrieving batches of rows based on the key information.

When you open a query instead of a table, the behavior is typically multiplied for each table in the query and Access will usually do the following:

1. Request primary key data for each table separately

2. Join the key data locally on the client machine

3. Request all needed field data from each table separately based on the key field

4. Join the requested data together in a local recordset and display to the user

The end result is that if you have large compound primary keys defined for each table, Access can pull down a lot of data that needs to be joined locally before it even begins to retrieve data that will eventually be displayed to the user. If you have a table with 13 fields but 12 fields comprise a compound primary key, Access will end up bringing down the 12 primary key fields twice, once to get the primary key data by itself and a second time to get data for display to the user.

Under certain circumstances, Access will have the primary key joining done on the server. Unlike the above example where Access retrieved all the primary key data for each table and then joined it locally, if all the tables in a particular query are based on the same DSN, Access can sometimes pass a WHERE clause to join the data on the server. For example, if you created a local query in an MDB file that was based on linked Products, Suppliers, and Categories tables from the NorthwindCS database on SQL Server, and all three tables were based on the same DSN, a query similar to the following may be executed in step 1, above:

SELECT "dbo"."Products"."ProductID"/"dbo"."Categories"."CategoryID"/ "dbo"."Suppliers"."SupplierID"

FROM "dbo"."Categories","dbo"."Products"/"dbo"."Suppliers" WHERE(("dbo"."Products"."CategoryID" = "dbo"."Categories"."CategoryID") AND ("dbo"."Products"."SupplierID" = "dbo"."Suppliers"."SupplierID"))

This effectively causes step 2, mentioned above, to be executed on the server. The benefit here is that only the primary key data that is necessary for the join will be passed over the network and brought down to Access. Although this is not as ideal as having all query processing happen on the server, it can dramatically improve performance depending on how your tables are structured.

The most important element here is that all tables must be based on the same DSN. If two tables are from the same SQL Server database but one table uses a file DSN and the other uses a system DSN, the less efficient process will be used. The more efficient processing is not guaranteed and will depend on other factors but it won't happen at all if there is more than one DSN. Always base linked tables from the same database using the same DSN if at all possible.

As you can see, although a robust server database is being used, most of the processing still happens locally when working with linked tables. The benefit with this design is that Jet makes it very easy to create queries that can join multiple remote data sources. This ease of use, however, comes with the price of inefficiency in many cases, and the increased network traffic that can result.

0 0

Post a comment