PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: "Humberto Hernandez" <humberto at axxis dot com dot mx>
Subject: Speed up sybperl queries
Date: Apr 26 1999 4:47PM

Hello List:

We are working in a project converting 60,000+ lines of SQR (An older report
generator for Sybase) code to sybperl. However I am very surprised since the
SQR programs seem to be a lot faster than the same algorithms written using
sybperl. For example in one instance a SQR program runs in 20 seconds vs. 3
minutos for Sybperl. Using the Perl profiler we determined that most of the
time is spent during the SQL execution and we decided to convert several SQL
queries to stored procedures that are loaded when the program is initiated
and are executed several times with different arguments. This way we were
able to aproach the speed of the SQR programs. This leads me to beleive that
SQR uses this technique internally to get its remarkable speed.

The following  is an example of the code we are writting, please forgive any
mistakes since I am writting it for the purpose of explaining and I don't
have sybperl with me to test it.

sub sp_load {
    my ($cmd) = @_;
    $cmd =~ /create\s+procedure\s+(\w+)/i or die;
    my $name = $1;
    $dbh->sql("drop proc $name");
    $dbh->sql("$cmd");
}

# main
sub {
    sp_load("
        create procedure rep0101_01
            @customer char(10) as
        select name
        from ccustomer
        where customer_id = @customer
    ");

    for (1..100) {
        $dbh->sql("exec rep0101_01 '12345$_'", sub { });
    }
}

The problem with this aproach is that we loose a lot of clarity in the code,
and you cannot run two instances of the program at the same time. I am
requesting your help to improve on the technique so we and others can make
extensive use of it.

Thank you
--
  Humberto