Speeding SELECT for a small number of fields OVER large number of record in Perl DBD::monetdb

31 Mar 2016

      Hi,

I'm using Perl DBD::monetdb from MonetDB source for close to a year
now. Recently, I discovered using SQL SELECT for one or two fields for
a large number of records, for example, 100000 records is too slow
compared to how faster MonetDB engine is. The same SQL SELECT is way
faster in MySQL due to C implementation of DBD::mysql.

MonetDB connector in Python is still way faster than Perl DBD::monetdb
because it allows user to tell the connector the maximum number of
records the user needs and the connector uses Xexport command
straightaway and only once.

In Perl, DBD::monetdb calls Mapi.pm via getReply which issues Xexport
command many times depending on the number of records in the SELECT
statement. Each time, it retrieves only 100 records. For 100000
records, it needs to call Xexport 1000 times.

I did my own patch to make getReply call Xexport only once a the rest
of the records after the initial request. With the patch, the time it
takes to select 2 fields for 101024 records drops from more 24 seconds
to under 4 seconds. My patch is:

<pre>
sub getReply {
  my ($self)= @_;

  if ($self->{active} == 0) {
    return $self->getBlock();
  } elsif ($self->{next} < $self->{replysize} + $self->{skip}) {
    return $self->getRow();
  } elsif (${self}->{offset} + $self->{replysize} < $self->{count}) {
    # get next slice
    my $rs = $self->{replysize};
    my $offset = $self->{offset} + $rs;
    my $therest = $self->{count} - $self->{replysize};
    $self->putblock("Xexport $self->{id} $offset $therest");   # FAST
    #$self->putblock("Xexport $self->{id} $offset $rs");       # SLOW
    return $self->getBlock();
  } else {
    # close large results, but only send on next query
    if ($self->{id} > 0 && $self->{count} != $self->{replysize}) {
      push @{$self->{piggyback}}, "Xclose $self->{id}";
      $self->{skip_in}++;
    }
    $self->{active} = 0;
  }
  return $self->{active};

}
</pre>

I hope this could help other people.

Regards,
Puthick

Speeding SELECT for a small number of fields OVER large number of record in Perl DBD::monetdb

Puthick Hok