Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

DBD::Oracle faster with bound sql than stored procedures?

by andreas1234567 (Vicar)
on Nov 24, 2006 at 13:33 UTC ( [id://585869]=perlquestion: print w/replies, xml ) Need Help??

andreas1234567 has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

(CentOS 4, perl v5.8.5, DBD::Oracle 1.19, Oracle XE)

I'm relatively new to the Oracle database. "Ask Tom" seems to be a good source on efficient use of the Oracle database. For multiple reasons, he recommends (1) Stored Procedures, over (2) SQL using Bind variables, over (3) SQL not using Bind variables (Dynamic sql).

I created a test application that inserts a row, then select a row using approach (1) and (2) above with Perl and DBD::Oracle. The results surprised me: Approach (2) is consistently 20-30% faster than approach (1).
$ ./bm_foo.pl -N=10000 Benchmark: timing 10000 iterations of insert_and_select_foo_ora_sp, in +sert_and_select_foo_ora_sql... insert_and_select_foo_ora_sp: 30 wallclock secs (10.17 usr + 0.38 sys + = 10.55 CPU) @ 947.87/s (n=10000) insert_and_select_foo_ora_sql: 20 wallclock secs ( 5.41 usr + 0.24 sy +s = 5.65 CPU) @ 1769.91/s (n=10000)
Running each approach individually with Devel::DProf
Approach 1 - Stored procedures:
Total Elapsed Time = 30.88130 Seconds User+System Time = 10.92130 Seconds Exclusive Times %Time ExclSec CumulS #Calls sec/call Csec/c Name 42.0 4.590 5.580 20000 0.0002 0.0003 DBI::st::execute 13.7 1.500 1.500 20000 0.0001 0.0001 DBI::st::fetchrow_array 8.97 0.980 0.980 50000 0.0000 0.0000 DBI::st::bind_param 8.97 0.980 7.840 10000 0.0001 0.0008 main::get_foo_ora_sp 6.23 0.680 1.170 20000 0.0000 0.0001 DBI::st::bind_param_inout 5.95 0.650 3.050 10000 0.0001 0.0003 main::insert_foo_ora_sp 3.75 0.410 0.410 19998 0.0000 0.0000 DBI::common::DESTROY 3.20 0.350 0.350 10004 0.0000 0.0000 DBI::_setup_handle 2.47 0.270 0.990 10002 0.0000 0.0001 DBI::_new_sth 2.47 0.270 0.710 10004 0.0000 0.0001 DBI::_new_handle 1.74 0.190 0.190 10000 0.0000 0.0000 main::update_foo_vars 1.74 0.190 11.270 10000 0.0000 0.0011 main::__ANON__ 0.82 0.090 11.360 10002 0.0000 0.0011 Benchmark::__ANON__ 0.82 0.090 0.090 10004 0.0000 0.0000 DBI::st::TIEHASH 0.73 0.080 0.080 9999 0.0000 0.0000 DBD::_mem::common::DESTRO +Y

Approach 2 - SQL using Bind variables
Total Elapsed Time = 20.02347 Seconds User+System Time = 6.393479 Seconds Exclusive Times %Time ExclSec CumulS #Calls sec/call Csec/c Name 58.4 3.735 3.735 30000 0.0001 0.0001 DBI::st::execute 15.7 1.005 1.005 30000 0.0000 0.0000 DBI::st::fetchrow_array 10.4 0.670 3.565 10000 0.0001 0.0004 main::insert_foo_ora_sql 8.76 0.560 2.415 10000 0.0001 0.0002 main::get_foo_ora_sql 4.85 0.310 6.455 10000 0.0000 0.0006 main::__ANON__ 2.58 0.165 0.165 10000 0.0000 0.0000 main::update_foo_vars 1.25 0.080 0.149 9 0.0089 0.0165 main::BEGIN 0.55 0.035 6.490 10002 0.0000 0.0006 Benchmark::__ANON__ 0.31 0.020 0.020 4 0.0050 0.0050 DynaLoader::dl_load_file 0.16 0.010 0.010 1 0.0100 0.0100 DBD::Oracle::db::_login 0.16 0.010 0.010 3 0.0033 0.0033 DBD::Oracle::st::_prepare 0.16 0.010 0.010 4 0.0025 0.0025 DynaLoader::dl_load_flags 0.16 0.010 0.010 8 0.0012 0.0012 Pod::Parser::BEGIN 0.16 0.010 0.020 6 0.0017 0.0033 Pod::Text::BEGIN 0.16 0.010 0.010 23 0.0004 0.0004 vars::import
Complete perl source code:
#!/usr/bin/perl use strict; use warnings; use Devel::DProf; use Benchmark; use Getopt::Long; use Pod::Usage; use DBI; use DBD::Oracle qw(:ora_types); my %foo = ( lnum1 => 123456789, lnum2 => 123456789, lnum3 => 123456789, lnum4 => 123456789, ); my %sths = (); my $dbh = undef; ################################################################## # Insert foo using stored procedure (PL/SQL) sub insert_foo_ora_sp { ################################################################## my $fooref = shift; # ------ prepare function call unless in cache if (!defined($sths{insert_foo_ora_sp})) { my $prepstr = q{BEGIN :retval := pkg_foo.func_insert_foo}. q{(:lnum1,:lnum2,:lnum3,:lnum4); END;}; $sths{insert_foo_ora_sp} = $dbh->prepare ($prepstr); } if (!defined($sths{insert_foo_ora_sp})) { die(q{sth_prepare failed!}); } my $retval = undef; $sths{insert_foo_ora_sp}->bind_param_inout(":retval", \$retval, 32); $sths{insert_foo_ora_sp}->bind_param(q{:} . $_, $fooref->{$_}) for ( +keys %{$fooref}); # ------ execute my $rv = $sths{insert_foo_ora_sp}->execute(); if ($rv != 1) { die(qq{sth_execute returned '$rv'}); } return $retval; } ################################################################## # Insert foo using sql (using bound sql) sub insert_foo_ora_sql { ################################################################## my $fooref = shift; if (!defined($sths{get_next_foo_id_sql})) { my $prepstr = qq{SELECT FOO_ID_SEQ.NEXTVAL FROM DUAL}; $sths{get_next_foo_id_sql} = $dbh->prepare($prepstr); } if (!defined($sths{get_next_foo_id_sql})) { die(q{sth_prepare failed!}); } my $rv = $sths{get_next_foo_id_sql}->execute(); die(q{sth_execute failed:} . $!) unless $rv; my @arr = $sths{get_next_foo_id_sql}->fetchrow_array(); push @arr, $foo{lnum1}, $foo{lnum2}, $foo{lnum3}, $foo{lnum4}; if (!$sths{insert_foo_ora_sql}) { my $sql = "insert into foo (foo_id,lnum1,lnum2,lnum3,lnum4) values + (?,?,?,?,?)"; $sths{insert_foo_ora_sql} = $dbh->prepare ($sql); die(q{sth_prepare failed!}) unless $sths{insert_foo_ora_sql}; } $rv = $sths{insert_foo_ora_sql}->execute(@arr); die(q{sth_execute failed:} . $!) unless $rv; return $arr[0]; # return foo_id } ################################################################## # Get foogroup using PL/SQL stored procedure sub get_foo_ora_sp { ################################################################## my $foo_id = shift; if (!defined($sths{get_foo_sql_sp})) { my $prepstr = qq{BEGIN pkg_foo.proc_get_foo (:pi_foo_id, :pio_foo +); END;}; $sths{get_foo_sql_sp} = $dbh->prepare($prepstr); } if (!defined($sths{get_foo_sql_sp})) { die(q{sth_prepare failed!}); } my $retvalsth = undef; $sths{get_foo_sql_sp}->bind_param(q{:pi_foo_id}, $foo_id); $sths{get_foo_sql_sp}->bind_param_inout(":pio_foo", \$retvalsth, 0, +{ ora_type => ORA_RSET } ); my $rv = $sths{get_foo_sql_sp}->execute(); if (!$rv) { die(qq{sth_execute returned '$rv'}); } my $number_of_rows = 0; $number_of_rows++ while ( my @row = $retvalsth->fetchrow_array ); return $number_of_rows; } ################################################################## # Get foo using sql sub get_foo_ora_sql { ################################################################## my $foo_id = shift; if (!$sths{get_foo_ora_sql_foo}) { my $sql = "SELECT foo_id,lnum1,lnum2,lnum3,lnum4 FROM foo WHERE fo +o_id = ?"; $sths{get_foo_ora_sql_foo} = $dbh->prepare ($sql); die(q{sth_prepare failed!}) unless $sths{get_foo_ora_sql_foo}; } my $rv = $sths{get_foo_ora_sql_foo}->execute($foo_id); die(q{sth_execute failed!}) unless $rv; my $number_of_rows = 0; $number_of_rows++ while (my @row = $sths{get_foo_ora_sql_foo}->fetch +row_array()); return $number_of_rows; } ################################################################## sub update_foo_vars { ################################################################## foreach my $key (keys %foo) { $foo{$key}++; } } # ------ main my $N = 1e2; my $dbistr = q{DBI:Oracle:}; my $dbiuser = q{scott}; my $dbipass = q{tiger}; GetOptions("N|n=i" => \$N) or pod2usage(2); $dbh = DBI->connect($dbistr, $dbiuser, $dbipass); die(q{connection failed!}) if (!defined($dbh)); timethese($N, { insert_and_select_foo_ora_sp => sub { update_foo_vars(); my $foo_id = insert_foo_ora_sp(\%foo); get_foo_ora_sp($foo_id); }, insert_and_select_foo_ora_sql => sub { update_foo_vars(); my $foo_id = insert_foo_ora_sql(\%foo); get_foo_ora_sql($foo_id); } } ); __END__
Complete PL/SQL source code:
-- pkg_foo.sql CREATE OR REPLACE PACKAGE pkg_foo AS FUNCTION func_insert_foo ( lnum1 in NUMBER, lnum2 in NUMBER, lnum3 in NUMBER, lnum4 in NUMBER ) RETURN NUMBER; PROCEDURE proc_get_foo ( pi_foo_id in NUMBER, pio_foo out SYS_REFCURSOR ); END pkg_foo; / CREATE OR REPLACE PACKAGE BODY pkg_foo AS FUNCTION func_insert_foo ( lnum1 in NUMBER, lnum2 in NUMBER, lnum3 in NUMBER, lnum4 in NUMBER ) RETURN NUMBER AS my_foo_id NUMBER; BEGIN SELECT FOO_ID_SEQ.nextval INTO my_foo_id FROM dual; INSERT INTO foo VALUES ( my_foo_id, lnum1, lnum2, lnum3, lnum4 ); RETURN my_foo_id; END func_insert_foo; PROCEDURE proc_get_foo ( pi_foo_id in NUMBER, pio_foo out SYS_REFCURSOR ) IS BEGIN OPEN pio_foo FOR SELECT foo_id, lnum1, lnum2, lnum3, lnum4 FROM foo WHERE foo_id = pi_foo_id; END proc_get_foo; END pkg_foo; /
It seems it doesn't pay of using Oracle Stored Procedures while using DBD::Oracle in this particular scenario. Is the promised speed-up by using SP's lost due to the relative "cost" of binding the variables?

Comments?

Andreas
--

Replies are listed 'Best First'.
Re: DBD::Oracle faster with bound sql than stored procedures?
by jonadab (Parson) on Nov 24, 2006 at 14:35 UTC

    Is speed really your most important consideration?

    I recommend strongly _against_ stored procedures, because I've seen what happens when an application is built around stored procedures, and it's not pretty. In fact, it is my considered opinion that stored procedures are a maintainability nightmare and should be avoided at pretty much all costs.

    The number one problem I see with stored procedures is that they are typically used to write application-level logic in SQL. This is very bad. SQL is a data language, was designed to be a data language, and does not do well as a programming language. Yes, it _has_ loops and things, but if you find yourself using them, you're cruisin' for a bruisin', in my opinion. When you start writing application logic in SQL, you get some very ugly code. Sure, it may all be tucked away in the database as a stored procedure, but somebody is still going to have to maintain it. You'll end up with sixty or a hundred lines of stored procedure to do what could be three lines of Perl and a couple of SQL queries. You end up with the next developer who has to touch that bit of code spending three days figuring out how it works first, instead of three minutes.

    Second, once you start down this path, forever will it dominate your destiny. Stored procedures will chain you ever more tightly to the specific RDBMS you're using, stealing away precious flexibility that you may wish you had back several years down the line, but it will then be too late. Embedding SQL code in your application (as opposed to using an abstraction layer) is bad enough, but embedding application logic in the database is likely to be absolutely fatal, in terms of your ability later to support another RDBMS.

    I do not know who Ask Tom is, but I would run screaming in the opposite direction from this particular advice and take any other recommendation that he offers cum shakero salis.

    There are almost always more important considerations than speed. Premature optimization is a root of all kinds of evil, for which some developers, having strayed, have turned away from the truth and pierced themselves through with many sorrows. When your application has a user-noticeable performance problem and you *profile* it and determine that an unnacceptable portion of its time is spent in a certain section of code, *then* you worry about how to make that section of code go faster, and if that means stepping away from the abstraction layer in that place and embedding custom SQL directly in the application, or using a stored procedure, or whatever, then and only then you do that, and you comment it copiously, with details about exactly *why* it's that way.


    Sanity? Oh, yeah, I've got all kinds of sanity. In fact, I've developed whole new kinds of sanity. You can just call me "Mister Sanity". Why, I've got so much sanity it's driving me crazy.

      I always thought that stored procedures were there to move data logic from the applications to the data store. So that you could take advantage of the same logic (bugs and/or fixes) in all applications that dealt with that data. So, when you have a Java application, a .NET application, a perl CGI script, etc., all accessing the same data, providing various views (and/or methods of access), they can use the stored procedures to do common work, whatever that may be.

      True, they somewhat tie you to a particular vendor. But the same can be said about tie-in from .NET or Java, or that evil Larry Wall. And, if you write your stored procedures in, say, Java, you may not be as tied in as you may think - most of the logic will be moveable, only the actual APIs would be different (and not always all of those).

      I suppose it depends on precisely what you're putting in stored procedures...

      Anyway, back to the original question - is your application living on the same machine as the server? If so, stored procedures won't offer as much benefit - part of their appeal is that they can deal with large quantities of data with much less network overhead.

      Why on Earth would you want to swap your RDBMS?

      If you're paying for Oracle it's because you need Oracle. And having paid for it you'd better use it.

      If you don't know who 'Ask Tom' then perhaps you should find out. His advice is invaluable in building large scalable Oracle databases.

      Personally, I'd use stored procedures for virtually all database access. I'd also put all the business logic I could into the the procs. Coupling your data and business logic protects your application.

      It's the front ends that should be easily swapped out and changed not the database.

      As for maintaince nightmares. Yup, I've seen those, Embedded dynamic SQL in Java code was the worst.

      As for the Oracle client C library, yes its blisteringly fast.

        Why on Earth would I swap my RDBMS? Well, because IBM or MS or whomever offered a much better deal. I've heard that these companies (including Oracle, of course) compete quite a bit ;-)

        What you do now can seriously impact how easy it is to switch, should your non-technical management decree it. If you take advantage of Oracle-specific functions, you can drastically speed up your queries, speed up your development, and hurt your ability to switch should the need arise. Kind of a trade-off.

        Personally, though, while I understand the concept and need for the ability to switch, I'd still go with whatever gave me the maximum benefit under the current infrastructure, without impacting maintainability in a negative way, and then tell management what the costs were to switch our application for when other sales guys approach them to get us to switch. It still may come out as a benefit to switch because the switch itself is a one-time charge, while the licensing and support is an ongoing charge.

        Why on Earth would you want to swap your RDBMS? If you're paying for Oracle it's because you need Oracle. And having paid for it you'd better use it.
        'cause it's life. Sometimes changes happen due to cost, politics and various other reasons. It just happens.

        As for what is swapped out, a well designed system can have any layer of responsibility removed and replaced due to light coupling. i.e. Dont' do database calls from your template system.

        Why on Earth would you want to swap your RDBMS?
        I never wrote that. I'm exploring a new platform of curiosity, trying so see what it has to offer. So far it seems very promising.
        If you don't know who 'Ask Tom' then perhaps you should find cout.
        I bought one of his books and I will continue to read his columns.
        As for maintaince nightmares. Yup, I've seen those, Embedded dynamic SQL in Java code was the worst.
        I second that.
        As for the Oracle client C library, yes its blisteringly fast.
        Do you mean replacing DBD::Oracle with some kind of compiled C routines?

        Andreas
        --

      Hi Jonadab,

      With all due respect, it doesn't sound like you've worked with stored procedures or even understand their purpose very well. Stored procedures have pros and cons just like any other tool.

      Pros:

      1. You don't need to recompile and push out a new application or configuration file to all of your users every time you need to make a change to your sql
      2. stored procedures are 'black boxes' that receive a specified set of parameters and return one or more result sets. How you do that can change without making modifications to the application (DBAs often are more likely to help you optimize your SQL if they don't have to wade through your (insert favorite programming language) code.
      3. The query plan of the stored procedure will be stored in the database, thereby eliminating the compilation and optimization time unless there is major changes in the data
      4. Moving to another DBMS is actually EASIER if you use stored procedures. Why? You call an equivalent stored procedure in the other DBMS (you're going to have to change the SQL anyways so why not separate the sql from your code as much as possible)?
      5. and so on....

      As a DBA of more than 15 years, I hear the argument "stored procedures are bad" at least once a month. They are just like any other tool... it depends on how you use them.

      IMHO, many developers don't like stored procedures simply because they think they lose control of the SQL to the DBAs. In most companies, your local DBA has too much to do then worry about your stored procedures EXCEPTION: if you want to update the stored procedure SQL code in production, you better have tested it thoroughly in your DEV/UAT environments.

      If your DBA is a "Little Napolean" and he/she wants total control, then that DBA is either inexperienced or just plain nuts.

      Jason L. Froebe

      Team Sybase member

      No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1

        '++' x 1e6.

        There is an annoying little phrase that crops up around here all to often in the context of anthropomorphising code, languages, idioms, documentation tools and particular pages of documentation: "XXX is your friend.".

        As much as that phrase annoys me in most contexts I see it used, I'm gonna use it.

        Befriend your local DBA, because if he's your friend, your life just got a whole lot easier.

        Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
        Lingua non convalesco, consenesco et abolesco. -- Rule 1 has a caveat! -- Who broke the cabal?
        "Science is about questioning the status quo. Questioning authority".
        In the absence of evidence, opinion is indistinguishable from prejudice.

        Perhaps what I am against is stored procedures used badly. I will freely admit that badly is the only way I have seen them used to date, but I'll concede that it may be possible to use them in a less problematic way that I have not seen.

        I will stand by this, though: in the absense of strong evidence that a particular section of code is causing a user-noticeable performance issue, the maintainability of the code is more important than its speed.


        Sanity? Oh, yeah, I've got all kinds of sanity. In fact, I've developed whole new kinds of sanity. You can just call me "Mister Sanity". Why, I've got so much sanity it's driving me crazy.
      Is speed really your most important consideration?
      Yes, performance is clearly one of them. That's why I benchmark my code, instead of relying on FUD.
      In fact, it is my considered opinion that stored procedures are a maintainability nightmare and should be avoided at pretty much all costs.
      Your nightmares are different from mine then. Orthogonally different actually. I keep having this horrible nightmare where this big, "flexible", "write-once, run (poorly) everywhere" application full of embedded sql comes to get me.
      You end up with the next developer who has to touch that bit of code spending three days figuring out how it works first, instead of three minutes.
      Yes, but only if you assume (s)he has not previous knowledge of the technology in question (i.e. PL/SQL, the Oracle database). In my opinion, that makes her/him unqualified for the role as developer for that platform. Besides, those three days spent learning a new technology could be the best investment that person made in a long time.

      Andreas
      --
        I keep having this horrible nightmare where this big, "flexible", "write-once, run (poorly) everywhere" application full of embedded sql comes to get me.

        If you actually read my post, I don't like having SQL embedded directly in the application, either. I much prefer having an abstraction layer, wherein all the code that has SQL in it is off to the side in a (probably custom, unless your needs are quite simple) module that the rest of the code uses.

        You end up with the next developer who has to touch that bit of code spending three days figuring out how it works first, instead of three minutes.
        Yes, but only if you assume (s)he has not previous knowledge of the technology in question (i.e. PL/SQL, the Oracle database). In my opinion, that makes her/him unqualified for the role as developer for that platform. Besides, those three days spent learning a new technology could be the best investment that person made in a long time.

        Apparently your experience with stored procedures differs from mine. Considerably. I'm talking about someone who is *comfortable* with SQL, someone that other people on the application's mailing list turn to with their hardest SQL-related questions, spending three days (well, three shifts) figuring out how on earth this set of inter-related stored procedures work. And he's still not confident changing them that he won't break anything.

        Maybe I just ran into some very-badly-written stored procedures (and, I probably should mention at this point that it was not on Oracle, although I doubt if that makes much difference), but that was my experience, and it left a really bad taste in my mouth. Each of those several multi-page stored procedures could have been a very small snippet of code in a high-level language and one or two short SQL queries, coming altogether to six or eight lines, and instead it runs onto five or six *hundred* lines, without comments, because it's doing stuff in SQL that SQL was never designed for.

        SQL is designed for getting information from the database and putting information into the database. When you start messing around with 30+ variables and your loops and conditionals are nested more than about two deep, it's time to use a language that was designed for writing such logic. Use the right tool for the job.


        Sanity? Oh, yeah, I've got all kinds of sanity. In fact, I've developed whole new kinds of sanity. You can just call me "Mister Sanity". Why, I've got so much sanity it's driving me crazy.

      As justifications go for not using a particular language feature, this is pretty weak.

      It's akin to never using regexes or hashes, because you might choose to swap the application to using a langauge that doesn't provide them at some unspecified point in the future.

      Also, your perception of the mainenance problems are very short sighted. SPs come into their own when they are written and maintained externally to the applications that uses them. To any given application they become just another library (use DB::XYZ qw/ xyz_fetch xyz_update /;). In this respect they have the same maintenance issues as any other CPAN or in-house module, which is usually seen as a plus not a minus.

      As for "permature optimization" aspect. There is a worse evil than premature optimisation. It is the assupmption that all optimisation *must* be premature.

Re: DBD::Oracle faster with bound sql than stored procedures?
by pajout (Curate) on Nov 25, 2006 at 13:09 UTC
    Imho, Oracle is very complex monster (I like it :) and Tom's advices are wise...

    As you can read in the previous comment, implementing application logic in stored procedures is not good idea. The better place is application server, whatever it means.

    Please, try to estimate some statistical conditions (load of the RDBMS, quantity of accesses per second, ...) and, after that, find the simplest solution which will satisfy it. There is not too many sites, which needs to deal 10 complex operations per each second... Hopefully sql with bind variables will be OK, specially, when you enhance it by optimization hints.

    I did not played Oracle for few years, but I remember that speed depends on too many factors. Configuration, load, quantity (and statistical distribution !) of rows in affected tables, indices, ... Nobody is able to create optimal application from the scratch.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://585869]
Approved by smokemachine
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others learning in the Monastery: (4)
As of 2024-03-29 04:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found