John C. Turnbull wrote:
- the database engine *may* be able to precompile the stored procedure,
and reuse that precompiled block at a later invocation of the stored
procedure for a (very slight) performance gain. Impact depends on the
nastyness of the expression and the engine itself.
This is no different from what you would get from a PreparedStatement
in the same engine though.
[JCT] I guess then that if the procedure is being used heavily and multiple
times there might be a real performance gain from using procedures over
straight SQL.
Most likely. There is really no need to use stored procedures, unless
administrative ease is a necessity. You will get the same advantage (for
sure) if you use straight SQL in a PreparedStatement that you keep a
handle to in your jdbc client.
The purpose of PreparedStatements are just that - they are precompiled
(or prepared if you like) - and ready to use to harvest the performance
gain.
2. Is it possible to disable direct access to tables and provide
the only access via procedures as it is in other RDBMS?
Except using GRANT/REVOKE? I don't think so.
But you might be able to do it anyway - grant access to the table(s)
only to a "sqlproc" (or whatever) user, and then execute the stored
procedure as that user?
[JCT] In Sybase what I would do would be to revoke access to all tables for
a particular group to which all users of the system would belong. Then I
would grant access to the set of procedures to that same group. The result
was that all data manipulations were of a pre-defined type and it was
impossible for a user to do anything undesirable. I see that Derby doesn't
seem to have the user group concept so to achieve the same effect every user
would have to have their privileges to the tables and procedures set
individually. No big problem I guess but having groups would be nice.
I'm still new to derby, but I think you are correct in that derby
doesn't have usergroup access implemented (yet?).
Cheers,
Thomas
