Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re: Recoding a multi-sql-statement storedProc transaction in a script

by dba (Monk)
on Dec 15, 2004 at 03:40 UTC ( #414926=note: print w/replies, xml ) Need Help??


in reply to Recoding a multi-sql-statement storedProc transaction in a script

Why would you ever want to take a stored procedure and convert to a client based multi-line SQL embedded in perl script?
  • Comment on Re: Recoding a multi-sql-statement storedProc transaction in a script

Replies are listed 'Best First'.
Re^2: Recoding a multi-sql-statement storedProc transaction in a script
by perrin (Chancellor) on Dec 15, 2004 at 04:32 UTC
    Stored procedures have a number of disadvantages compared to SQL embedded in a perl script. One big one is that you need DBA privileges to manage them. Another is that updating them and backing them up is typically more complex than the basic source control steps used for perl code. The biggest one for me is that things which are not pure SQL -- i.e. procedural stuff in the programming language supported by the database -- is usually done in some crippled language without modern constructs or solid debugging tools like Perl's. There is a performance advantage to them when they involve examining a large number of rows that are not actually needed in the end result, but this is not that common a situation.
      Stored procedures have a number of disadvantages compared to SQL embedded in a perl script.

      I know next to nothing about SQL Server, but as an Oracle guy, I have to speak up in support of stored procedures.

      One big one is that you need DBA privileges to manage them.

      If that is really the case, it is an SQL Server oddity. Oracle lets everyone create and manage his own stored procedures.

      Another is that updating them and backing them up is typically more complex than the basic source control steps used for perl code.

      When you back up your database, that backup includes stored procedures. You can update them very easily using the DB SQL client. Of course, you want to put them into source control. You can use the same source control system you use for your Perl code.

      The biggest one for me is that things which are not pure SQL -- i.e. procedural stuff in the programming language supported by the database -- is usually done in some crippled language without modern constructs or solid debugging tools like Perl's.

      Well, those languages are domain languages, and are mainly intended to query the database, which they can do much more elegantly than more general languages (including Perl). Also, some DBMS have plugins that let you write stored procedures in Perl (not sure if that is a good idea, though).

      There is a performance advantage to them when they involve examining a large number of rows that are not actually needed in the end result, but this is not that common a situation.

      There are also the performance advantages of not having to parse and prepare the SQL every time (that will be done only once, and not just once per connection, which is the best you can do from the outside), of using native database datatypes, and of reduced network traffic.

      And then we have: easier transaction management in Perl (just keep AutoCommit on, one transaction can always be a single statement), shorter, simpler code (Perl just needs to call stored procedures, which is simple with our excellent DBI, the stored procedures themselves are usually more concise than their Perl counterparts), accessibilty of the code from other programming environments, the ability to fix code and to see dependencies in one place (rather than everywhere on your server farm) and data validation right in the database.

      Forgive the rant, but I am a hopeless PL/SQL fan boy, and try to keep my programs completely free of any SQL (for the same reasons that I keep them free of HTML). It is all about separation of concerns and using the right tool for the job.

        I know next to nothing about SQL Server

        I've never used SQL Server. My comments refer to Oracle.

        Oracle lets everyone create and manage his own stored procedures.

        Only if you have DBAs who are happy to grant that privilege. None of the ones I've worked with were.

        When you back up your database, that backup includes stored procedures. You can update them very easily using the DB SQL client. Of course, you want to put them into source control. You can use the same source control system you use for your Perl code.

        No special procedure is required to make perl code live in a typical environment. For stored procs, you have to connect to the database and install the new version. It's not horrible, but it is more work than file-based stuff. And it adds to versioning problems between code and database. A good automated code deploy tool could probably fix this issue, but most places don't have one.

        There are also the performance advantages of not having to parse and prepare the SQL every time (that will be done only once, and not just once per connection, which is the best you can do from the outside), of using native database datatypes, and of reduced network traffic.

        With Oracle, when you use placeholders correctly, statements are parsed and then kept in the cache. Sending the request again doesn't require it be parsed again. The network stuff is a savings if you need to examine lots of rows but won't actually be needing them in your final results, as I mentioned. Otherwise, it's negligible.

        accessibilty of the code from other programming environments

        That's the big argument in favor of stored procs, and the only one that I actually buy. I don't think PL/SQL is an adequate language for this kind of development though.

        Forgive the rant

        Ditto! We disagree about most of this, but some of that is surely due to different database policies at our places of work.

        try to keep my programs completely free of any SQL (for the same reasons that I keep them free of HTML)

        There are some nice modules for keeping your SQL statements in a separate file. That's a nice way to do it for people like me who want to avoid stored procs.

      I'm with Thilosophy on this one.

      Sure, you might need to have DBA (or DBO == database owner) privileges to manage stored procedures, but that's actually a good thing - there should be some control on what SQL is run against a server, but source control is simply done the same way as with any other language - store source files in CVS (or perforce, or...), and load the source files to production systems in a controled manner (just as you'd move perl code into production in a controled manner).

      In terms of performance, my tests on Sybase show that stored procedures called as RPCs give the best performance because you don't have no SQL parsing overhead, and you usually don't have the query plan generation overhead either.

      I agree that doing procedural processing in SQL is bad, but if you can perform your requests as set operations then you're ahead.

      In addition, for Sybase at least it is important to keep transactions short, and wrapping them in a stored procedure is one way of achieving that.

      Michael

        Good point, Sybase has different behavior with regard to performance of SQL parsing. Oracle doesn't get an advantage from stored procs because it caches the parsed statements already, but most people seem to use stored procs excusively with Sybase.
Re^2: Recoding a multi-sql-statement storedProc transaction in a script
by punkish (Priest) on Dec 15, 2004 at 04:15 UTC
    see my response above. I wrote the program for one installation. There I had access to the db, and was able to create the storedproc in the database. Now I am using the same program for another installation... in this case it is not yet clear if I will have access to the db to create a storedproc in it. Hence, I am trying to redo the script with all the SQL in the script itself.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://414926]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2021-09-20 04:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?