Zend_Db and unbuffered queries

Question

I have a long running query which I try to fetch via an unbuffered pdo-statement (see code next). But when I 'execute' the query unbuffered, or I do a 'fetchAll', the timings (also see under) don't really change much... What I thought is that unbuffered just executes the query and give me a cursor?

The get_db function returns a Zend_Db object.

  l('Start 1');

  $sql = get_db('ATO')->select()
                       ... Big query...
                       ->assemble();

  l('Assembled');

  get_db('APNS')->query($sql)->fetchAll();

  l('All fetched... Going again!');

  $PDOStatement = get_db('ATO')->getConnection()
                                ->prepare($sql,
                          array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));

  l('prepared');

  if ( $PDOStatement->execute() === false ) {
    l(':(');
    exit(1);
  }

  l('fetching');

  while ( ($PDOrow = $PDOStatement->fetch()) !== false ) {
  }

  l ('all done');

  function l($t) {
    static $start = null;
    if ( $start === null ) {
      $start = microtime(true);
    }
    echo sprintf("[%0.5f] %s\n", microtime(true) - $start, $t);
  }

And the timings:

[0.00000] Start 1
[0.02262] Assembled
[214.69091] All fetched... Going again!
[214.69105] prepared
[417.01584] fetching
[420.55217] all done

As you can see there is nearly no difference in fetching all and the unbuffered variant.

What might I be doing wrong here?

Thanks!

Answer 1

You are wrong because you try to use unbuffered queries to speed up.

1. Buffered queries fetch all result to PHP memory.
2. With unbuffered result lies on server until it would be fetched.

Every way has own advantages and disadvantages but it weakly related to time.

Read manual Buffered and Unbuffered queries