|Subject||Re: Design advice|
--- In firstname.lastname@example.org, Helen Borrie <helebor@t...>
> At 11:42 AM 20/08/2005 +0000, you wrote:that
> >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.
> >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
> 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.They are
> The same goes for executable SPs that perform plain-vanilla DML.
> a waste of resources and they add dependencies that will drive youcrazy
> with their sheer volume. Use executable procedures to performsequences of
> operations that can be kicked off with one set of parameters(replaceable
> or not) from the client.desktop
> 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, astatements,
> spreadsheet application. They don't understand parameterised
> they don't understand transaction isolation or atomic tasking ordelegation
> of data-intensive work to the server. And, no kidding, sometimesthey don't
> even understand SQL and have been observed to argue that they don'twant to
> be put into the position of having to learn it.Thank you very much for your advice. I really apreciate 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... :-)