CakePHP slow response on SQL Server find

Question

I have a problem with my app in CakePHP 2.7.1, with Sqlserver Datasource.

When I do a find operation on any table, the response time is extremely slow (more than 1 minute).

I did the research with XDebug + QCachegrind and I found out that the problem is Sqlserver->listSources(), which lists all the 1385 tables on the database.

This is the shortened code (core file lib\Cake\Model\Datasource\Database\Sqlserver.php:172)

public function listSources($data = null) {
    $result = $this->_execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
    while ($line = $result->fetch(PDO::FETCH_NUM)) {
        $tables[] = $line[0];
    }
    return $tables;
}

I put a simple microtime benchmark around the while block and nearly all the process time is taken by these 3 lines.

Is there any way to speed up this?

Is there any way to tell CakePHP not to do the listSources process?

Answer 1

This is the shortened code

The not-shortened code is much more relevant:

public function listSources($data = null) {
    $cache = parent::listSources();
    if ($cache !== null) { 
        return $cache; # <-
    }
    $result = $this->_execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
    if (!$result) {
        $result->closeCursor();
        return array();
    }
    $tables = array();
    while ($line = $result->fetch(PDO::FETCH_NUM)) {
        $tables[] = $line[0];
    }
    $result->closeCursor();
    parent::listSources($tables); # <-
    return $tables;
}

The parent method only populates the cache (in the _cake_model_ cache config), the default cache time in production mode is 999 days and in development it's ten seconds. Given the description you're currently in development mode - recognise that (whilst pretty inconvenient) it's basically a development-environment problem.

There are a couple of solutions, but the simplest by far is to just cache the data for longer, and irrespective of debug/production mode. To do so just change the relevant cache config:

Cache::config('_cake_model_', array(
    ...
    'duration' => '+999 days' # <-
));

If there's a schema change you'll need to remember to clear the cache, otherwise your app won't know about it.

The much-more-intrusive solution (overriding model schema and setSource, hardcoding the model schemas so the app doesn't have to query the db to know) would remove the calls to listSources altogether; but at the significant inconvenience/cost of you needing to manage your model schemas - worth pursuing if this delay is a problem, and not if it isn't.

Answer 2

Open

./Lib/Cake/Model/Datasource/Database/Sqlserver.php

Find this text

PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL

Change to

PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY

When i tried that in my cakephp mssql speed increased 50% even 100%