9.2. Zend_Db_Profiler
Prev | Chapter 9. Zend_Db | Next

## 9.2. Zend_Db_Profiler

### 9.2.1. 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.

    <?php
    require_once 'Zend/Db.php';

    $params = array (
        'host'     => '127.0.0.1',
        'username' => 'malory',
        'password' => '******',
        'dbname'   => 'camelot',
        'profiler' => true  // turn on profiler; set to false to disable (default)
    );

    $db = Zend_Db::factory('PDO_MYSQL', $params);

    // turn off profiler:
    $db->getProfiler()->setEnabled(false);

    // turn on profiler:
    $db->getProfiler()->setEnabled(true);
    ?>

### 9.2.2. Using the Profiler

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

    <?php
    $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.

- `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:

    <?php
    $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:

    <?php
    $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";
    ?>

### 9.2.3. 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:

#### 9.2.3.1. 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.

    <?php
    // Only profile queries that take at least 5 seconds:
    $profiler->setFilterElapsedSecs(5);

    // Profile all queries regardless of length:
    $profiler->setFilterElapsedSecs(null);
    ?>

#### 9.2.3.2. 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.

    <?php
    // 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 (so we can figure out why data keeps disappearing)
    $profiler->setFilterQueryType(Zend_Db_Profiler::DELETE);

    // Remove all filters
    $profiler->setFilterQueryType(null);
    ?>

#### 9.2.3.3. 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 Section 9.2.3.2, “Filter by query type” for a list of the query type constants.

    <?php
    // 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 (so we can figure out why data keeps
    // disappearing)
    $profiles = $profiler->getQueryProfiles(Zend_Db_Profiler::DELETE);
    ?>

Prev | Up | Next
Chapter 9. Zend_Db | Home | 9.3. Zend_Db_Select