db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Nielsen <Thomas.Niel...@Sun.COM>
Subject Re: Derby database design issues
Date Thu, 02 Aug 2007 12:10:35 GMT
Hi John,

I'll give it a go, even though I'm still learning about derby.
Others may correct me if I'm wrong, or add more detail :)

> 1.       Is there any advantage (e.g. performance, security) to using 
> procedures over standard SQL?

Off the top of my head there are a couple of notable advantages of using 
stored procedures:

- separate the (biz)logic from the client(s).
This gives greater flexibility wrt clients, resulting in less coding, 
and I dare say simpler bugfixing, and (a lot) less administration.
There is one centralized location where a potential update/bugfix must 
be applied. No need to run around installing updated clients, so it 
eases administration. And it can be done "online", so it's 
instantaneous. Combined this probably makes it one of the more important 
advantages of stored procedures.

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

So, yes, in some cases there are advantages. In others, no there isn't. 
It depends on what you want to achieve.

> 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?

Thomas Nielsen

View raw message