Subject Re: Design advice
Author jgeer2002
--- In, Helen Borrie <helebor@t...>
> At 11:42 AM 20/08/2005 +0000, you wrote:
> >I'm developing a commercial application that uses Firebird. At the
> >moment I have all my queries coded in stored procedures.
> This is extremely unnecessary.
> >The process to deploy updates to my app I think is going to be
> >painful, because for example, in order to modify a field I have to
> >delete every stored procedure that reference that field, make the
> >modifications and recreate the stored procedures.
> Exactly.
> >I'm thinking of dropping all my stored procedures and use dynamic sql
> >directly from my code instead.
> >
> >I would like to know your opinion about this. What drawbacks can I
> >expected?
> My opinion is that the sooner you replace any selectable SPs that do
> nothing except encapsulate regular SELECT queries in permafrost, the
> better. Keep the ones that provide workarounds for retrieval logic
> SQL doesn't support, or supports badly; ditch the rest.
> The same goes for executable SPs that perform plain-vanilla DML.
They are
> a waste of resources and they add dependencies that will drive you
> with their sheer volume. Use executable procedures to perform
sequences of
> operations that can be kicked off with one set of parameters
> or not) from the client.
> The biggest drawback with dynamic queries is the developer.
> To be more specific, you get a lot of developers who have only used
> databases before and try to make Firebird work like Access or, worse, a
> spreadsheet application. They don't understand parameterised
> they don't understand transaction isolation or atomic tasking or
> of data-intensive work to the server. And, no kidding, sometimes
they don't
> even understand SQL and have been observed to argue that they don't
want to
> be put into the position of having to learn it.
> Use parameters for your dynamic SQL as much as you can. That means
> everything except user-specified queries that are completely ad hoc by
> design. Most user inquiries are not ad hoc. They want the same query,
> over and over, with different values in the search (WHERE)
> criteria. "Prepare once, execute often."
> Well, you did ask for opinions... :-)
> ./heLen

Thank you very much for your advice. I really apreciate it.