Open mvorisek opened 1 year ago
@mvorisek Can confirm that this dramatic performance loss with added references in models is happening, especially if you have lots of cascaded hasOne relations with added fields of each hasOne references.
@mkrecek234 I debug the repro steps on atk4/ui as described above and the problem is not reference travesing. With deeper nesting, the query is not becoming larger and larger. The repro here does use hasMany. For hasOne please submit a separate issue.
The problem here is:
a) https://github.com/atk4/data/blob/4.0.0/src/Model.php#L1469 kick in as when traversed, SubFolder
condition is added, and thus the scope is checked with an extra load, which is prooven also by the performance graph above
b) the select query is slow/large as these:
(
select
coalesce(
count(*),
0
)
from
`file` `_affS_d17cc72bb50e`
where
`atk_afp_file__parent_folder_id` = `file`.`atk_afp_file__id`
) `atk_fp_file__count`
and
`atk_afp_file__parent_folder_id` `atk_fp_file__parent_folder_id`,
(
select
`atk_afp_file__name`
from
`file` `_affpf_f7867cefdce3`
where
(
`atk_afp_file__is_folder` = 1
and `atk_afp_file__id` = `file`.`atk_afp_file__parent_folder_id`
)
) `atk_fp_file__parent_folder`
the fields should not be definitely computed on scope validation if there are not a part of validated scope (there are not) and in general, we might introduce something like "lazy fields", fields computed on the 1st access only.
also the performance graph above shows the query render might not be the fastest, so this should be investigated as well, but please note the real query render is lower, as the performance measurement itself imply significant overhead
can be reproduced on https://github.com/atk4/ui/blob/03a9d65ff32602493ecf9980d740fa3f8fc5e21f/demos/init-db.php#L403
NOTES:
$entity->SubFolder->import...
to$entity->getModel()->import...
, the total import time is about 10x faster (measured /wo xdebug/profiling)