Hi,

thanx Lucas and Helen for you replies.....

Helen, this was exactly, what I was searching for. I tried it already, and
it is working like a charm. I was so stuck in the master-links, that I
plainly forgot about the param-links...

And for those, who are interested in IB-performance.
The SP does 3 inner Joins which depend on each other, what means, that IB
has to build a 4X Cartesian Product.
The First Table contains 500k rows(customers), the second 2mill(orders), the
third 9mill(order-items) and the last 2k(article-list)....
The View needs around 2-5 seconds for building a result with a
singeleton-where-clause, and a few hours to execute completely, the SP
executes in less than 0.4 seconds for each row....All on an Athlon900 with
IDE-RAID0 and 768MB Ram....I simply ddn't want to mess with the view, as I
fear, that anybody could do a select *, and the server would stand for at
least an hour....the SP is much more safe and also performs better

With a similar setup, and a bit less data, MS-SQL 7 on a PIII-850 with
SCSI-Raid5 (LVD) needs between 0.3 and 0.5 seconds for the SP.....
so, IB does perform pretty well, or?


CU, Kaputnik
(Nick Josipovic)

nick@... <mailto:nick@...>
kap@... <mailto:kap@...>
-----------------------------------------------------------------------
superior Client/Server programming:
www.IBObjects.com <http://www.ibobjects.com/>
a nice Tool for Interbase:
www.InterbaseWorkbench.com <http://www.InterbaseWorkbench.com>

> -----Original Message-----
> From: Helen Borrie [mailto:helebor@...]
> Sent: Sunday, January 07, 2001 3:40 PM
> To: IBObjects@egroups.com
> Subject: Re: [IBO] SP's in Master-Detail Relationships...
>
>
>
> Don't forget about MasterParamLinks. That is for linking an input
> parameter for the detail to a FieldByName('xxx').AsSomething in the
> master. Still use MasterSource but use MasterParamLinks instead of
> MasterLinks.
>
> H.
>
> At 03:05 PM 07-01-01 +0100, you wrote:
> >Nick,
> >
> >you don't have to set it up as a master-detail relationship, you can do
> >it this way, too:
> >
> >Use the OnDataChangEvent of the Datasource that's bound to the
> >MasterQuery and add sth. like:
> >
> >procedure MyForm.DSOnDataChange ( ... )
> >begin
> > if Field = NIL then
> > begin
> > with detailQry do
> > begin
> > if not Prepared then Prepare;
> > Params[0].AsInteger := masterQry.FieldByName ( 'XX' ).AsInteger;
> > Open;
> > end;
> > end;
> >end;
> >
> >this way you set up your own master-detail handling.
> >
> >I forgot that the masterlinks-entry is providign a where clause, so this
> >shouldn't work with selecting from procedures, wehre you need an input
> >param.
> >
> >
> >Let me know if this suits you.
> >
> >Luc.
> >
> >Kaputnik schrieb:
> > >
> > > Hi..
> > > first,thanx for your reply
> > >
> > > Well, I initially created a view to do this. The problem is, that the
> > SQL is
> > > a join over 4 tables of which each table has at least 1
> million rows, and
> > > one has over 6 Million rows...And it is a cascaded Join, or 4
> sub_joins,
> > > thus very ineffective...Interbase is at its limits here, building a
> > > result-set....Even if I limit the view with a where-clause,
> it is plainly
> > > much(!!) slower than the SP.
> > >
> > > My problem is this: I have a master-table Customers with a
> CUST_ID as PK,
> > > and a detail view/SP referring to this CUST_ID...With the view, I can
> > simply
> > > generate Keylinks like View.Customer=Customer.Cust_ID...
> > >
> > > the SP neeeds a select * from Get_Customer_Articles(:paramCust_ID)
> > > and I simply don't know, HOW to build the keylinks and HOW to
> realize the
> > > parameter.....
> > >
> > > CU, Kaputnik
> > > (Nick Josipovic)
> > >
> > > nick@... <mailto:nick@...>
> > > kap@... <mailto:kap@...>
> > >
> -----------------------------------------------------------------------
> > > superior Client/Server programming:
> > > www.IBObjects.com <http://www.ibobjects.com/>
> > > a nice Tool for Interbase:
> > > www.InterbaseWorkbench.com <http://www.InterbaseWorkbench.com>
> > >
> > > > -----Original Message-----
> > > > From: Lucas Franzen [mailto:luc@...]
> > > > Sent: Saturday, January 06, 2001 4:09 PM
> > > > To: IBObjects@egroups.com
> > > > Subject: Re: [IBO] SP's in Master-Detail Relationships...
> > > >
> > > >
> > > >
> > > >
> > > > Kaputnik schrieb:
> > > > >
> > > > > Hi,
> > > > > I have one problem......
> > > > >
> > > > > I have a database with between 1-6 Mill. Rows per Table
> > > > > In order to do a small Master-Detail Lookup, I created a
> SP with a
> > PK as
> > > > > Input-Parameter, which is doing a Join over 4 Tables. Doing
> > > > this in a View
> > > > > would kill the IB-Server for a long while, although the
> > > > Query-Optimizer does
> > > > > it's job astonishingly good and uses all inidzes as intended.
> > > > >
> > > > > This SP is intended for a Master-Detail-Relationship, where the
> > user can
> > > > > browse through the custoemrs, and view all the different
> > > > articles, which he
> > > > > ordered in all his past orders....
> > > > > I would like to know, if and how it would be possible to include
> > this SP
> > > > > into an IB_Query to have the MD-updates handled automatically....
> > > > > The SP is selectable and has only one input-paramter (the
> > > > Customer-No.) and
> > > > > 4 output-variables....
> > > > > Is it possible to do this like this? Select * from
> > > > SP(paramters) and handle
> > > > > the rest in the master-links??
> > > >
> > > > Selecting from a SP is like slecting from a table or view.
> > > >
> > > > You do just enter:
> > > >
> > > > SELECT <FIELDS> from SP_NAME ( :PARAM1, :PARAM2, ...)
> > > >
> > > > The MasterLinks should work, too, though I haven't tested it so far.
> > > >
> > > > One problem I experienced with selecting data from a SP
> with an IB_Query
> > > > was that you better leave the KeyLInks property empty and set
> > > > KeyLinksAutoDefine to TRUE.
> > > > In my case stepping throught he result set, took appr. 1 min for 100
> > > > records when I self-defined the KeyLink (it was the PK of
> the underlying
> > > > table).
> > > > When using KeyLinkAutoDefine it went back to normal speed.
> > > >
> > > > But I'm not sure why you do want to use a SP for selecting
> the detail
> > > > data.
> > > > You should be able to get the 4 table join within a
> 'regular' query just
> > > > as fast as from a SP.
> > > >
> > > > Im my experience SP's aren't faster in selecting, but they
> do offer the
> > > > opportunity to write more complex SQL-Statements (you can handle
> > > > IF..THEN, etc).
> > > >
> > > > Regards
> > > > Luc.
> > > >
> > > >
> > > >
> >
>
> All for Open and Open for All
> InterBase Developer Initiative · http://www.interbase2000.org
> _______________________________________________________
>
>
>