PRB: SHAPE Provider Pulls Down All Records in Child Table

View products that this article applies to.

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.

Back to the top

CAUSE

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

Back to the top

RESOLUTION

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

Back to the top

STATUS

This behavior is by design.

Back to the top

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.

Back to the top

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.

Back to the top

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.

Back to the top