Thread: stored proc vs sql query string

On 4/6/06 12:12 AM, "surabhi.ahuja" <surabhi.ahuja@iiitb.ac.in> wrote:

> i have heard somewhere that writing a stored procedure, is much better than
> firing a sql query(such as select * from table_name) onto the database.
> is it true and if yes how?

This isn't going to be true most of the time, I think.  Write SQL where you
can, and where you can't (because you can't express something in SQL), write
a procedure.  There are places where using a stored procedure can be more
efficient, but I think starting with SQL, benchmarking and testing, and then
determining what queries need special attention is the best way to go at the
beginning.

> also i want to know that is the performnance in java slower as compared to
> cpp, given that the same things is being done.

Java and cpp performance are not really related to postgresql performance.
You will probably need to ask that on another list.  There are many other
reasons to choose one language over another besides speed (in fact, I think
speed is probably not the first thing to think about when choosing a
language).

Sean

> > i have heard somewhere that writing a stored procedure, is much better than
> > firing a sql query(such as select * from table_name) onto the database.
> > is it true and if yes how?

stored procedures (functions on postgresql) eliminate a lot of
overhead.  they also provide a lot of covenience of transactions
manually opening and closing them.  There are also security and design
benefits.

> This isn't going to be true most of the time, I think.  Write SQL where you
> can, and where you can't (because you can't express something in SQL), write

stored procedures allow the application to do complex things in the
database without having complex sql inside.  it also allows multiple
application technologies to access the database without reimplementing
the query in 10 differnet places.  what happens when that query
changes?

> a procedure.  There are places where using a stored procedure can be more
> efficient, but I think starting with SQL, benchmarking and testing, and then
> determining what queries need special attention is the best way to go at the
> beginning.
>
> > also i want to know that is the performnance in java slower as compared to
> > cpp, given that the same things is being done.
>
> Java and cpp performance are not really related to postgresql performance.
> You will probably need to ask that on another list.  There are many other
> reasons to choose one language over another besides speed (in fact, I think
> speed is probably not the first thing to think about when choosing a
> language).

language choice is important such that it may expose none, some, or
all, of the internal libpq api (or implement it's own version of the
client side protocol).  cpp can often be faster because you can make
very thin wrappers over the libpq calls (STL is ideal for this).

stored procedures, particularly pl/sql funtions, have the enormous
advantage in that queries are first class objects inside the
procedural code....so you can

x :=  1 + 3;
select * from t where id = x;

without nasty string concatination or learning a whole API just to
read and write data to the database.  Another advantage is that the
can also executed by the query engine so you can do:

select outstanding_account_balance(account) from account where...

these two advantages alone mean huge reductions in code along with
corresponding savings in development and real dollars.  It has been
more or less proven that functional, declaritive style coding has less
errors and is more reliable than mixed sql/procedural applciation code
given developers with equal skill.  thus, I would argue the opposite,
use procedures everywhere, keep application code to an absolute
minumum, because it is expensive to write and changes frequently.

The main reason not to code your logic in the database is to keep your
code portable across multiple databases.   Around year 2000 I realized
pg is the only database I would ever want to use again in my
professional career.

merlin

Merlin Moncure wrote:

>  It has been
>more or less proven that functional, declaritive style coding has less
>errors and is more reliable than mixed sql/procedural applciation code
>given developers with equal skill.
>
I did not know there were empirical studies on this, I would love to be
able to read them and cite them.  Can you point me to any in particular?

Hi,

I wasn't prepared to ask this question, yet :<  but
all the talk of stored procedures, etc. suggests
this might be a good time to venture forth...

Humor me:  assume I have done the analysis and *know*
this to be correct for my situation  :>

I want to embed a good deal of the invariant aspects
of my application *domain* in the databases that
service it -- instead of in the applications riding
*above* them.  So, I let the database, itself, do
sanity checking of data on input -- if the database
rejects the INSERT, the application *knows* there is
something wrong with the data (instead of building
libraries to check each datum in each application
and *hoping* that the checks are implemented
consistently from one application to the next, etc.)

Anyway, the problem I have is how to handle cases
where the "database" needs user confirmation of an
action (!).  I.e. it has verified that the data
coming in is legitimate (adheres to all of the rules)
and *can* be stored in the appropriate tables -- BUT,
notices "something" that causes it to wonder if the
user REALLY wants to INSERT this data.  The database
would like to alert the user to what it has noticed
and get confirmation from the user (of course, I
mean my *application* wants to do this -- but, based
on observations made *by* the database, itself).

By way of example, the *toy* application I am playing with
to explore my implementation options is a "book" database;
it tracks titles, books, authors, publishers, etc.
The sort of thing a library could use to manage its
collection.

Assume the user tries to INSERT an entry for a "book".
Legitimately, this can be:
- a new title that the database has never seen before
- a new title by an author with other titles in the database
- an existing title thus another copy of that title

However, it can also just *appear* to be a legitimate new
title!

For example, the title may match an existing entry -- but
the author may be different (e.g., misspelled, or some
"other" author listed on a book having multiple authors, etc.).
Ideally, I would like the database to suspend the INSERT,
ask for confirmation (and "why") and then, either commit
the INSERT or abort it (based on the user's response).

Nearest I can imagine, there's only one ways I can do this:
issue a query that looks for these types of problems and
based on the result, let the *application* prompt the
user for confirmation.  Then, *if* confirmed, do the real
INSERT.

Is there a more elegant way?

Thanks,
--don

Don Y wrote:
> Hi,
>
> I wasn't prepared to ask this question, yet :<  but
> all the talk of stored procedures, etc. suggests
> this might be a good time to venture forth...
>
> Humor me:  assume I have done the analysis and *know*
> this to be correct for my situation  :>
>
> I want to embed a good deal of the invariant aspects
> of my application *domain* in the databases that
> service it -- instead of in the applications riding
> *above* them.  So, I let the database, itself, do
> sanity checking of data on input -- if the database
> rejects the INSERT, the application *knows* there is
> something wrong with the data (instead of building
> libraries to check each datum in each application
> and *hoping* that the checks are implemented
> consistently from one application to the next, etc.)
>
> Anyway, the problem I have is how to handle cases
> where the "database" needs user confirmation of an
> action (!).  I.e. it has verified that the data
> coming in is legitimate (adheres to all of the rules)
> and *can* be stored in the appropriate tables -- BUT,
> notices "something" that causes it to wonder if the
> user REALLY wants to INSERT this data.  The database
> would like to alert the user to what it has noticed
> and get confirmation from the user (of course, I
> mean my *application* wants to do this -- but, based
> on observations made *by* the database, itself).
>
> By way of example, the *toy* application I am playing with
> to explore my implementation options is a "book" database;
> it tracks titles, books, authors, publishers, etc.
> The sort of thing a library could use to manage its
> collection.
>
> Assume the user tries to INSERT an entry for a "book".
> Legitimately, this can be:
> - a new title that the database has never seen before
> - a new title by an author with other titles in the database
> - an existing title thus another copy of that title
>
> However, it can also just *appear* to be a legitimate new
> title!
>
> For example, the title may match an existing entry -- but
> the author may be different (e.g., misspelled, or some
> "other" author listed on a book having multiple authors, etc.).
> Ideally, I would like the database to suspend the INSERT,
> ask for confirmation (and "why") and then, either commit
> the INSERT or abort it (based on the user's response).
>
> Nearest I can imagine, there's only one ways I can do this:
> issue a query that looks for these types of problems and
> based on the result, let the *application* prompt the
> user for confirmation.  Then, *if* confirmed, do the real
> INSERT.

You could *insert* the data and then *rollback* the transaction. Then
you would *know* the data is *valid*.
Only if the user *confirms* the action, then you do it *again* and
actually *commit* the transaction.

P.S. these* *stars* are *unnerving* ;-)

bkw

surabhi.ahuja@iiitb.ac.in ("surabhi.ahuja") writes:
> i have heard somewhere that writing a stored procedure, is much
> better than firing a sql query(such as select * from
> table_name) onto the database.
>
> is it true and if yes how?

It can be way more efficient.

Consider two alternative ways of handling some complex processing:

  1.  Pull all the data via SELECT * FROM TABLE_NAME, and process that
      row by row, on the client.

  2.  Pull the data in as a SELECT inside a stored procedure, where
      processing takes place inside the stored procedure.

In the first case, ALL the data has to be drawn into memory on the
database server, then marshalled, then passed over to the client,
possibly across a network connection.  Processing then takes place on
the client, and updates may have to be passed back, one by one, across
the network connection, to the server.

In the second case, the same data is drawn into memory on the server.
It doesn't have to be transformed to be communicated to the client,
and there will be no substantial processing that takes place on the
client.

There's *substantial* savings in processing to be had by using stored
procedures.

> also i want to know that is the performnance in java slower as
> compared to cpp, given that the same things is being done.

There is no a priori reason to expect Java code accessing a database
to be either slower or faster than C++ code doing something
equivalent.  Commonly, database I/O is the performance bottleneck,
which would point to language choice being irrelevant.

You have given no reason to distinguish between any cases...
--
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/sap.html
Rules of the Evil Overlord #81. "If I am fighting with the hero atop a
moving platform, have disarmed him, and am about to finish him off and
he glances behind  me and drops flat, I too will  drop flat instead of
quizzically turning around to find out what he saw."
<http://www.eviloverlord.com/>

Don Y wrote:
> Hi,
>
> I wasn't prepared to ask this question, yet :<  but
> all the talk of stored procedures, etc. suggests
> this might be a good time to venture forth...
>
> Humor me:  assume I have done the analysis and *know*
> this to be correct for my situation  :>
>
> I want to embed a good deal of the invariant aspects
> of my application *domain* in the databases that
> service it -- instead of in the applications riding
> *above* them.
Keeping in mind that you've done plenty of analysis, I'd raise the
standard cautionary note that you have to be pretty certain that you're
right about what's invariant if you're going to couple your application
logic very tightly to your data model.

> Anyway, the problem I have is how to handle cases
> where the "database" needs user confirmation of an
> action (!).  I.e. it has verified that the data
> coming in is legitimate (adheres to all of the rules)
> and *can* be stored in the appropriate tables -- BUT,
> notices "something" that causes it to wonder if the
> user REALLY wants to INSERT this data.  The database
> would like to alert the user to what it has noticed
> and get confirmation from the user (of course, I
> mean my *application* wants to do this -- but, based
> on observations made *by* the database, itself).
I've been dealing this myself, as it happens, in the context of
importing and validating data (an ETL kind of thing).  My answer is to
use temporary tables as a way of staging the data.  One of the
advantages of that is that you can wrap up the DDL for the temporary
table inside your code (whether in or out of the database) and thus
insulate that part of the process from other changes in the destination
tables.  Then you can fire off whatever rules you like about whether to
alert the user of certain conditions with the data without worrying
about commits.  Of course the transaction approach can probably handle
that, too.

Cheers,

Eric

On Apr 6, 2006, at 6:39 AM, Sean Davis wrote:
> On 4/6/06 12:12 AM, "surabhi.ahuja" <surabhi.ahuja@iiitb.ac.in> wrote:
>
>> i have heard somewhere that writing a stored procedure, is much
>> better than
>> firing a sql query(such as select * from table_name) onto the
>> database.
>> is it true and if yes how?
>
> This isn't going to be true most of the time, I think.  Write SQL
> where you
> can, and where you can't (because you can't express something in
> SQL), write
> a procedure.  There are places where using a stored procedure can
> be more
> efficient, but I think starting with SQL, benchmarking and testing,
> and then
> determining what queries need special attention is the best way to
> go at the
> beginning.

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.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On 4/7/06, Jim Nasby <jnasby@pervasive.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@pumptheory.com
 w: mark.aufflick.com
 p: +61 438 700 647
 f: +61 2 9436 4737