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

Far from it, views are quicker than this! The OR makes it uncertain whether or not each field (e.g. ID) should equal a parameter or not (the value of parameters aren't known at prepare time), hence it cannot use an index for that field. This may to a certain extent be circumvented in stored procedures by using a number of IF statements with varying select statements rather than OR (or EXECUTE STATEMENT). On the other hand, you may query your view like 'where id = :id' (hence able to use an index) without any OR since you probably know what to ask for when building your select statement in your program.

Hence, based on the information you've supplied so far, I consider views to be superior to stored procedures in your particular case.

For The Firebird Book, take a look at
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_firebird_book#supplement

HTH,
Set


[Non-text portions of this message have been removed]