DBD::_::db::type_info() slow

[Dean Kopesky]

Hi!

While testing an application that generates SQL based on data extracted
from a database, I noticed that about 80% of the runtime was being
consumed by DBI::db::quote().  A significant portion of that was in
DBD::_::db::type_info().  By caching the results of type_info_all() (which
type_info() calls repeatedly), and by memoizing type_info() itself, I was
able to speed it up 5x or so.

I have appended the diffs to this message.  (I don't know if defining new
DB handle attributes willy-nilly is the best way to cache these values, but
it's the best thing I could think of....)

-Dean Kopesky / Bridge Information Systems / [EMAIL PROTECTED]


*** DBI.pm      Mon Jun  4 14:01:39 2001
--- DBI.pm.2    Mon Jul  2 13:07:53 2001
***************
*** 1070,1079 ****
  
      sub type_info {
        my ($dbh, $data_type) = @_;
-       my $tia = $dbh->type_info_all;
-       return unless @$tia;
-       my $idx_hash = shift @$tia;
  
        my $dt_idx   = $idx_hash->{DATA_TYPE} || $idx_hash->{data_type};
        Carp::croak("type_info_all returned non-standard DATA_TYPE index value 
($dt_idx != 1)")
            if $dt_idx && $dt_idx != 1;
--- 1070,1087 ----
  
      sub type_info {
        my ($dbh, $data_type) = @_;
  
+       my $ti_cache = $dbh->{'TypeInfoCache'};
+       my $tia      = $dbh->{'TypeInfoAllCache'};
+       my $idx_hash = $dbh->{'IdxHashCache'};
+ 
+       if ( ! $idx_hash ) {
+               $dbh->{'TypeInfoCache'}    = $ti_cache = {};
+               $dbh->{'TypeInfoAllCache'} = $tia      = $dbh->type_info_all;
+               $dbh->{'IdxHashCache'}     = $idx_hash = shift @$tia;
+       }
+       return unless $idx_hash;
+ 
        my $dt_idx   = $idx_hash->{DATA_TYPE} || $idx_hash->{data_type};
        Carp::croak("type_info_all returned non-standard DATA_TYPE index value 
($dt_idx != 1)")
            if $dt_idx && $dt_idx != 1;
***************
*** 1082,1087 ****
--- 1090,1099 ----
        my @ti;
        my @data_type_list = (ref $data_type) ? @$data_type : ($data_type);
        foreach $data_type (@data_type_list) {
+             if ( exists $ti_cache->{$data_type} ) {
+               return $ti_cache->{$data_type}->[0] unless wantarray;
+               return @{$ti_cache->{$data_type}};
+           }
            if (defined($data_type) && $data_type != DBI::SQL_ALL_TYPES()) {
                push @ti, grep { $_->[$dt_idx] == $data_type } @$tia;
            }
***************
*** 1100,1105 ****
--- 1112,1120 ----
        my @out = map {
            my %h; @h{@idx_names} = @{$_}[ @idx_values ]; \%h;
        } @ti;
+ 
+       $ti_cache->{$data_type} = \@out;
+ 
        return $out[0] unless wantarray;
        return @out;
      }