| Subject | Re: [firebird-support] Design advice |
|---|---|
| Author | Helen Borrie |
| Post date | 2005-08-20T13:14:04Z |
At 11:42 AM 20/08/2005 +0000, you wrote:
nothing except encapsulate regular SELECT queries in permafrost, the
better. Keep the ones that provide workarounds for retrieval logic that
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 crazy
with their sheer volume. Use executable procedures to perform sequences of
operations that can be kicked off with one set of parameters (replaceable
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 desktop
databases before and try to make Firebird work like Access or, worse, a
spreadsheet application. They don't understand parameterised statements,
they don't understand transaction isolation or atomic tasking or delegation
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
>I'm developing a commercial application that uses Firebird. At theThis is extremely unnecessary.
>moment I have all my queries coded in stored procedures.
>The process to deploy updates to my app I think is going to beExactly.
>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 sqlMy opinion is that the sooner you replace any selectable SPs that do
>directly from my code instead.
>
>I would like to know your opinion about this. What drawbacks can I
>expected?
nothing except encapsulate regular SELECT queries in permafrost, the
better. Keep the ones that provide workarounds for retrieval logic that
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 crazy
with their sheer volume. Use executable procedures to perform sequences of
operations that can be kicked off with one set of parameters (replaceable
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 desktop
databases before and try to make Firebird work like Access or, worse, a
spreadsheet application. They don't understand parameterised statements,
they don't understand transaction isolation or atomic tasking or delegation
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