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: mpeppler at itf dot ch (Michael Peppler)
Subject: Sybase::CTlib speed
Date: Jan 4 1996 11:47AM

The thing that got me onto the CTlib bug that I reported yesterday
wasan attempt to measure the speed difference between sybperl 1.x (Perl
4) and the three Perl5 modules.

In particular it seemed to me that the CTlib module was really slow. So
I ran this little test script:

#!/usr/local/bin/perl

use Sybase::CTlib;
use Benchmark;
require 'ctlib.pl';

$x = new Sybase::CTlib mpeppler;

$t0 = new Benchmark;

$x->ct_execute("select date = date, amount = amount from ledger where account like 'CIS%'\n");
while($x->ct_results($type) == CS_SUCCEED) {
    next if(!$x->ct_fetchable($type));
    while(%dat = $x->ct_fetch(1)) {
	$date{$dat{date}} += $dat{amount};
	++$count;
    }
}

$t1 = new Benchmark;
print "\nCTlib $count loops took:",timestr(timediff($t1, $t0)), "\n";

$t0 = new Benchmark;
open(OUT, ">/dev/null");
foreach (sort keys(%date)) {
    print OUT "$_: $date{$_}\n";
}
close(OUT);
$t1 = new Benchmark;

$count = scalar(keys(%date));
print "\n$count foreach loop took:",timestr(timediff($t1, $t0)), "\n";
__END__

The 'date' column in the ledger table is a DATETIME variable, and the
'amount' column is a MONEY variable. The query retrieves 27124 rows,
with 820 different dates.

When run as coded above, the script takes:

   fetch loop:  68 secs (49.88 usr  3.22 sys = 53.10 cpu)
   foreach loop: 0 secs ( 0.32 usr  0.02 sys =  0.33 cpu)

I suspected that the native handling of DATETIME and MONEY datatypes
was causing certain problems, so I used the T-SQL convert() function to
retrieve a CHAR and a FLOAT value, respectively. The script now takes:

   fetch loop:  21 secs (15.07 usr  0.60 sys = 15.67 cpu)
   foreach loop: 1 secs ( 0.25 usr  0.00 sys =  0.25 cpu)

Wow!!!

Even the foreach loop gets a serious beating, needing 30% more time to
do the conversion from MONEY to charecter string.

To narrow things down a bit I ran the script again, converting one,
then the other column a "normal" datatype:
Convert(float, amount):
   fetch loop: 47 secs (36.82 usr  1.62 sys = 38.43 cpu)
Convert(char, date):
   fetch loop: 51 secs (36.62 usr  2.57 sys = 39.18 cpu)

For comparison (using convert() on both columns):

DBlib module:
   fetch loop: 23 secs (11.30 usr  0.90 sys = 12.20 cpu)
Sybperl 1.0xx (perl 4):
   fetch loop: 35 secs (17.40 usr 0.75 sys = 18.15 cpu)
Sybperl module, Perl 5:
   fetch loop: 41 secs (19.18 usr 0.93 sys = 20.11 cpu)


Obviously, these timings should be taken with a large grain of salt...
but I think that the native handling of DATETIME,MONEY and
NUMERIC/DECIMAL datatypes in Sybase::CTlib is going to be made optional
- so that you can still get the speed when you need it.

Michael