Discussions

advantages and disadvantages of using Stored procedures

Tags:
+
0 Votes
Locked

advantages and disadvantages of using Stored procedures

ramesh.sakanadagi
can anyone plese explain me in detail what are the advantages and disadvantages of using stored procedures?
  • +
    0 Votes
    Tony Hopkinson

    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?

    Cons

    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.

    +
    0 Votes
    leirags

    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.

    +
    0 Votes

    Hi

    himabindu.pendurti

    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
    compilation.

    +
    0 Votes
    leirags

    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.

    +
    0 Votes

    Hi

    himabindu.pendurti

    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
    compilation.

  • +
    0 Votes
    Tony Hopkinson

    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?

    Cons

    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.

    +
    0 Votes
    leirags

    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.

    +
    0 Votes

    Hi

    himabindu.pendurti

    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
    compilation.

    +
    0 Votes
    leirags

    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.

    +
    0 Votes

    Hi

    himabindu.pendurti

    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
    compilation.