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.
>
>
>