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

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/&gt;

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

Bernhard Weisshuhn wrote:

Don Y wrote:

[snip]

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.

Ah, OK. More elegant. But, it still moves responsibility for this
to the application layer, not the database, itself. I can't see
any way of avoiding this :-(

OTOH, an API with like insert_data(...., bool confirm) would
remind the application developers that the intended interface
is:

switch (insert_data(..., FALSE)) {
case INVALID:
/* something wonky in the data, abort */
break;
case QUESTIONABLE:
/* possible typographical error, require confirmation */
if (confirmed)
insert_data(..,TRUE);
break;
case LOOKS_GOOD:
insert_data(..., TRUE);
}

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

<frown> Sorry, i've been writing specifications for the past
few days and use the "emphasis" SGML tag quite a bit :-/
(the idea of posting in HTML is just anathema...)

--don

On 4/6/06, Don Y <pgsql@dakotacom.net> wrote:

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?

Well, your application could:
BEGIN TRANSACTION;
Then it would
INSERT INTO... or call a stored procedure. The triggers/stored procedure
would
do all what's needed to perform such action, but when it notices something
suspicious it would RAISE (see PLpgSQL) a notice describing the problem(s).

If your user application notices such messages, it issues a message
"WARNING:
the message, do you want to continue?" and if user presses yes, you
do COMMIT. Otherwise you do ROLLBACK.

The thing is to use
http://www.postgresql.org/docs/8.1/static/plpgsql-errors-and-messages.html

Regards,
Dawid

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

Ah, OK. More elegant. But, it still moves responsibility for this
to the application layer, not the database, itself. I can't see
any way of avoiding this :-(

Sure, I had a similar problem where newer data is required to specify
certain fields, but a legacy application can't provide that data. So there's
a flag, that defaults to false, that when set to true means "I know, I know,
just shut up and insert the data anyway."

The "flag" could be an argument to a stored procedure. In my case it's an
actual column in the table, because I wanted to use check constraints and I
wanted a record of the fact that the entry is "incomplete" because of its
source.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

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