Microsoft KB Archive/196968

= PRB: SHAPE Provider Pulls Down All Records in Child Table =

Article ID: 196968

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q196968



SYMPTOMS
When using a SHAPE statement to retrieve parent and child records, the SHAPE provider pulls down all child records regardless of whether there is a matching parent record.



CAUSE
The SHAPE provider relies exclusively on the child statement to determine which records to return.



RESOLUTION
Modify the child statement to restrict the records returned to match only those related to the parent records.



STATUS
This behavior is by design.



MORE INFORMATION
The SHAPE provider is a data-provider-neutral service provider. It works by reading the parent and child records into temporary tables on the local machine and using the Client Cursor Engine to dynamically filter the child records according to the value of a field in the current parent record.

Because the provider is data-provider-neutral, it does not know anything about SQL syntax or how the parent and child statements relate to each other in terms of statements sent to the data provider.

For example, with the following SHAPE statement: SHAPE {SELECT * FROM Employees WHERE LastName='Davolio'} APPEND ({SELECT * FROM Orders} AS EmpOrders

RELATE EmployeeID TO EmployeeID) the SHAPE provider does not know how to modify the second SELECT statement in order to restrict the records to just Nancy Davolio. In fact, it does not even know that the parent records are being restricted at all. Because of this, all Orders for all employees are read into the local buffer.

There are two workarounds, which are detailed below.

Use a Parameterized SHAPE Statement
One workaround, especially if the parent recordset will just contain a single record, is to use a Parameterized SHAPE statement: SHAPE {SELECT * FROM Employees WHERE LastName='Davolio'} APPEND ({SELECT * FROM Orders WHERE EmployeeID = ?} AS EmpOrders

RELATE EmployeeID TO PARAMETER 0) In this case, the SHAPE provider reads the parent records first. It then queries for the child records as each parent record is visited. If the parent recordset contains a single record, then this is very efficient. If it contains more records, then a separate query to retrieve child records will be executed for each parent record visited. The child records are cached, so this does not add overhead if parent records are visited multiple times.

Use a JOIN in the Child Statement
Another workaround is to make the child statement more complex so that it reflects any restrictions placed upon the parent statement. This can be accomplished through a JOIN: SHAPE {SELECT * FROM Employees WHERE LastName='Davolio'} APPEND ({SELECT Orders.*

FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID WHERE Employees.LastName = 'Davolio'} AS EmpOrders RELATE EmployeeID TO EmployeeID) If the parent and child tables do not have a one-to-many relationship; that is, if EmployeeID is not a unique index or Primary Key of the Employees table, the following alternative syntax using a sub-select is more general and will work in all cases:  SHAPE {SELECT * FROM Employees WHERE LastName='Davolio'}   APPEND ({SELECT * FROM Orders WHERE EmployeeID IN

(SELECT EmployeeID FROM Employees WHERE LastName = 'Davolio')} AS EmpOrders RELATE EmployeeID TO EmployeeID) This is somewhat more expensive in terms of server processing, but makes up for it in terms of reduced network traffic.

NOTE: The SQL syntax given above will work with Microsoft SQL Server and Microsoft Jet. Other data providers may require different syntax to accomplish the same goals.

