General discussion


advantages and disadvantages of using Stored procedures

By ramesh.sakanadagi ·
can anyone plese explain me in detail what are the advantages and disadvantages of using stored procedures?

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Well I can give you a summary

by Tony Hopkinson In reply to advantages and disadvanta ...

This is more on the approach of doing your database accesses via sps from an application rather that hitting the tables directly.

Sps Pros

Faster (pre compiled)

Potential for a much greater control of what is allowed, with a really complex schema, or making sure your really complex schema doesn't get bent by an application writer.

Much better security, calls are parameterised and you can get application privileges at the row level, built into the schema.

Chance for making database agnostic applications without sacrificing performance gains unique to each DBMS.

Sounds a no brainer doesn't it?


Less flexible, you need the DBA to make changes before you can write the application.
That means reworking schemas on big servers or on lots of little ones if you distribute with an app.

Much more propriety than high level code, so multiple databases, results in multiple codebases.

Debugging SPs is still a pain in the ***, aside from syntax highlighting there's little support IDE wise.

Th real questions to ask are how reactive do you need to be and how reactive can you afford to be.

Given the choice I'd plump for SPs every time , but I'm a DBA and an application developer. You need more than just knowing SQL.

A note of caution I worked at a place that went mad on the stored proc route, then the DBAs who did the design moved on.

Left an unbelievably complex database and no one who dare sniff near it. Stalled application development badly.
They no longer exist.

Work really hard on the documentation, and don't complicate things for a laugh, ie leave some things for the application coders to do.
Especially presentation and such.

Collapse -

Stored procedures Advantages

by leirags In reply to advantages and disadvanta ...

I made some programs to companies they need some kind of special reports these programs work helping to comercial programs and if some kind of reports need "litte tuneup" i do this modifing store procedures, these are advantage and the comercial program only use the new procedure because are called by name and works great.

The first disadvantages are you need give the master password to the right people "they know how make all works fine".

The secund are, everyting goes wrong if the procedures are badly modify or "malware".

From programer view: the implementation of procedures inside your program depends of the software you use to develop programs personaly i use:
Powerbuilder, VB 6.

Collapse -


by himabindu.pendurti In reply to advantages and disadvanta ...

Stored procedures and pre compiled and hence execution
takes less time. stored procedures can reduce long SQL
queries to a single line that is transmitted over a wire and
thus reduces client/server traffic. offers efficient reuse of
code and programming abstraction.

The disadvantages are
there is an overhead involved in switching from SQL to
PL/SQL, this may be significant in terms of performance
but usually this overhead is outweighed by performance
advantages of using PL/SQL
more memory may be required when using packages as
the whole package is loaded into memory as soon as any
object in the package is accessed
native compilation can take twice as long as normal

Back to Junk Forum
3 total posts (Page 1 of 1)