very slow response from CakePHP when performing a simple DB query

Question

I am new to CakePHP framework and is trying to setup a "Hello World" project. When working on it, I experienced a very slow response from CakePHP when performing a simple DB query.

Here are the steps that I have done:

Download the CakePHP framework (2.3.0 RC1) and have it setup
Create a "Test" DB with an empty table named "Tests".

Edit the default AppController.php file as following:

class AppController extends Controller {

    var $uses = array('Test');

    function say_hello() {
    $this->Test->query("select * from test where id=0");
    echo "hello";
    }
}

After that, I accessed the link "http://localhost/app/say_hello" and it took more than 1s to respond.

If I commented out the query statement as following:

class AppController extends Controller {

    var $uses = array('Test');

    function say_hello() {
        //$this->Test->query("select * from test where id=0");
        echo "hello";
    }
}

Then, it only took around 60ms to respond.

This doesn't seem right to me as performing a simple query on an empty table shouldn't take ~940ms. I have tried debuging with DebugKit and it showed that the ControllerAction (in this case, the say_hello action is pretty simple) was taking more than 1s of the processing time. Also note that the slowness issue was not caused by DB as DebugKit showed that there was only one query when executing the say_hello action and that query almost took 0ms to complete.

I am not sure what is causing such slowness. Can any experienced CakePHP members shed me some light on what's wrong in this case? What else I should do to troubleshoot and fix the issue?

Thanks.

DebugKit should tell you exactly how long the query itself took. I assume the additional time is for it to load the model or other similar things - to Jim's point, I would guess it would not add an additional 1s per query after the first. — Dave, Jan 16 '13 at 02:02
Try deleting the `$uses` array and replace `$this->Test->query` with `ClassRegistry::init('Test')->query()`, does it takes less time? (check it after the first one, and remember to clear the cache). — Nunser, Jun 07 '13 at 13:42

score 7 · Answer 1 · answered Jan 15 '13 at 19:54

7

While in debug mode, CakePHP will refresh your database bindings, object cache and schema within a short amount of time, describing tables mapped to defined models.

Before testing a CakePHP app performance, make sure you change the debug level to 0.

answered Jan 15 '13 at 19:54

Martin Samson

3,970
21
25

The debug mode doesn't seems to be the cause. I have disabled the debug mode by adding Configure::write('debug', 0); to the core.php but the slowness issue described above still happened. – user526929 Jan 16 '13 at 00:36
Is your tmp directory writable by the webserver/php process? CakePHP will save table schemas in tmp/cache – Martin Samson Jan 17 '13 at 18:17

score 0 · Answer 2 · edited Nov 24 '16 at 07:53

Please check that your webserver and the database server are on the same physical machine.
If they are on the same physical machine, the time it takes should be less, usually a few hundred milliseconds, If they aren't on the same physical machine, then the network delay will matter. You can dive into the source code of CakePHP framework, it uses the PDO to connect the database and call some functions of PDO, every time will takes a quite time because of the network delay time. So this solution may be unapporiate, you should write a specific API, then you PHP code to call the API instead of querying the database directly.

very slow response from CakePHP when performing a simple DB query

2 Answers2