At 09:47 AM 4/01/2007, you wrote:

>hi there
>
>i have been asked to write a reporting application. im wondering how
>best to use the firebird 1.5 database. the reports in question are all
>subject to user selectable filters. the choices i can see are dynamic
>sql or sp. i would prefer sp but i have a question first:
>
>where ((:inputfilter is null) or (... = :inputfilter))
>
>if i use this kind of statement in my SP to dynamically assign
>optional filters does this mean the SP is not optimised?

"Optimisation" isn't a thing that either happens or doesn't
happen. It refers to the series of choices made by set of routines
known as "the optimiser" that tries to identify the most efficient
way to return the set requested in a SELECT statement. In the case
of a selectable SP, the optimiser has nothing to "optimise", since
the output is data that doesn't exist - it's "virtual" - it has no
underlying metadata objects that could be organised in alternative
ways to be more or less efficient.

However, most selectable SPs manufacture their output by executing
DSQL statements. The same optimisation process occurs whether these
statements are executed from a SP or from a dynamic request and the
quality of the optimisation is as good (or bad) either way.

>also my list of filters for each report may grow in the future. so is
>it better to do dynamic sql?

It's neither better nor worse, from a performance point of view at least.

>but then again sp is more secure?

"Secure from malicious intruders"? Again neither better nor
worse. Writing an application that fetches data via a SSP doesn't
stop anyone from accessing your data if you let them steal your database file.

For providing ad hoc query capability in your applications, it *can*
be safer, from the point of view of avoiding the situation where an
inept user would be otherwise capable of constructing runaway queries
--- as long as you code it that way, of course! :-)

>any help for me to arrive at a decision very mush appreciated.

I suggest that the decision should be: don't decide! It's unlikely
that you could make a hard and fast rule that would apply to all of
your reporting requirements. If a SSP can simplify what the report
procedure has to do with the output and/or reduce the number of
records crossing the wire, it can be a plus....on the other hand, if
the users need the option to specify the ordering or grouping
criteria for the records, a SSP is likely to perform worse: you
can't use SP input arguments to modify the output order internally
and applying ordering and grouping criteria to SSP output can be
noticeably tortuous.

./heLen