0

I have a Delphi application which has 2 TADODataSet objects that reference CLIENT (Master) and ORDER (detail) data. I have the 2 components linked via the DataSource and Masterfields (using ClientCode) properties and everything functions as expected. However, I've noticed a performance issue recently and have discovered that even if only just one client record is returned for for the master record set, the entire orders table is returned and then filtered for the specific client. Is this how it should function? From tracing the database activity I can see that the following SQL is being executed:

-- the one client that I wanted to view
SELECT fields FROM Client WHERE ClientCode = 1;

SELECT fields from Order; -- entire orders table

I am using Delphi 2006 BDS

PDM
  • 503
  • 2
  • 12
  • 27
  • TCustomADODataSet descendants? AFAIK there's no way to add a TCustomADODataSet from the palette. – jachguate Jan 25 '13 at 17:17
  • 1
    If you are using `TADOQuery`, you can use parametrized queries instead of `MasterFields` – iMan Biglari Jan 25 '13 at 17:18
  • Sorry guys I meant TADODataSet. Have amended my question. – PDM Jan 25 '13 at 17:23
  • Yeah I think that parameterized queries is the way I am going to have to go. I think I overestimated MasterFields as being cleverer than it actually is. – PDM Jan 25 '13 at 17:25
  • `MasterSource` and `MasterFields` are still the way to go; you just connect them to an ADOQuery that accepts parameters for the fields listed in `MasterFields`, and they'll get passed automatically from the parent table to the child table's SQL parameters. – Ken White Jan 25 '13 at 18:14

2 Answers2

1

You just have to watch that Masterfield is named correctly with the fieldname of your masterdataset.

here id

bummi
  • 27,123
  • 14
  • 62
  • 101
  • I am still having problems getting this too work. I get an unspecified error when the child dataset is opened. The MasterFields property is set correctly. – PDM Jan 28 '13 at 01:32
  • What kind of error? Fieldtypes are matching? Parametertype known? SQL correct? – bummi Jan 28 '13 at 06:11
  • The field types are matching and the parameter type is set. The SQL it's trying to execute is SELECT * FROM Order WHERE ClientCode = ? It doesn't look like the param is being set correctly. – PDM Jan 28 '13 at 10:53
  • Remove ? with :xxx where xxx is the same like Masterfields. e.g. MasterFields=id will lead to "WHERE ClientCode =:id" . Masterfields=id;id2 will lead to "WHERE ClientCode =:id and AnotherField=:id2" – bummi Jan 28 '13 at 12:25
  • Hi Bummi, sorry, my previous comment wasn't very clear. The SQL of the CommandText property is (at design time) set to: **SELECT * FROM Order WHERE ClientCode = :ClientCode** However, when the SQL is executed and monitored via the OnWillExecute event of the TADOConnection, the SQL is then set to **SELECT * FROM Order WHERE ClientCode = ?** – PDM Jan 29 '13 at 00:59
  • SELECT * FROM **[ORDER]** WHERE ClientCode=:ClientCode – bummi Jan 29 '13 at 06:11
1

Make sure the parameter types are the same in both cases, i.e. master and child.

BobT
  • 66
  • 1
  • 1
  • 5