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

Hi, Im trying to determine through DBI which is the 'fastest' way to perform an insert. the three subs below are the ways I'm trying but over 1500 iterations, there seems to be little difference. Am I timing the wrong things? Are other factors such as table size and indexing more important than method of insert when it comes to time?

Where $iter is about 1500 and $id is the primary key... 

sub standard {
    my $sth = $dbh->prepare('insert into comic.stuff values (?, ?, ?)'
+);
    for (1..$iter) {
        print "$_\n";
        eval {
            $sth->execute($id, $name, $card);
        };
        print "problemos $@\n" if ($@);
        $id++;
    }
}

sub noeval_proc {
    my $sth = $dbh->prepare('call comic.my_proc(?, ?, ?)');
    for (1..$iter) {
        print "$_\n";
        $sth->execute($id, $name, $card);
        $id++;
    }
}
sub procedure  {
    my $sth = $dbh->prepare('call comic.my_proc(?, ?, ?)');
    for (1..$iter) {
        print "$_\n";
        eval {
            $sth->execute($id, $name, $card);
        };
        print "problemos $@\n" if ($@);
        $id++;
    }
}

[download]

Replies are listed 'Best First'.
Re: Benchmarking Oracle - stored proc or insert
by t0mas (Priest) on Dec 19, 2000 at 18:41 UTC
    Inserts on a table with few indexes and no referencial integrity constraints are done very fast on oracle, so a stored procedure will not be such a big performance benefit. Oracle caches sql statements, so your prepared insert statement (if you are using placeholders) will be read from cache and not even compiled again.

    My guess is that you will see a performance decrease if you put the sql command in a string and executes it direct without using placeholders, but only a small one.

    I usually use stored procedures (for inserts) to create a interface layer for the database users, hiding the physical table design from them. This way I can change the physical tables without needing to rewrite any application code. So I advocate the use of stored procs for everything (except building portable applications :)



    /brother t0mas
[reply]
Re: Benchmarking Oracle - stored proc or insert
by mwp (Hermit) on Dec 19, 2000 at 17:50 UTC
    I hazily remember having to commit inserts to Oracle before they would become effective. Do you have AutoCommit turned on? If you don't, that could definitely be the problem. =)

    Otherwise, from a style standpoint, you might like this better than how you're using eval:

    my $sth = $dbh->prepare('...');
$sth->execute($id, $name, $card) 
    || die "problemos $sth->errstr\n";

    [download]

    'kaboo

[reply]
[d/l]
[select]

      AutoCommit, from what I understand, tends to slow things down (it has to commit, then flush the 'pending changes' buffer -- or whatever you call it -- on each insert). I have an app that inserts 300K rows daily, so what I did is commit every 5000 rows (and one at the end of the insertion loop, just to make sure =). That sped things up significantly. Using bind_columns also made a measurable (positive) difference.

      Philosophy can be made out of anything. Or less -- Jerry A. Fodor

[reply]
[d/l]
      Ive got RaiseError on so the || die stuff would be superfluous anyway, Autocommit is on by default. I may try and turn it off and just do one commit at the end and see if it makes any difference.

      NOTE: Chuffin 'eck turning off autocommit and doing one at the end makes the whole lot about 10x faster, i guess its just not committing every call...

[reply]
(jptxs) Re: Benchmarking Oracle - stored proc or insert
by jptxs (Curate) on Dec 19, 2000 at 20:45 UTC

    What t0mas said in this thread is entirely right. The only thing I would add is a question: what are you doing the benchmarking for? If it's just to see how fast it goes, more power to you. If you are trying to figure out how to speed up a particular operation, then you have a few other options if speed is the critical factor.

    Oracle gives you a few ways to use "the direct path" to insert things into a database. Now, this is used for bulk operations more often than not, but as I said I'm not sure what your goal is. You can also create objects in "NO-LOGGING" mode, which basically has that object always use a form of the direct path. The direct path, when it is applied correctly, can give a huge speed benefit - and the nice thing is it really help when there are many indices and triggers to deal with too. The direct path is dangerous though because it skips the redo logs and therefore bypasses Oracle's own recovery mechanisms. I don't know what type of shop you're in, but using the direct path can also effect other business processes that may use the redo logs as a source of information such at replication and backup tools.

    This may be information overkill, but, if it's not, cool. If you want to know more /msg me or try Oracle's Technet, which is a really decent free source of information. 

    "A man's maturity -- consists in having found again the
seriousness one had as a child, at play."   --Nietzsche

    [download]
[reply]
[d/l]
Re: Benchmarking Oracle - stored proc or insert
by AgentM (Curate) on Dec 19, 2000 at 21:02 UTC
    'nother quick idea. Could there be a penalty or even a benefit by simply using do{..}? Might be interestin'....
    AgentM Systems nor Nasca Enterprises nor Bone::Easy nor Macperl is responsible for the comments made by AgentM. Remember, you can build any logical system with NOR.
[reply]
      I'm 90% sure that ->do() does a prepare, then execute under the hood anyway but Ill have a look....
[reply]

Back to Seekers of Perl Wisdom