Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

ORM with Oracle - 4 queries just to get one record?

It's me again. Again, ORM on Oracle.

Why is Phalcon ORM firing four SQL queries for such a simple query as below?

Resource::query()
            ->where('STAFF_NO = :staff:', array( 'staff' => $staffNo ))
            ->execute()
            ->getFirst()

Here is what I caught using event manager:

[Tue, 08 Sep 15 17:18:00 +0200][INFO] SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END RET FROM ALL_TABLES WHERE TABLE_NAME='RESOURCES'
[Tue, 08 Sep 15 17:18:00 +0200][INFO] SELECT TC.COLUMN_NAME, TC.DATA_TYPE, TC.DATA_LENGTH, TC.DATA_PRECISION, TC.DATA_SCALE, TC.NULLABLE, C.CONSTRAINT_TYPE, TC.DATA_DEFAULT, CC.POSITION FROM ALL_TAB_COLUMNS TC LEFT JOIN (ALL_CONS_COLUMNS CC JOIN ALL_CONSTRAINTS C ON (CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND CC.TABLE_NAME = C.TABLE_NAME AND CC.OWNER = C.OWNER AND C.CONSTRAINT_TYPE = 'P')) ON TC.TABLE_NAME = CC.TABLE_NAME AND TC.COLUMN_NAME = CC.COLUMN_NAME WHERE TC.TABLE_NAME = 'RESOURCES' ORDER BY TC.COLUMN_ID
[Tue, 08 Sep 15 17:18:02 +0200][INFO] SELECT resources.IC_NO, resources.STAFF_NO, resources.NAME FROM resources WHERE resources.STAFF_NO = :staff
[Tue, 08 Sep 15 17:18:02 +0200][INFO] SELECT COUNT(*) "numrows" FROM (SELECT resources.IC_NO, resources.STAFF_NO, resources.NAME FROM resources WHERE resources.STAFF_NO = :staff)


33.7k
Accepted
answer

By default, Phalcon uses database introspection to know what fields must be mapped, basic validations, etc. In production, you have to enable a meta-data cache to avoid these queries being executed on every request. https://docs.phalconphp.com/en/latest/reference/models-metadata.html

Thanks Andy. Now it's reduced to two queries.

I still wish Phalcon does not execture the SELECT COUNT query unless when it is needed.