search

phalcon / cphalcon

High performance, full-stack PHP framework delivered as a C extension.
https://phalcon.io
BSD 3-Clause "New" or "Revised" License
10.79k stars 1.96k forks source link

[BUG]: The setting of phalcon.orm.resultset_prefetch_records is not taking effect. #16322

Open m-yamaishi09580 opened 1 year ago

m-yamaishi09580 commented 1 year ago

Describe the bug Hello. I upgraded from Phalcon v3.1 to v5 and noticed that when I use the Phalcon\Mvc\Model::find method to retrieve a result set and then use it multiple times with foreach, the number of SQL queries increased, resulting in a performance degradation in my application. After checking the discussion at https://github.com/orgs/phalcon/discussions/15889, I found out that in v3.1, the result set was kept in memory if the number of records was 32 or less, and the number of SQL queries did not increase even if foreach was used multiple times. However, in v5, it was changed to control pre-fetching with phalcon.orm.resultset_prefetch_records. So, I tried setting phalcon.orm.resultset_prefetch_records = "32" in my php.ini file, but it had no effect and increased the number of SQL queries.

Could you tell me what can be done to fix this issue?

To Reproduce Steps to reproduce the behavior:

CREATE TABLE public.users (
  id bigserial not null
  , name text
  , age text
  , created_at timestamp(6) without time zone
  , modified_at timestamp(6) without time zone
  , primary key (id)
);
class Users extends \Phalcon\Mvc\Model
{
    public $id;
    public $name;
    public $age;
    public $created_at;
    public $modified_at;

    /**
     * Initialize the method for the model.
     */
    public function initialize()
    {
        $this->setSchema('public');
        $this->setSource('users');
    }
}
$users = Users::find();

foreach ($users as $user) {
    $names[] = $user->name;
}
foreach ($users as $user) {
    $names[] = $user->name;
}
foreach ($users as $user) {
    $names[] = $user->name;
}
LOG:  execute pdo_stmt_00000001: SELECT "users"."id", "users"."name", "users"."age", "users"."created_at", "users"."modified_at" FROM "public"."users"
LOG:  execute pdo_stmt_00000002: SELECT "users"."id", "users"."name", "users"."age", "users"."created_at", "users"."modified_at" FROM "public"."users"
LOG:  statement: DEALLOCATE pdo_stmt_00000001
LOG:  execute pdo_stmt_00000003: SELECT "users"."id", "users"."name", "users"."age", "users"."created_at", "users"."modified_at" FROM "public"."users"
LOG:  statement: DEALLOCATE pdo_stmt_00000002
LOG:  statement: DEALLOCATE pdo_stmt_00000003

Expected behavior The number of SQL queries should be limited to once.

Details

phalcon

Phalcon is a full-stack PHP framework, delivered as a PHP extension, offering lower resource consumption and high performance. phalcon => enabled Author => Phalcon Team and contributors Version => 5.2.1 Build Date => Feb 28 2023 19:21:37 Powered by Zephir => Version 0.17.0-$Id$

Directive => Local Value => Master Value phalcon.db.escape_identifiers => On => On phalcon.db.force_casting => Off => Off phalcon.orm.case_insensitive_column_map => Off => Off phalcon.orm.cast_last_insert_id_to_int => Off => Off phalcon.orm.cast_on_hydrate => Off => Off phalcon.orm.column_renaming => On => On phalcon.orm.disable_assign_setters => Off => Off phalcon.orm.enable_implicit_joins => On => On phalcon.orm.enable_literals => On => On phalcon.orm.events => On => On phalcon.orm.exception_on_failed_save => Off => Off phalcon.orm.exception_on_failed_metadata_save => On => On phalcon.orm.ignore_unknown_columns => Off => Off phalcon.orm.late_state_binding => Off => Off phalcon.orm.not_null_validations => On => On phalcon.orm.resultset_prefetch_records => 32 => 32 phalcon.orm.update_snapshot_on_save => On => On phalcon.orm.virtual_foreign_keys => On => On phalcon.warning.enable => On => On


 - PHP Version: (`php -v`)
PHP 8.1.17 (cli) (built: Mar 14 2023 23:07:43) (ZTS Visual C++ 2019 x64)
Copyright (c) The PHP Group
Zend Engine v4.1.17, Copyright (c) Zend Technologies
    with Zend OPcache v8.1.17, Copyright (c), by Zend Technologies
m-yamaishi09580 commented 1 year ago

I wondered if resultset_prefetch_records can be overwritten in the INI file after it was set during Phalcon compilation, so I tried compiling Phalcon with the value of resultset_prefetch_records set to 32 in an Ubuntu 22.04.2 LTS environment. After confirming that the value of phalcon.orm.resultset_prefetch_records was indeed 32 without being overwritten in the INI file, I tried running the above code and found that the number of SQL queries issued remained at 3. I'm even more confused now. Does anyone know about this issue?

niden commented 1 year ago

I can confirm this is a bug. Although the setting is there, even if you set it up in the model, it does not read it properly

This one also does not work:

Users::setup(
    [
         'prefetchRecords' => '32',
    ]
);
niden commented 1 year ago

I have a solution to move away from the php.ini settings and instead to use a Settings class, to define all these options. This way it will be a bit easier and more isolated on a per application basis.

s-ohnishi commented 7 months ago

It would be useful to have a mechanism to override phalcon.orm.resultset_prefetch_records temporarily or by model, but isn't it necessary that the phalcon.orm.resultset_prefetch_records specified before that be reflected? Although it is not specifically described, is phalcon.orm.resultset_prefetch_records already reflected?