We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

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)


34.6k
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.phalcon.io/en/latest/reference/models-metadata.html



2.9k

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.



801
edited Feb '20

Hi Muhammad,

I have same problem, is it a configuration?
how did you managed to reduced to 2 queries, any code snippet if possible..



801
in reply to Andres Gutierrez

Hi Andy,

This link is not working... :(

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.phalcon.io/en/latest/reference/models-metadata.html