[Dbix-class] trying to figure out why DBIc query is slow

[Frank Sheiness]

I have two postgres queries that return the same data set.  One has some
hard coded values, the other uses some joins  to figure out which rows to
get.  Both queries run very quickly via the psql client and a DBI script.
Using DBIc, the latter query runs very slowly.  I've tried using
HashReinflator but it's still very slow.  Here are the two queries:

### FAST - about 0.2 seconds for catalyst request to complete
  my $bandwidth =3D $c->model('Intermapper::Datasample')->search(
      {
        'me.dataset_id' =3D> 3165,
        'myself.dataset_id' =3D> 3166,
        -and =3D> [
          'me.sample_time' =3D> {'>=3D' =3D> $from},
          'me.sample_time' =3D> {'<=3D' =3D> $to},
          'myself.sample_time' =3D> {'>=3D' =3D> $from},
          'myself.sample_time' =3D> {'<=3D' =3D> $to},
        ],

      },
      {
        join =3D> 'myself',
        '+select' =3D> qw(myself.sample_mean),
        '+as' =3D> qw(down_mean),
        order_by =3D> 'me.sample_time'
      }
    );

Generates SQL:
SELECT me.dataset_id, me.sample_time, me.sample_mean, me.sample_stdv,
me.sample_min, me.sample_max, me.sample_size, myself.sample_mean FROM
datasample me  JOIN datasample myself ON myself.sample_time =3D
me.sample_time WHERE ( ( ( me.sample_time >=3D ? AND me.sample_time <=3D ? =
AND
myself.sample_time >=3D ? AND myself.sample_time <=3D ? ) AND me.dataset_id=
 =3D ?
AND myself.dataset_id =3D ? ) ) ORDER BY me.sample_time: '2012-10-01',
'2012-11-06 23:59', '2012-10-01', '2012-11-06 23:59', '3165', '3166'

### SLOW - about 13 seconds for catalyst request to complete
  my $bandwidth =3D $c->model('Intermapper::Datasample')->search(
      {
        'dataset.device_id' =3D> $server->intermapper,
        'dataset_2.device_id' =3D> $server->intermapper,
        'dataset.name' =3D> 'BytT',
        'dataset_2.name' =3D> 'BytR',
        'dataset.label' =3D> {'-like' =3D> '%[1]%'},
        'dataset_2.label' =3D> {'-like' =3D> '%[1]%'},
        -and =3D> [
          'me.sample_time' =3D> {'>=3D' =3D> $from},
          'me.sample_time' =3D> {'<=3D' =3D> $to},
          'myself.sample_time' =3D> {'>=3D' =3D> $from},
          'myself.sample_time' =3D> {'<=3D' =3D> $to},
        ],
      },
      {
        join =3D> ['dataset', {myself =3D> 'dataset'}],
        '+select' =3D> qw(myself.sample_mean),
        '+as' =3D> qw(down_mean),
        order_by =3D> 'me.sample_time'
      }
    );

Generates SQL:
SELECT me.dataset_id, me.sample_time, me.sample_mean, me.sample_stdv,
me.sample_min, me.sample_max, me.sample_size, myself.sample_mean FROM
datasample me  JOIN dataset dataset ON dataset.dataset_id =3D me.dataset_id
JOIN datasample myself ON myself.sample_time =3D me.sample_time  JOIN datas=
et
dataset_2 ON dataset_2.dataset_id =3D myself.dataset_id WHERE ( ( (
me.sample_time >=3D ? AND me.sample_time <=3D ? AND myself.sample_time >=3D=
 ? AND
myself.sample_time <=3D ? ) AND dataset.device_id =3D ? AND dataset.label L=
IKE
? AND dataset.name =3D ? AND dataset_2.device_id =3D ? AND dataset_2.label =
LIKE
? AND dataset_2.name =3D ? ) ) ORDER BY me.sample_time: '2012-10-01',
'2012-11-07 23:59', '2012-10-01', '2012-11-07 23:59', '659', '%[1]%',
'BytT', '659', '%[1]%', 'BytR'
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20121107/58b=
6eb37/attachment.htm