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?

You need to look into caching the results at a very low level, so that there isn't a lot of overhead there to deal with. APC, Memcached, etc. — Charles Harmon, Aug 27 '15 at 18:09

score 6 · Answer 1 · answered May 03 '17 at 04:08

6

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%

answered May 03 '17 at 04:08

aswzen

1,512
29
46

1

Thanks a lot! I have exactly same case. – sonicli May 30 '18 at 09:58
1

Wow, my connection was too slow, simple operations was taking about minutes to perform and this solved my problem. I think CakePHP must implement a way to chose which cursor to use as it is REALLY RELEVANT to speed of your script, thanks! – Newton Pasqualini Filho Jul 23 '18 at 18:33
1

I confirm I got a 100% increase in speed. – Valeriu Ciuca Mar 09 '23 at 09:29

score 0 · Answer 2 · answered Sep 14 '15 at 08:36

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.

CakePHP slow response on SQL Server find

2 Answers2