Re: stored proc vs sql query string

On 4/7/06, Jim Nasby <jnasby(at)pervasive(dot)com> wrote:
> You're forgetting that (at least in plpgsql), "raw" queries get
> compiled into prepared statements. Prepared statements are faster to
> execute than queries that have to be manually parsed every time. Of
> course you can pass in prepared statements from the client side as
> well, but if you stick with using stored procedures as an API to the
> database you don't have to worry about forgetting to do that. And as
> others have mentioned there's non-performance-related benefits to
> using stored procs as well.

And prepared queries are only persistant to a connection so your code
needs to remember which queries it has prepared on that connection.

Prepared queries are very useful however. When you have an application
that does dynamically generate sql, it is likely that it is often
generating identical sql. If you can identify that and use a cache of
prepared sql you will gain big efficiencies.

On the main issue of this thread, I don't personally subscribe to the
"do everything as a stored proc because it's faster" way of thinking.
SQL should not be randomly intermixed with procedural code for sure,
but you should be able to isolate it into a set of classes (if you're
OO) rather than implement great wads of your application in stored
procs.

Especially when you have multiple apps and/or versions accessing the
same db you want to avoid making yourself a big versioning problem and
a huge catalog of stored procs (Especially since we don't have stored
proc namespaces or versioning in postgres).

The poster who suggested that mixed sql/procedural coding requires a
higher calibre of developer might be onto something - bad perl or java
code tends to have worse sql embedded in it. Again I would point to my
previous suggestion of containing db access in a small set of
classes/methods/whatever - then you can assign your most sql savvy
developer to maintaining that code.

Just my 2c.

Mark Aufflick.
--
Mark Aufflick
e: mark(at)pumptheory(dot)com
w: mark.aufflick.com
p: +61 438 700 647
f: +61 2 9436 4737