Zend_Db is a database abstraction layer, and is intended to
provide a common API for SQL operations.
Zend_Db_Table is a
Table Data Gateway, intended to abstract common table-level database
operations. Due to their abstract nature and the "magic" they do under
the hood to perform their operations, they can sometimes introduce
performance overhead.
In order to keep usage as simple as possible, and also to support
constantly changing schemas during development,
Zend_Db_Table does some magic under the hood: on
first use, it fetches the table schema and stores it within object
members. This operation is typically expensive, regardless of the
database -- which can contribute to bottlenecks in production.
Fortunately, there are techniques for improving the situation.
Zend_Db_Table can optionally utilize
Zend_Cache to cache table metadata. This is
typically faster to access and less expensive than fetching the
metadata from the database itself.
The Zend_Db_Table
documentation includes information on metadata caching.
As of 1.7.0, Zend_Db_Table also provides support
for hardcoding metadata in the table definition. This is
an advanced use case, and should only be used when you know the
table schema is unlikely to change, or that you're able to keep
the definitions up-to-date.
Zend_Db_Select is relatively good at its job. However,
if you are performing complex queries requiring joins or
sub-selects, it can often be fairly naive.
The only real answer is to write your own SQL;
Zend_Db does not require the usage of
Zend_Db_Select, so providing your own, tuned
SQL select statements is a perfectly legitimate approach,
Run EXPLAIN on your queries, and test a variety of
approaches until you can reliably hit your indices in the most
performant way -- and then hardcode the SQL as a class property
or constant.
If the SQL requires variable arguments, provide placeholders in
the SQL, and utilize a combination of
vsprintf() and array_map() to
inject the values into the SQL:
// $adapter is the DB adapter. In Zend_Db_Table, retrieve
// it using $this->getAdapter().
$sql = vsprintf(
self::SELECT_FOO,
array_map(array($adapter, 'quoteInto'), $values)
);