Board index » delphi » Any thoughts on procedurised DB access

Any thoughts on procedurised DB access

Hi,
We are currently having a nice debate here about ways and means of
getting at the data within the database (using Oracle 8).  One camp is
arguing that we should not be using SQL but calling procedures to add,
amend and delete records, the other camp (Ok me :) ) is wondering is
this is worthwhile as this is the job that SQL was designed to do in
the first place.
I have some concerns over the disjointedness of using procedures to
amend the tables - I see it being far too much work even to implement
optimistic locking.
I favour using Direct Oracle Access Components with either SQL stored
in the server and cached in the client, or PL/SQL procedures that
return cursors that DOA can manipulate.  I see the DOA components
doing all of the hard work then and I don't have to worry about it.
Does anyone have any thoughts, experience or tales to tell on the pros
and cons of doing things one way or the other ?

Thanks

 

Re:Any thoughts on procedurised DB access


Quote
Andrew Porter wrote in message <38c3cc49.21174...@forums.inprise.com>...
>We are currently having a nice debate here about ways and means of
>getting at the data within the database (using Oracle 8).  One camp is
>arguing that we should not be using SQL but calling procedures to add,
>amend and delete records, the other camp (Ok me :) ) is wondering is
>this is worthwhile as this is the job that SQL was designed to do in
>the first place.

There are two main advantages of using stored procedures:
1) Except for *very* simple queries, performance is better even if the
procedure executes the identical SQL statement, but for more complex queries
performance can be made *much* better since a stored procedure can do any
number of SQL statements. This allows you to break up complex queries in to
multiple simpler ones, place conditions on some of these queries that would
otherwise be left or right joins, and aggregate what would otherwsie be
separate queries executed from the application. All this adds up to *huge*
performance improvements.
2) Reuse. Multiple applications, or one monolithic app broken into smaller
pieces, all have access to these common server supplied procs. Depending on
the server (I don't personally know Oracle) there may be a security
advantage as well - access only through the procedures means no-one can mess
with the data through ad-hoc queries.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
You have a Right to Free Speech, but not the right to make me listen, nor to
use my property as a soapbox.

Re:Any thoughts on procedurised DB access


On Mon, 6 Mar 2000 19:37:12 -0500, "Wayne Niddery (TeamB)"

Quote
<winwri...@chaffhome.com> wrote:
>There are two main advantages of using stored procedures:

Thanks for the reply.
Whilst I accept that there are general benefits of using stored
procedures, I see life being far easier for normal database updating
using either a procedure that returns an updatable cursor or plain old
SQL (I would like all the SQL to be centralised and not in the client
however).  Most tables require a screen to update them that is (90% of
the time) simply a view of the table, with little bells or whistles.
For this scenario I see procedurising insert, updates and deletes
being sledgehammers to nuts.

Am I way of track with the way things are moving today ?

Re:Any thoughts on procedurised DB access


Andrew,

You've raised some points that are the subject of almost theological
debate among database designers.  As a background to this discussion,
everything that Wayne says about stored procedures is correct.  Now, to
proceed.

One school says perform  all modifications to the database through
parameterized stored procedures, simply passing values to the server.
Show all data to the user front end via views, thus preventing the user
from having direct access to the data.  This approach has the advantages
that Wayne points out.  It also has one huge (in my mind) disadvantage:
you cannot use data-aware controls.  You are going to have to build all
the logic to get the data out of controls, and ship it to the server.

The other approach is to use data-aware controls with parameterized
queries, and to prepare the queries.  In Oracle, every query that is
sent to the server is retained in its parsed state in the SGA as long as
memory is available.  This means that when the query is reexecuted with
changed parameters, it does not have to be reparsed.  This is not as
fast as a stored procedure -- for one thing, the entire text of the
query is being sent across the network -- but it is fast.  The question
is, is it fast enough?  Oracle is fast;  DOA is fast;  it's fast enough
for me.  If you have something that is quite complicated then do build a
stored procedure for it, but make use of the convenience of data-aware
controls.

You mention centralizing the SQL on the server.  I'm not sure what you
mean by that.  All SQL is executed on the server, but SQL that resides
on the server is in a stored procedure, package, function or trigger.
Or perhaps I've misunderstood you.

In short, you can take either approach.  Delphi is really built on the
premise that data-aware controls will be used, but it doesn't require
their use.  I use them.  In most cases, the speed penalty is small and
acceptable IMO.

John

Re:Any thoughts on procedurised DB access


On Tue, 07 Mar 2000 09:19:22 -0800, John Pierce

Quote
<jpie...@healthplanning.com> wrote:

>You've raised some points that are the subject of almost theological
>debate among database designers.  As a background to this discussion,
>everything that Wayne says about stored procedures is correct.  Now, to
>proceed.

John , thanks for your reply it was very valuable and somewhere on the
lines that I have been thinking.

However, I have been "playing" around with DOA's ability to use a
cursor create from a procedure and it works pretty well.  This allows
you to still use data aware controls etc..

Look at the pkgapply demo that comes with DOA for a simple example.
Although it is still more work than pure SQL I am quite happy to
concede this argument here (expecially with the DOA dataset's
updatingtable property so that automatic constraint checking can still
be use - a most valuable feature).

I have taken the pkgapply demo a little further to do a refresh
immediately upon editing and also to do optimistic locking (both
simple when I read the help pages).  As I said all of this does
require a lot more code than simple SQL based queries that are
updatable, I just need to weigh the advantages of what would be
effectively an API for our application over speed of development.

WRT storing the SQL on the server, I was thinking of deriving a
component from the DOA Dataset that had a new property of an SQL
reference, and it used this to extract the SQL from the Database.  I
would then do some simple caching locally to improve speed etc..

Half the problem with all of this is that there are so many ways to do
things that picking the right one is the biggest problem, not actually
doing it :)

Thanks

Re:Any thoughts on procedurised DB access


Andrew,
Quote

However, I have been "playing" around with DOA's ability to use a
cursor create from a procedure and it works pretty well.  This allows
you to still use data aware controls etc..
Quote

Yes, ref cursors work very well with data-aware controls, and, of
course, you can pass them parameters.  I was simply making an argument
that maintainability of an application is more important than the last
nanosecond of speed, and I think bypassing the data-aware controls
decreases maintainability.  You seem to me to be thinking along the
right lines.  There are lots of different ways to do this stuff,  and
there are definitely some wrong ways, but certainly many right ways.

I am just now in the process of converting a large application from the
BDE to DOA, so I don't yet have my full bag of tricks, but I really like
DOA.

PL/SQL is a great language and a natural for Pascal programmers.  If
you're new to it (or even if you aren't) I strongly recommend three
books by Steven Feuerstein (O'Reilly Press):  _PL/SQL Programming_,
_Advanced PL/SQL Programming_
and _Oracle built-in Packages_.

John

Re:Any thoughts on procedurised DB access


Quote
John Pierce wrote in message <38C53A1A.1C77E...@healthplanning.com>...

> It also has one huge (in my mind) disadvantage:
>you cannot use data-aware controls.  You are going to have to build all
>the logic to get the data out of controls, and ship it to the server.

Just for the record, I have not abandoned data-aware controls. I do advocate
stored procs (even more lately from experience) but not at the cost of
throwing away such a good feature.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
You have a Right to Free Speech, but not the right to make me listen, nor to
use my property as a soapbox.

Re:Any thoughts on procedurised DB access


On Tue, 07 Mar 2000 15:47:59 -0800, John Pierce

Quote
<jpie...@healthplanning.com> wrote:
>PL/SQL is a great language and a natural for Pascal programmers.  If
>you're new to it (or even if you aren't) I strongly recommend three
>books by Steven Feuerstein (O'Reilly Press):  _PL/SQL Programming_,
>_Advanced PL/SQL Programming_
>and _Oracle built-in Packages_.

I am fairly new to Oracle but despite there being a great mystique
about it am finding it fairly straight forward (at least at the level
I am at).  I already have "Oracle PL/SQL Programming" (haven't really
used it much) and have just this second ordered the Nutshell Advanced
book from Amazon. (I am an O'Reilly Nut myself - can't stop buying
them)

One of the things I would dearly love is a full blown application that
uses Delphi and DOA and Oracle to play with and show to my bosses.
All the work I am doing at the moment is investigative to try and
convince people that we really should be using Delphi here. We have
already proposed and backed using Java but people are beginning to
have second thoughts about this so I am now showing off Delphi and its
biggest pro - speed of development (of course along with look/feel and
speed of exe).

Re:Any thoughts on procedurised DB access


Quote
Andrew Porter wrote:
> On Tue, 07 Mar 2000 15:47:59 -0800, John Pierce

> I am fairly new to Oracle but despite there being a great mystique
> about it am finding it fairly straight forward (at least at the level
> I am at).
> One of the things I would dearly love is a full blown application that
> uses Delphi and DOA and Oracle to play with and show to my bosses.
> All the work I am doing at the moment is investigative to try and
> convince people that we really should be using Delphi here. We have
> already proposed and backed using Java but people are beginning to
> have second thoughts about this so I am now showing off Delphi and its
> biggest pro - speed of development (of course along with look/feel and
> speed of exe).

A lot of the complexity of Oracle lies in assigning and sizing table
spaces and in  tuning.  Most of the Oracle books are aimed at Unix, but
I
think it's worth it to buy _Oracle 8 & Windows NT Black Book_ from
Coriolis Press.  Lots of good stuff about tuning.  But I agree with you
--
Oracle is a great deal more straightforward than its reputation.

Why not prototype a small application yourself?

John

Re:Any thoughts on procedurised DB access


In article <38C53A1A.1C77E...@healthplanning.com>, John Pierce

Quote
<jpie...@healthplanning.com> wrote:
>Andrew,

>You've raised some points that are the subject of almost
theological
>debate among database designers.  As a background to this
discussion,
>everything that Wayne says about stored procedures is correct.
Now, to
>proceed.

>One school says perform  all modifications to the database
through
>parameterized stored procedures, simply passing values to the
server.
>Show all data to the user front end via views, thus preventing
the user
>from having direct access to the data.  This approach has the
advantages
>that Wayne points out.  It also has one huge (in my mind)
disadvantage:
>you cannot use data-aware controls.  You are going to have to
build all
>the logic to get the data out of controls, and ship it to the
server.

>The other approach is to use data-aware controls with
parameterized
>queries, and to prepare the queries.  In Oracle, every query
that is
>sent to the server is retained in its parsed state in the SGA
as long as
>memory is available.  This means that when the query is
reexecuted with
>changed parameters, it does not have to be reparsed.  This is
not as
>fast as a stored procedure -- for one thing, the entire text of
the
>query is being sent across the network -- but it is fast.  The
question
>is, is it fast enough?  Oracle is fast;  DOA is fast;  it's
fast enough
>for me.  If you have something that is quite complicated then
do build a
>stored procedure for it, but make use of the convenience of
data-aware
>controls.

>You mention centralizing the SQL on the server.  I'm not sure
what you
>mean by that.  All SQL is executed on the server, but SQL that
resides
>on the server is in a stored procedure, package, function or
trigger.
>Or perhaps I've misunderstood you.

>In short, you can take either approach.  Delphi is really built
on the
>premise that data-aware controls will be used, but it doesn't
require
>their use.  I use them.  In most cases, the speed penalty is
small and
>acceptable IMO.

Just a quick question.

Can you not use a TUpdateSQL component to do the
Update/Insert/Delete stored proc calls ?

Cheers,
Ciaran

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

Other Threads