Re: Bugs / Performance-Issues of SQL::Statement

Hi Jens,

Thanks much for the patches. I'll look into them as soon as possible=20
and presumably add them to a new release soon thereafter. I'm happy to=20
discuss your other ideas. Unless they're DBI specific, though, we=20
should probably do that off-list.

--=20
Jeff

jens.rehsack@bayerbbs.com wrote:
> Hi Jeff,
>
> because of very poor answer times of our SQL::Statement subclass, I=20
> investigated some time in analysing.
> At first I removed a small bug, which brings SQL::Statement failing on =

> 'select distinct':
> Argument "LN1" isn't numeric in numeric comparison (<=3D>) at=20
> /usr/lib/perl5/vendor_perl/5.8.5/SQL/Statement.pm line 972.
>
> I fixed that place and the same bug at another place. Reason seems the =

> assumption, your implementation of looks_like_number instead of DBI one=
's.
> But DBI's version returns an array containing 0 or 1 for each parameter=
=20
> given to prove. Checking that array (as was done), looks_like_number
> ('a','b') will always return (0,0) which is evaluated as true.
>
> The best result in several steps improving performance was removing the=
=20
> %$cols =3D reverse %{ $col_nums }; line from sub eval_where.
>
> Applying all changes as attached, reduced my test-script from 68 second=
s=20
> down to 34 seconds:
>
> Total Elapsed Time =3D 69.85 Seconds
> User Time =3D 67.56 Seconds
> Exclusive Times
> %Time ExclSec CumulS #Calls sec/call Csec/c Name
> 61.2 41.38 66.150 135036 0.0003 0.0005 SQL::Statement::eval_where=

> 16.7 11.31 11.320 591834 0.0000 0.0000 SQL::Statement::get_row_va=
lue
> 15.2 10.29 24.770 591766 0.0000 0.0000=20
> SQL::Statement::process_predicate
> 3.57 2.410 2.410 295917 0.0000 0.0000 SQL::Statement::is_matched=

> 3.49 2.360 2.470 11 0.2145 0.2245 SQL::Statement::join_2_tab=
les
> 1.92 1.300 70.510 6 0.2167 11.751 SQL::Statement::SELECT
> 1.11 0.750 0.750 455522 0.0000 0.0000 DBI::looks_like_number
> 1.04 0.700 0.700 161469 0.0000 0.0000=20
> SQL::Statement::TempTable::fetch_row
> 0.07 0.050 0.230 9 0.0056 0.0256 main::BEGIN
> 0.07 0.050 0.050 10 0.0050 0.0050 SQL::Statement::BEGIN
> 0.04 0.030 0.030 107 0.0003 0.0003 Exporter::import
> 0.04 0.030 0.030 13 0.0023 0.0023 BDCconf::Query::assignResu=
lt
>
> Total Elapsed Time =3D 36.15654 Seconds
> User Time =3D 33.72654 Seconds
> Exclusive Times
> %Time ExclSec CumulS #Calls sec/call Csec/c Name
> 33.2 11.21 29.084 135036 0.0001 0.0002 SQL::Statement::eval_where=

> 27.8 9.398 9.398 591834 0.0000 0.0000 SQL::Statement::get_row_va=
lue
> 19.4 6.558 17.874 591766 0.0000 0.0000=20
> SQL::Statement::process_predicate
> 11.0 3.720 3.860 6 0.6200 0.6433 Data::Dumper::Dumpxs
> 6.18 2.083 2.207 11 0.1894 0.2006 SQL::Statement::join_2_tab=
les
> 4.55 1.534 1.534 295917 0.0000 0.0000 SQL::Statement::is_matched=

> 2.04 0.689 0.689 161469 0.0000 0.0000=20
> SQL::Statement::TempTable::fetch_row
> 1.14 0.384 0.384 455528 0.0000 0.0000 DBI::looks_like_number
> 1.09 0.369 32.404 6 0.0616 5.4006 SQL::Statement::SELECT
> 0.89 0.300 32.829 3 0.1000 10.942 BDCconf::Query
> 0.47 0.160 32.404 3 0.0533 10.801 SQL::Statement::JOIN
> 0.42 0.140 0.140 6 0.0233 0.0233 Data::Dumper::new
>
> I hope you like the patch or the idea behind it - because we (here in t=
he=20
> office) have some more ideas we like to discuss ...
>
>
>
> Freundliche Grüße / Best Regards
>
> Jens Rehsack
> _________________________________________
>
> Fa. Manß & Partner
> Phone: +49 - 214 - 30 - 46 193
> Fax: +49 - 214 - 30 - 31 625
> E-mail: jens.rehsack@bayerbbs.com
> Web: http://www.BayerBBS.com
>
> Geschäftsführung: Vorsitzender Andreas Resch | Arbeitsdirektor =
Norbert=20
> Fieseler
> Vorsitzender des Aufsichtsrats: Klaus Kühn
> Sitz der Gesellschaft: Leverkusen | Amtsgericht Köln, HRB 49895
> =20