DB-Library Vs Storedproc. [ In search of Balance?? ] 
Author Message
 DB-Library Vs Storedproc. [ In search of Balance?? ]

With the availability of Cursors in System 10, Most of the
things could be accomplished using stored procedures.

What are the pros and cons of maximizing the use of stored
procedures as opposed to using e.g. C/C++ with DB-lib??

For example I have following questions in my mind:

Is it a good practice to eliminate embedded sql etirely
from the application and use stored proc. even for simple
select statements?

Is'nt it OK to move database-intensive logic to the server side
in order to boost performance and to have less of C/C++ code?

Which of the two approaches support better maintenace?

Your comments are greatly appreciated..

Amir Baig
Consultant
713-757-5054



Fri, 12 Dec 1997 03:00:00 GMT
 DB-Library Vs Storedproc. [ In search of Balance?? ]

: With the availability of Cursors in System 10, Most of the
: things could be accomplished using stored procedures.

: What are the pros and cons of maximizing the use of stored
: procedures as opposed to using e.g. C/C++ with DB-lib??

: For example I have following questions in my mind:

: Is it a good practice to eliminate embedded sql etirely
: from the application and use stored proc. even for simple
: select statements?

: Is'nt it OK to move database-intensive logic to the server side
: in order to boost performance and to have less of C/C++ code?

: Which of the two approaches support better maintenace?

I will go for a SP solution anytime and anywhere.
I would suggest to well divide your work and allocate
resources doing only Server work and somebody doing front-end work.

It works like a charm.

The FE team should make all there SP-construction requests to the
Server team. This way you will have a consistent system
infact, even the bugs would be consistent :=))

cheers,
shahid



Sat, 13 Dec 1997 03:00:00 GMT
 DB-Library Vs Storedproc. [ In search of Balance?? ]
: With the availability of Cursors in System 10, Most of the
: things could be accomplished using stored procedures.

: What are the pros and cons of maximizing the use of stored
: procedures as opposed to using e.g. C/C++ with DB-lib??

: For example I have following questions in my mind:

: Is it a good practice to eliminate embedded sql etirely
: from the application and use stored proc. even for simple
: select statements?

: Is'nt it OK to move database-intensive logic to the server side
: in order to boost performance and to have less of C/C++ code?

: Which of the two approaches support better maintenace?

: Your comments are greatly appreciated..

: Amir Baig
: Consultant
: 713-757-5054

A lot of the answers depend on a few things.  1)  Who will maintain the
code?  2)  How powerful is your server?  3)  What type of underlying
hardware everything is running on? etc...

For the most part, in System 10, try to do all in database data
manipulation using stored procedures.  Once you have to start comparing
data with text files, loading files to or from client workstations, or
have to start doing strange things while loading text files, then you
should consider using DB-LIB or Open Client calls.

Stored Procedures offer security and efficency.  The server can execute
a stored procedure faster than having the same code send in via DB-LIB.  
Also, you are not moving data across the network to a client machine to
compare against.  Security because you can restrict users to specific
stored procedures, even when they are using the same machine.

My opinion is to use stored procedures for in-database data manipulation
and DB-LIB for everything else.

Thanks for the opportunity to assist you.
Daniel Vans{*filter*}burg, Consultant/Owner
Vans{*filter*}burg Computer Consulting



Sat, 13 Dec 1997 03:00:00 GMT
 DB-Library Vs Storedproc. [ In search of Balance?? ]

Quote:

>What are the pros and cons of maximizing the use of stored
>procedures as opposed to using e.g. C/C++ with DB-lib??

In general stored procedures are the way to go; they execute
significantly faster, they provide a finer level of security,
they reduce the traffic between client and server, and they
provide the ability to change the behaviour of the query without
re-compiling the client.

However, there are a couple of things to keep in mind when
dealing with stored procedures.  Things such as:

   o  Their query plan is static; that is, if the distribution
      of your data varies greatly with time, the only way that
      the stored procedures will recognize this change is to
      force a recompile.

   o  Sybase does not yet support a very dynamic SQL, so
      implementing certain queries will not be possible.

   o  Sybase's stored procedures are non-reentrant...if you
      are going to have a large number of clients running the
      same procedure at the same time you will consume quite
      a bit more memory than you would expect.

Quote:
>For example I have following questions in my mind:

>Is it a good practice to eliminate embedded sql etirely
>from the application and use stored proc. even for simple
>select statements?

In practice, I typically don't implement simple queries in stored
procedures, just because the effort of setting up the procedure call,
and maintaining a lot of small procedures isn't always worth it.
But, your mileage may vary.

Quote:
>Is'nt it OK to move database-intensive logic to the server side
>in order to boost performance and to have less of C/C++ code?

This is supposed to be one of the purposes of a server.  But, you
should always be aware of performance trade-offs.  As a general rule,
if it has to do with data integrity and constraints you should put
it in the server.

Quote:
>Which of the two approaches support better maintenace?

Like I said, if you go overboard with the number of stored procedures
you have laying around it can be a hastle to maintain.

Good Luck.
-scott



Sun, 14 Dec 1997 03:00:00 GMT
 DB-Library Vs Storedproc. [ In search of Balance?? ]

Quote:

>With the availability of Cursors in System 10, Most of the
>things could be accomplished using stored procedures.

>What are the pros and cons of maximizing the use of stored
>procedures as opposed to using e.g. C/C++ with DB-lib??

>For example I have following questions in my mind:
>Is'nt it OK to move database-intensive logic to the server side
>in order to boost performance and to have less of C/C++ code?

amir,

if you have 100 users, you most likely want the server doing
as little as possible (ie offload as much as poss to the clients).

systems that have the client doing more work are natually more scaleable

one thing to watch for is network traffic. dont have the server do so little
that heaps of data are being sent to the client.

i am often in the position of being asked by a devloper for another index on
a table. that table may already have a bunch of similar indexes. i need to know
if all of those indexes are being used (and by what) so that i can decide
if an index can be removed or merged etc. if stored procs are used i can atleast
get the plans via an automated process to determine index usage. whereas if the
sql is buried in the app it can be very hard to find in order to do a show plan.

of course the plan may be determined by the type of data passed to the stored proc
which can complicate matters. but u have a much better chance with stored procs.

also stored procs better shield the app from changes to be structure. with
dynamic sql all u have are views which can be very limiting. sp's also
optimize (more or less) only once.

sp i like sp's best.

good luck

steve.

Quote:
>Which of the two approaches support better maintenace?

>Your comments are greatly appreciated..

>Amir Baig
>Consultant
>713-757-5054



Sun, 14 Dec 1997 03:00:00 GMT
 DB-Library Vs Storedproc. [ In search of Balance?? ]

Quote:

> What do you think of this? It sounds logical.


> >With the availability of Cursors in System 10, Most of the
> >things could be accomplished using stored procedures.

> >What are the pros and cons of maximizing the use of stored
> >procedures as opposed to using e.g. C/C++ with DB-lib??

> >For example I have following questions in my mind:

> >Is'nt it OK to move database-intensive logic to the server side
> >in order to boost performance and to have less of C/C++ code?

> amir,

> if you have 100 users, you most likely want the server doing
> as little as possible (ie offload as much as poss to the clients).

> systems that have the client doing more work are natually more scaleable

[Danger, incoming soapbox oratory]

This depends entirely on the application.  If the operations performed
involve a bunch of accesses to different db tables, then putting
everything
in a stored procedure can be orders of magnitude faster because you don't
have to shuffle all that data back and forth across the communications
link.

Stored procedures also make sense in a high volume simple transaction
processing environment (Online Transaction Processing, OLTP).  This is
because the stored procedure has already been parsed and had a query
plan generated.  If you running SQL multiple times a second, you don't
want to waste time submiting "new" SQL each time.

On the other hand, if there's little data access and complicated logic,
C/C++ code makes a lot more sense.  Sybase Transact-SQL certainly isn't
the most powerful programming language in the world.

A centralized server is by definition a bottleneck.  Distributed
processing
is a lot like an electric car.  It's great if you can just figure out what
to do with that extension cord problem (data movement/coordination/control

in the case of distributed processing).

In the case of distributed database processing, I find it's usually not
useable for high performance applications.  The two issues of data
movement and data consistency are very difficult problems.  Keep in
mind, the essence of relational database design is that a high I/O join
operation is fast.  If you distribute the data, you stand the
possibility of contradicting this basic assumption.

The subject line says it all.  The search for the proper balance
for your application ("you must feel the way Luke").

Ben Slade
Silver Spring, MD, USA
"What's the meaning of the word 'definition'?" (Stephen Wright)



Mon, 15 Dec 1997 03:00:00 GMT
 DB-Library Vs Storedproc. [ In search of Balance?? ]

Quote:

>i am often in the position of being asked by a devloper for another index on
>a table. that table may already have a bunch of similar indexes. i need to know
>if all of those indexes are being used (and by what) so that i can decide
>if an index can be removed or merged etc. if stored procs are used i can atleast
>get the plans via an automated process to determine index usage. whereas if the
>sql is buried in the app it can be very hard to find in order to do a show plan.

This is probably the most important point if you are part of a large
project with lots of people writing SQL; if you have a performance
problem it can be very difficult to find the offensive embedded SQL in
order to see why it gives such a performance hit.

Of course the stored procs should do the minimum amout of work and
return a minimal amount of data to a client for perfromance and
scalability.

FWIW we got a 5 times speed up by fixing a PowerBuilder generated
piece of SQL to retrieve just a single row in place of half the table
(reduced network traffic) and another 4 times speedup by optimising
the fixed SQL (Sybase execution plan improvements). The auto-generated
SQL was rubbish. This SQL was easy to find because it was the only SQL
behind a grossly slow screen, but if we'de been dealing with, say, a
COBOL subsystem, with lots of SQL in it then merely finding the
problem could have been difficult.

----------------------------------------------------------------------
Martin Gregorie             |Logica UK Ltd

+44 (0171) 637 9111         |of the author and not of Logica
----------------------------------------------------------------------



Mon, 15 Dec 1997 03:00:00 GMT
 DB-Library Vs Storedproc. [ In search of Balance?? ]

Quote:

>With the availability of Cursors in System 10, Most of the
>things could be accomplished using stored procedures.

>What are the pros and cons of maximizing the use of stored
>procedures as opposed to using e.g. C/C++ with DB-lib??

>For example I have following questions in my mind:

>Is it a good practice to eliminate embedded sql etirely
>from the application and use stored proc. even for simple
>select statements?

>Is'nt it OK to move database-intensive logic to the server side
>in order to boost performance and to have less of C/C++ code?

>Which of the two approaches support better maintenace?

>Your comments are greatly appreciated..

>Amir Baig
>Consultant
>713-757-5054

Generally it is better to use stored procs then embedded SQL.  For one,
stored procedures have pre-compiled query plans, thus improving
performance.  Second, stored procs can insulate the application code
from table changes keeping you from having to recompile a buch of C code
everytime you add or delete a column.

Hope this helps

Jim



Mon, 15 Dec 1997 03:00:00 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. DB-Library error 10040: DB-Library network communication layer not loaded

2. Visual Basic App using Foxpro DB vs Access DB vs SQL Server DB

3. searching for simple file-db-library

4. DB-Library vs ODBC speed

5. DB-Library vs. ODBC

6. DB-Library vs. ODBC

7. DB-Library/C vs. ODBC

8. Client Lib vs. DB-Library under UNIX (Sol2)

9. Can you use the resultset of a StoredProc in another StoredProc

10. ADO Library vs. ADO Recordset Library

11. Performance Problem with StoredProc - ADO vs Analyzer

12. DB-library library


 
Powered by phpBB® Forum Software