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