Hi,
we noticed a slow down after upgrading from 7.10.22 to 7.11.20. The whole upgrade process took around 1 hour to complete. We tracked SQL sessions and had noticed that it repeatedly makes queries like:
SELECT id FROM notes WHERE notes.parent_id = '' AND notes.deleted=0 AND notes.parent_type = 'Cases'
As our database has lot of notes related to cases (parent_type = ‘Cases’) without parents (parent_id is null or empty string) it queries each of them with queries like following:
SELECT notes.*,notes_cstm.* FROM notes LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c WHERE notes.id = '1299a1c0-e303-1a4b-61d6-60a6cdea1efd' AND notes.deleted=0 LIMIT 0,1
We have the same problem after the upgrade. We can see lot of logs querying Cases notes without parent and this is slowing down our suiteCRM instance.
This is the excerpt from the log:
[INFO] Query:SELECT id FROM notes WHERE notes.parent_id = '' AND notes.deleted=0 AND notes.parent_type = 'Cases'
Execution Time:0.00024914741516113
Hook called: Notes::before_retrieve
Retrieve Note : SELECT notes.*,notes_cstm.* FROM notes LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c WHERE notes.id = '1299a1c0-e303-1a4b-61d6-60a6cdea1efd' AND notes.deleted=0
Limit Query:SELECT notes.*,notes_cstm.* FROM notes LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c WHERE notes.id = '1299a1c0-e303-1a4b-61d6-60a6cdea1efd' AND notes.deleted=0 Start: 0 count: 1
… and the same query for each note related to Cases without parent (Case was deleted, but related note wasn’t deleted and the parentId
is being cleared after deleting the Case record)
Note related queries are performed each time for each note without it’s case parent. After deleting all notes with parent_type = 'Cases'
without parent_id
it started working much faster, but unfortunately we are not allowed to manage and clear the database records.
We’ve tried to revert the CRM version back to 7.10.22 and it works fine with the same database instance.
It looks like the problem is related to entering the following part of code:
/data/Relationships/One2MBeanRelationship.php
if (!isset($link->getFocus()->$lhsKey)) {
LoggerManager::getLogger()->warn('One2MBeanRelationship getQuery: Trying to get property of non-object');
$linkFocusLhsKey = null;
} else {
$linkFocusLhsKey = $link->getFocus()->$lhsKey;
}
The similar behaviour is reported in following topic, but there was no clear answer:
I can’t get around why is this being called after upgrade and why are we querying Cases notes on almost each action (studio module browsing, opening modules with cases, etc.). Do you have any clue regarding this?