I don´t know how firebird check it internally but you can try swaping
the where clause to

where (:name_or_code=0 and supp_code=:loccode) or
(supp_name=:locname)

but if, and only if, :name_or_code is always 1 or 0
this will remove some checks but I must try on your "real World" data.

in other hand, pehaps removing the "else if ..." part and leaving
only a "else begin end" may improve velocity but may be not too much
(I think)...

--- In firebird-support@yahoogroups.com, Alan.Davies@... wrote:
>
> Yes thanks emb, tried that with a real-world sp and the execute
time
> goes up from 219 ms to 750 ms so it is not as efficient - I guess
I'll
> stick with what I have already.
> --
> Alan J Davies
> Aldis
>
>
>
> Quoting emb_blaster <EMB_Blaster@...>:
>
> > you´ve tried this?
> > begin
> > for select supp_code,supp_name
> > from supplier
> > /* next line is the only difference */
> > where (supp_code=:loccode and :name_or_code=0) or
> > (supp_name=:locname and :name_or_code=1)
> > into :code,:name
> > do
> > suspend;
> > end
> >
> > this may solve, but I don´t know if it will be fastest than your
code.
> > more comments? anyone?
> >
> > --- In firebird-support@yahoogroups.com, Alan.Davies@ wrote:
> >>
> >> Hi - I'm using FB 2.1 on various systems, e.g. W2003 server, and
am
> >> looking to improve readability and reduce the possibility of
errors
> >> within stored procedures.
> >> A very simple example would be:
> >> 3 parameters passed to sp - name_or_code, locname and loccode
> >> begin
> >> if (:name_or_code=0) then /* code */
> >> begin
> >> for select supp_code,supp_name
> >> from supplier
> >> /* next line is the only difference */
> >> where supp_code=:loccode
> >> into :code,:name
> >> do
> >> suspend;
> >> end
> >> else if (:name_or_code=1) then /* name */
> >> begin
> >> for select supp_code,supp_name
> >> from supplier
> >> /* next line is the only difference */
> >> where supp_name=:locname
> >> into :code,:name
> >> do
> >> suspend;
> >> end
> >> end
> >>
> >> Is it possible to combine these into one statement with e.g. a
case
> >> statement surrounding the "where .." or by setting a variable
with
> >> the relevant condition?
> >> Not possible to alter the way the parameters are passed, they
come
> >> from Delphi apps written by others.
> >>
> >> --
> >> Alan J Davies
> >> Aldis
> >>
> >
> >
> >
>