PRB: SHAPE Provider Pulls Down All Records in Child Table (196968)

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.