The advantages of stored procedures, in order by importance, are:
Procedures are executed on the server so messages don't need to go back and forth to the client during the time the procedure is executed.
Procedures are parsed once, and the result of the parsing is stored persistently, so there's no need to reparse for every execution.
Procedures are in the catalog so they are retrievable, and procedures are subject to security provisions, in the same way as other SQL data.
Procedures are in one place so code sharing is easy, and when changes happen there's no need to send code changes to clients.
Less Traffic
Stored procedures mean less message traffic between clients and servers. The client must send some sort of message to initiate the procedure, and the procedure must return some sort of result when the procedure is over, but that's all—no message passing occurs within the procedure. So a stored procedure that contains [n] statements will need only two messages, while an ODBC application that contains [n] statements will need (2 * n) messages. This factor is significant because a message takes at least a few milliseconds (on a TCP/IP connection to the same computer), and most likely a few centiseconds (on a LAN), or even a few deciseconds (on a WAN or Internet connection). Against this, you must set the cost of loading a stored procedure from disk the first time, which takes a few milliseconds. Calculating these factors together, we can say that stored procedures are faster than direct passing of SQL statements when (a) more than two SQL statements in the stored procedure are executed, and (b) the stored procedure is accessed so frequently that it might already be in the operating system's cache.
A stored procedure is not the only thing that leads to less traffic. You can sometimes achieve similar effects with views or constraints. And you must remember that not all application programs need messages over a network—they could be Common Gateway Interface (CGI) programs called by the application server.
A message is not the same thing as a fetch. Beware of twaddle like "if a database has many rows, then stored procedures are good because the excess rows won't be transferred over the network for processing"—the rows won't be transferred anyway if you use WHERE clauses in your SQL statements! However, data-change (INSERT/UPDATE/DELETE) statements can cause useless messages if the DBMS feels obliged to return the number of rows that were changed. That's an SQL Standard requirement, but only Microsoft does it, and the message can be suppressed by telling Microsoft: SET NOCOUNT ON.
At the start of this section, we gave you four advantages to using stored procedures. Traffic reduction is more important than the other three combined. If you're on a network, you need stored procedures, and traffic reduction is the reason.
Semi precompilation
The second advantage of stored procedures is that they're precompiled. This means that the DBMS only has to prepare a statement once, instead of preparing a statement every time it executes. To avoid building false hopes, we should emphasize that the precompilation is only partial, is only temporary, and is not a free lunch.
Let's take Informix as an example. We know that other DBMSs operate a bit differently, but they all operate under the same constraints. In a general fashion, here is what actually goes on.
When a CREATE PROCEDURE statement (for example, CREATE PROCEDURE Sp_proc1) is executed, Informix parses the statement and stores two things in the database catalog: a list of the objects (tables or other stored procedures) on which the procedure depends, and a list of tokens from the parsing. The token list is called pcode. It's a step away from an ASCII command but a long way from executable code. Pcode is somewhat like Java bytecode—it's interpretable, not executable. The pcode is kept in a BLOB field in the catalog's tables.
When EXECUTE PROCEDURE Sp_proc1 is run for the first time, Informix loads the procedure's pcode and makes a query plan. The query plan has to go in a cache, but the cache has only enough room for 16 query plans because query plans require a lot of RAM. If the cache already contains 16 query plans, Informix discards the least-recently-used query plan at this point. The precompilation advantage for stored procedures thus applies only to the last 16 procedures you've used. All other procedures must be reloaded and, in effect, precompiled again, because making the query plan is the bulk of the precompilation job.
When EXECUTE PROCEDURE Sp_proc1 is run and it's not the first time, Informix has a cached query plan ready to go. However, the DBMS must still check the dependency list because the stored procedure might refer to some object that has been altered or dropped since the first time the procedure was executed. The other thing that might have changed since the first time is the parameter values, so Informix reprocesses them too.
Now Informix locks the procedure. Usually stored procedures are not reentrant because some of the variable information is stored outside the user's area. By ensuring that only one job at a time can execute, Informix ensures that executions of stored procedures are serializable.
And then Informix actually does the job.
|
| Author: Arun Krishnan 15 Jun 2004 | Member Level: Silver Points : 0 |
Nice Explanation for Stored Procedure. You may include some more comaprision points
|
| Author: Sankaranarayanan.M 26 Jul 2004 | Member Level: Bronze Points : 0 |
hi
the explanation about SPs is fine and crisp.i need a small clarification. if u want to execute a select query whether u prefer SP or just execute the query from Client itself.Plz anwser this in detail
thankz for anwsers
sankar
|