At 08:29 AM 12/06/2007, you wrote:

>helen
>
>thanks very much for your considered reply. as a result of this thread
>i have decided to use SPROCs for insert/update/delete statements. for
>the select statements i am going to either use (a) views (b) standard
>sql or (c) SPROCS utilising input parameters. (d) a bunch of different
>sprocs e.g. SP_USER_S_ID, SP_USER_S_CLIENTID, etc.
>
>the SPROC input parameter solution would mean having input parameters
>and dynamic WHERE clauses like this:
>
> where (:in_id is null or id = :in_id)
> and (:in_clientid is null or id_client = :in_clientid)
> and (:in_username is null or u.username = username)
>
>i suspect this would yield a faster performance than using a view?
>nevertheless i am still going to use views for the sake of simplicity.

Not necessarily. Views use indexes and, in many cases, if an index
can be used it will improve performance. As long as the select
statements in your procs use indexes just as effectively as the
views, they might be faster or you might notice no difference. This
question isn't unlike "how long is a piece of string?", really. Only
your reality testing can determine the relative performance qualities
of your requests with your data.

>select statements by nature need to be flexible. i.e. if i have a
>table then it is very conceivable that i may want to perform selects
>on a variety of different criteria (PK, FK, etc. so i am wondering
>where SPROCS fit into this with Firebird 1.5? are they inadequate for
>the job? i am just trying to understand better. thanks.

Well, the SQL language was invented for flexibility. The PSQL
extensions should enhance flexibility, not inhibit it. In my
opinion, SPs are for doing stuff that benefits from program
logic: data processing that, if left at the client, creates an
opening for the kind inconsistency that can arise when you are
relying on n different application programmers who 1) might have an
imperfect understanding of how the data hangs together and 2) might
be hitting the same data at different times from different
directions. SPs can ensure that it's the database that makes the rules.

SPs (again, IMO) are not very beneficial for queries that are totally
ad hoc, which seems to be what you want. The introduction of EXECUTE
STATEMENT addressed the *demand* from developers to use SPs that way
so, if you're determined to do it, you can.

Myself, I take comfort from knowing exactly what will happen as the
result of invoking a SP. I don't like EXECUTE STATEMENT. That's a
generational thing, probably. (As a grandmother twice over, I'm
trying to abandon tightrope-walking as a pastime).

./heLen