Data designer
Hi!
Consider two tables:
PARENT(ID,NAME) and CHILD(ID,NAME,PARENT_ID).
The goal is to build master-detail report.
I add those two tables in Data Designer and make the link
(PARENT.ID->CHILD.PARENT_ID).
When I run a report Report Builder executes two queries
1) select id,name from parent
2) select id,name,parent_id from child
and then combines retreived data by himself.
There is no problem in this case.
If I add condition NAME='Some Name' for table PARENT , then RB runs two
queries
1) select id,name from parent where name='Some Name'
2) select parent.id,child.id,child.name,child.parent_id
from parent,child
where child.parent_id=parend.id and parent.name='Some Name'
and then combines retreived data by himself.
The problem arises, when CHILD is a VIEW, not a table. In my case CHILD is a
very complicated view, and Oracle doesn't use indexes on field PARENT_ID
when executes second query.
Before I used SQL tracer, I thought that Master-Detail in RB works little
different: for each row in first dataset second query is executed
select id,name,parent_id from child where parent_id=:parent_id.
So, the question is: Is there any possibility to make master-detail in RB
work like normal master-detail in Delphi?
Thank you.
Consider two tables:
PARENT(ID,NAME) and CHILD(ID,NAME,PARENT_ID).
The goal is to build master-detail report.
I add those two tables in Data Designer and make the link
(PARENT.ID->CHILD.PARENT_ID).
When I run a report Report Builder executes two queries
1) select id,name from parent
2) select id,name,parent_id from child
and then combines retreived data by himself.
There is no problem in this case.
If I add condition NAME='Some Name' for table PARENT , then RB runs two
queries
1) select id,name from parent where name='Some Name'
2) select parent.id,child.id,child.name,child.parent_id
from parent,child
where child.parent_id=parend.id and parent.name='Some Name'
and then combines retreived data by himself.
The problem arises, when CHILD is a VIEW, not a table. In my case CHILD is a
very complicated view, and Oracle doesn't use indexes on field PARENT_ID
when executes second query.
Before I used SQL tracer, I thought that Master-Detail in RB works little
different: for each row in first dataset second query is executed
select id,name,parent_id from child where parent_id=:parent_id.
So, the question is: Is there any possibility to make master-detail in RB
work like normal master-detail in Delphi?
Thank you.
This discussion has been closed.
Comments
When using external DataSets (i.e. not using the Data designer) RB supports
two type of linking: DataPipline linking and Delphi's DataSet linking.
DataPipeline linking requires that the detail data be sorted on the same
fields as the master, plus sorted on the linking fields. With DataPipeline
linking the performance is much faster because the detail query is fired
only one time. With Delphi's TDataSet linking the detail query fires once
per master record.
RB's Data designer uses DataPipeline linking. To view the linking SQL that
is generated for the detail dataview, position the mouse over the dataview
and press ctrl + left mouse button.
Alternatives would be to not use the Data Designer at all, just use external
datasets. Or perhaps create some custom dataviews that do not use
datapipeline linking. See RBuilder\Demos\EndUser\Custom DataViews and the
DADE thread of the Tech Tips newsgroup.
--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
You are not right! A have an example, when consequative execution of Detail
query is MUCH faster, than yours... The thing is in SQL server! Server
builds plan of a query regarding on his own "thoughts"
tables or views may be very unclear for user. RB can't give a chance to make
tips for Oracle for example.
You should consider two ways of Data designing, both: existing and usual
Master-Detail like in Delphi. As a programmer, I want to fully control, what
queries are executed, RB give me no chance
Truly yours, Maxim Balagansky.
See RBuilder\Demos\EndUser\Custom DataViews if you would like to create your
custom dataviews.
--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com