Is there a performance difference between PDO fetch statements?

Question

like in

/* Exercise PDOStatement::fetch styles */
print("PDO::FETCH_ASSOC: ");
print("Return next row as an array indexed by column name\n");
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
print("\n");

print("PDO::FETCH_BOTH: ");
print("Return next row as an array indexed by both column name and number\n");
$result = $sth->fetch(PDO::FETCH_BOTH);
print_r($result);
print("\n");

print("PDO::FETCH_LAZY: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $sth->fetch(PDO::FETCH_LAZY);
print_r($result);
print("\n");

print("PDO::FETCH_OBJ: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $sth->fetch(PDO::FETCH_OBJ);
print $result->NAME;
print("\n");

Well the default are FETCH BOTH, I am wondering if FETCH ASSOC is faster when I'm going to fetch a lot of data; or they are the same?

Thanks

Answer 1

I wanted to know the answer to this question, so I wrote a quick benchmark script.

I suggest you run this benchmark on your own server, however, this is a typical result on my setup, for single row results:

1. PDO::FETCH_ASSOC - 936 ms
2. PDO::FETCH_BOTH - 948 ms
3. PDO::FETCH_NUM - 1,184 ms
4. PDO::FETCH_OBJ - 1,272 ms
5. PDO::FETCH_LAZY - 1,276 ms

For large data sets, these results are typical:

1. PDO::FETCH_LAZY - 5,490 ms
2. PDO::FETCH_NUM - 8,818 ms
3. PDO::FETCH_ASSOC- 10,220 ms
4. PDO::FETCH_BOTH - 11,359 ms
5. PDO::FETCH_OBJ - 14,027 ms

See the comments on git hub, fetching all the rows does change the test.