Introduction

Zend_Db_Profiler can be enabled to allow profiling of queries. Profiles include the queries processed by the adapter as well as elapsed time to run the queries, allowing inspection of the queries that have been performed without needing to add extra debugging code to classes. Advanced usage also allows the developer to filter which queries are profiled.

Enable the profiler by either passing a directive to the adapter constructor, or by asking the adapter to enable it later.

$params = array(
    'host'     => '127.0.0.1',
    'username' => 'webuser',
    'password' => 'xxxxxxxx',
    'dbname'   => 'test'
    'profiler' => true  // turn on profiler
                        // set to false to disable (disabled by default)
);
 
$db = Zend_Db::factory('PDO_MYSQL', $params);
 
// turn off profiler:
$db->getProfiler()->setEnabled(false);
 
// turn on profiler:
$db->getProfiler()->setEnabled(true);

The value of the 'profiler' option is flexible. It is interpreted differently depending on its type. Most often, you should use a simple boolean value, but other types enable you to customize the profiler behavior.

A boolean argument sets the profiler to enabled if it is a TRUE value, or disabled if FALSE. The profiler class is the adapter's default profiler class, Zend_Db_Profiler.

$params['profiler'] = true;
$db = Zend_Db::factory('PDO_MYSQL', $params);

An instance of a profiler object makes the adapter use that object. The object type must be Zend_Db_Profiler or a subclass thereof. Enabling the profiler is done separately.

$profiler = MyProject_Db_Profiler();
$profiler->setEnabled(true);
$params['profiler'] = $profiler;
$db = Zend_Db::factory('PDO_MYSQL', $params);

The argument can be an associative array containing any or all of the keys 'enabled', 'instance', and 'class'. The 'enabled' and 'instance' keys correspond to the boolean and instance types documented above. The 'class' key is used to name a class to use for a custom profiler. The class must be Zend_Db_Profiler or a subclass. The class is instantiated with no constructor arguments. The 'class' option is ignored when the 'instance' option is supplied.

$params['profiler'] = array(
    'enabled' => true,
    'class'   => 'MyProject_Db_Profiler'
);
$db = Zend_Db::factory('PDO_MYSQL', $params);

Finally, the argument can be an object of type Zend_Config containing properties, which are treated as the array keys described above. For example, a file "config.ini" might contain the following data:

[main]
db.profiler.class   = "MyProject_Db_Profiler"
db.profiler.enabled = true

This configuration can be applied by the following PHP code:

$config = new Zend_Config_Ini('config.ini', 'main');
$params['profiler'] = $config->db->profiler;
$db = Zend_Db::factory('PDO_MYSQL', $params);

The 'instance' property may be used as in the following:

$profiler = new MyProject_Db_Profiler();
$profiler->setEnabled(true);
$configData = array(
    'instance' => $profiler
    );
$config = new Zend_Config($configData);
$params['profiler'] = $config;
$db = Zend_Db::factory('PDO_MYSQL', $params);

Using the Profiler

At any point, grab the profiler using the adapter's getProfiler() method:

$profiler = $db->getProfiler();

This returns a Zend_Db_Profiler object instance. With that instance, the developer can examine your queries using a variety of methods:

getTotalNumQueries() returns the total number of queries that have been profiled.
getTotalElapsedSecs() returns the total number of seconds elapsed for all profiled queries.
getQueryProfiles() returns an array of all query profiles.
getLastQueryProfile() returns the last (most recent) query profile, regardless of whether or not the query has finished (if it hasn't, the end time will be NULL)
clear() clears any past query profiles from the stack.

The return value of getLastQueryProfile() and the individual elements of getQueryProfiles() are Zend_Db_Profiler_Query objects, which provide the ability to inspect the individual queries themselves:

getQuery() returns the SQL text of the query. The SQL text of a prepared statement with parameters is the text at the time the query was prepared, so it contains parameter placeholders, not the values used when the statement is executed.
getQueryParams() returns an array of parameter values used when executing a prepared query. This includes both bound parameters and arguments to the statement's execute() method. The keys of the array are the positional (1-based) or named (string) parameter indices.
getElapsedSecs() returns the number of seconds the query ran.

The information Zend_Db_Profiler provides is useful for profiling bottlenecks in applications, and for debugging queries that have been run. For instance, to see the exact query that was last run:

$query = $profiler->getLastQueryProfile();
 
echo $query->getQuery();

Perhaps a page is generating slowly; use the profiler to determine first the total number of seconds of all queries, and then step through the queries to find the one that ran longest:

$totalTime    = $profiler->getTotalElapsedSecs();
$queryCount   = $profiler->getTotalNumQueries();
$longestTime  = 0;
$longestQuery = null;
 
foreach ($profiler->getQueryProfiles() as $query) {
    if ($query->getElapsedSecs() > $longestTime) {
        $longestTime  = $query->getElapsedSecs();
        $longestQuery = $query->getQuery();
    }
}
 
echo 'Executed ' . $queryCount . ' queries in ' . $totalTime .
     ' seconds' . "\n";
echo 'Average query length: ' . $totalTime / $queryCount .
     ' seconds' . "\n";
echo 'Queries per second: ' . $queryCount / $totalTime . "\n";
echo 'Longest query length: ' . $longestTime . "\n";
echo "Longest query: \n" . $longestQuery . "\n";

Advanced Profiler Usage

In addition to query inspection, the profiler also allows the developer to filter which queries get profiled. The following methods operate on a Zend_Db_Profiler instance:

Filter by query elapsed time

setFilterElapsedSecs() allows the developer to set a minimum query time before a query is profiled. To remove the filter, pass the method a NULL value.

// Only profile queries that take at least 5 seconds:
$profiler->setFilterElapsedSecs(5);
 
// Profile all queries regardless of length:
$profiler->setFilterElapsedSecs(null);

Filter by query type

setFilterQueryType() allows the developer to set which types of queries should be profiled; to profile multiple types, logical OR them. Query types are defined as the following Zend_Db_Profiler constants:

Zend_Db_Profiler::CONNECT: connection operations, or selecting a database.
Zend_Db_Profiler::QUERY: general database queries that do not match other types.
Zend_Db_Profiler::INSERT: any query that adds new data to the database, generally SQL INSERT.
Zend_Db_Profiler::UPDATE: any query that updates existing data, usually SQL UPDATE.
Zend_Db_Profiler::DELETE: any query that deletes existing data, usually SQL DELETE.
Zend_Db_Profiler::SELECT: any query that retrieves existing data, usually SQL SELECT.
Zend_Db_Profiler::TRANSACTION: any transactional operation, such as start transaction, commit, or rollback.

As with setFilterElapsedSecs(), you can remove any existing filters by passing NULL as the sole argument.

// profile only SELECT queries
$profiler->setFilterQueryType(Zend_Db_Profiler::SELECT);
 
// profile SELECT, INSERT, and UPDATE queries
$profiler->setFilterQueryType(Zend_Db_Profiler::SELECT |
                              Zend_Db_Profiler::INSERT |
                              Zend_Db_Profiler::UPDATE);
 
// profile DELETE queries
$profiler->setFilterQueryType(Zend_Db_Profiler::DELETE);
 
// Remove all filters
$profiler->setFilterQueryType(null);

Retrieve profiles by query type

Using setFilterQueryType() can cut down on the profiles generated. However, sometimes it can be more useful to keep all profiles, but view only those you need at a given moment. Another feature of getQueryProfiles() is that it can do this filtering on-the-fly, by passing a query type (or logical combination of query types) as its first argument; see this section for a list of the query type constants.

// Retrieve only SELECT query profiles
$profiles = $profiler->getQueryProfiles(Zend_Db_Profiler::SELECT);
 
// Retrieve only SELECT, INSERT, and UPDATE query profiles
$profiles = $profiler->getQueryProfiles(Zend_Db_Profiler::SELECT |
                                        Zend_Db_Profiler::INSERT |
                                        Zend_Db_Profiler::UPDATE);
 
// Retrieve DELETE query profiles
$profiles = $profiler->getQueryProfiles(Zend_Db_Profiler::DELETE);

Specialized Profilers

A Specialized Profiler is an object that inherits from Zend_Db_Profiler. Specialized Profilers treat profiling information in specific ways.

Profiling with Firebug

Zend_Db_Profiler_Firebug sends profiling infomation to the » Firebug » Console.

All data is sent via the Zend_Wildfire_Channel_HttpHeaders component which uses HTTP headers to ensure the page content is not disturbed. Debugging AJAX requests that require clean JSON and XML responses is possible with this approach.

Requirements:

Firefox Browser ideally version 3 but version 2 is also supported.
Firebug Firefox Extension which you can download from » https://addons.mozilla.org/en-US/firefox/addon/1843.
FirePHP Firefox Extension which you can download from » https://addons.mozilla.org/en-US/firefox/addon/6149.

Example #1 DB Profiling with Zend_Controller_Front

// In your bootstrap file
 
$profiler = new Zend_Db_Profiler_Firebug('All DB Queries');
$profiler->setEnabled(true);
 
// Attach the profiler to your db adapter
$db->setProfiler($profiler);
 
// Dispatch your front controller
 
// All DB queries in your model, view and controller
// files will now be profiled and sent to Firebug

Example #2 DB Profiling without Zend_Controller_Front

$profiler = new Zend_Db_Profiler_Firebug('All DB Queries');
$profiler->setEnabled(true);
 
// Attach the profiler to your db adapter
$db->setProfiler($profiler);
 
$request  = new Zend_Controller_Request_Http();
$response = new Zend_Controller_Response_Http();
$channel  = Zend_Wildfire_Channel_HttpHeaders::getInstance();
$channel->setRequest($request);
$channel->setResponse($response);
 
// Start output buffering
ob_start();
 
// Now you can run your DB queries to be profiled
 
// Flush profiling data to browser
$channel->flush();
$response->sendHeaders();