PRB: SHAPE Provider Pulls Down All Records in Child Table

ID: Q196968

The information in this article applies to:

Microsoft Data Access Components version 2.0

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 via 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.

REFERENCES

For additional information on SHAPE syntax, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q189657
   TITLE     : HOWTO: Use the ADO SHAPE Command

Keywords          : kbOLEDB kbVBp600 
Version           : WINDOWS:2.0
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: December 9, 1998