When to go for stored procedures rather than embedded SQL

I am confused as when to go for stored procedures rather than embedded SQL in the code

When I googled out, I found out these points

• They allow modular programming.
• They can reduce network traffic.
• They can be used as a securit开发者_StackOverflow社区y mechanism.

Is please tell me how does network traffic is related to it ??

Another main advantage for SP: you can change them (to bugfix, to extend) without changing your application code .... yet another layer of separation, which can be beneficial.

And also: security. If you use SProcs for everything, all your callers need in terms of permissions on your database is EXECUTE permissions on those SProcs - they don't need direct read/write access to your tables.

It can reduce network traffic in the sense that you send a single command to a stored proc rather than line after line of SQL statements.

Another benefit is the performance of queries themselves is better than embedded SQL due to being pre-compiled.

they can reduce network traffic by only returning the required data to the client.

Or to turn it around; a design/coding practice that can waste network traffic is to select a set of data from the DB, return it to the client and do processing there on some of the dataset. Obviously if you are working on some of the data set it would be better from a traffic perspective to not send to the client the data that is not being processed

It will reduce network traffic in the event that your database server and your server/client running the embedded-SQL are seperate.

It reduces network traffic because stored procedures are handled on the Database Server; for embedded-SQL running on a seperate machine, the database accesses must be handled over the network, thus increasing traffic.

If your embedded-SQL and database are on the same machine it will have no effect on network traffic. An example is a LAMP stack on one machine.

I would firstly question going stored procs at all...

Unlike actual programming language code, they:

• not portable (every db has its own version of PL/SQL. Sometimes different version of the same database are incompatible - I've seen it)
• not easily testable (not supported by industry standard unit testing frameworks)
• not easily updatable/releasable (you need to drop/create them - ie modify the db to change)
• do not have library support (why write code when someone else has)
• are not easily integratable with other technologies (try calling a web service from them)
• are typically about as primitive as Fortran and thus are inelegant and laborious to get useful coding done
• do not offer debugging/tracing/message-logging etc (some dbs may support this - I haven't seen it though)
• etc.

If you have a very database-specific action (eg an in-transaction action to maintain db integrity), or keep your procedures very atomic and simple, perhaps you might consider them.

Caution is advised when specifying "high performance" up front. It often leads to poor choices at the expense of good design and it will bite you much sooner than you think.

Use stored procedures at your own peril (from someone who's been there and never wants to go back). My recommendation is to avoid them like the plague.

It depends.

• Are you writing an application that should be run with several databases?
• What kind of data operation does your application require? Simple and thin data manipulation?

I suppose this isn't your case, because you tagged your question as 'plsql', 'SQL', 'Store procedures'. The concept of embedded SQL in Pl/Sql is the follow:

Embedded SQL statements incorporate DDL, DML, and transaction control statemen within a procedural language program. They are used with the Oracle precompilers. Embedded SQL is one approach to incorporating SQL in your procedural language applications. Another approach is to use a procedural API such as Open Database Connectivity (ODBC) or Java Database Connectivity (JDBC).

In that case there are many and important reasons. The most important are:

1. The short answer could be that it's easier to write highly efficient code to access large amount of data in "Oracle database" in PL/SQL store procedures than in any others language. This because it's strictly integrated in the Oracle database.

2. Read the manual before: Advantages of Pl/sql stored procedures

   • Improved performance
     • Network traffic(small amount of information sent over a network). With a single call of a store procedure, a large amount of data manipulation can be done on the db server, without to go back and forth with individual sql statements and without to send over the network the data needed for the intermediate state of data manipulation itself. This concept is strongly related to applications in which intensive and highly efficient data operations/manipulation are required. It's not only a matter of subset of data to be used and sent to the client, but a matter of a quality of data in the intermediate data processing state in order to achieve the final data! If the result needed involve many sql steps and statements to be done, the advantage is evident.
   • No compilation is required at compilation time
   • More probability that the code is in Shared pool of the SGA.
   • Memory allocation of the code
   • Security with definer's rights procedures
   • Inherited privileges and schema context with invoker's rights procedures

3. Specific characteristics of the pl/sql and Oracle database, just to write a few of these:

   • Advantages doing independent units of Work with Autonomous Transactions`

   • DML, transaction management and exception handler inside the db

   • Calling sql function **inside SQL

   • Packaged cursor

   • Streaming table function . Table functions with the CURSOR expression, enable you to stream data through multiple transformations, in a single SQL statement.

   • Deterministic function

   • Complex dynamic sql manipulation using dbms_SQL API in conjunction with native dynamic SQL(famous fourth method).

   • All modular reasons(you already mentioned): 1 Encapsulating Calculations 2 To simplify sub queries used inside outer sql 3 Combining scalar and aggregate values inside the same sql 4 Write once, using many.

   • Etc...

Stored procedures may be required to get the performance you need from your application code. The biggest problem with embedded SQL is that all of the business logic typically goes into the application code. This can be hugely inefficient. For example, developers will start doing client side joins: They call the database to get a set of ID values for other table records then query each of those tables one record at a time to retrieve the data they require. What can be done with one roundtrip to the database with a stored procedure may now take hundreds or thousands of roundtrips to the database with embedded sql. Each roundtrip to the database takes a lot of time not to mention that each query will have to be compiled tremendously increasing the load on the database server.

If your application is a low volume application with few users this can work. High volume applications with lots of users can quickly overload even large database servers and cause severe performance problems, even to the point where the application stops working.