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.