database Qcodo query speed

not understand the problem, it is a matter of performace are you saying?

It is a matter of performance.
I have 2 versions of a code with slightly different schema.
Version 1 is using qcodo 0.3.x.
Version 2 is using qcodo 0.4.x

I have made a sql patch to migrate version 1 to version 2.
Now, in version 2, qcodo queries of the form QueryArray run a lot slower than than that in version 1.

The SQL patch just converts the old code(Qcodo 0.3.x) database structure to the new code(0.4.x) database structure. I did this by first examining a diff between the schema structure of both the codes. Then, I wrote queries to convert the old code schema to the new code schema and executed the same.

The query which takes about 15s is:
$objDocuments = ComplianceDocuments::QueryArray(
                                  QQ::AndCondition(
                                       QQ::OrCondition(
                                               QQ::Equal(QQN::ComplianceDocuments()->UsersAsComplianceUser->UsersId, $objUser->Id),
                                               QQ::In(QQN::ComplianceDocuments()->GroupsAsComplianceGroup->Groups->Id, QApplication::UserGroups($objUser->Id)),
                                               QQ::Equal(QQN::ComplianceDocuments()->DomainsAsComplianceDomain->Domains->Id, $objUser->PrimaryGroup->Domain->Id)
                                       ),
                                       QQ::Equal(QQN::ComplianceDocuments()->StatusTypeId, StatusType::Approved),
                                       QQ::Equal(QQN::ComplianceDocuments()->IsDeleted, false)
                                   ),
                                   $objCClauses
                               );

When I profile the code, the following is noticed(time in milliseconds):

Function                              InvocationCount  TotalSelfCost TotalInclusiveCost
ComplianceDocumentsGen::InstantiateDbRow     2916       4051             10817
QMySqliDatabaseRow->GetColumn                     85299       2634             7100
QType::Cast                                     34953       446             2608
QType::CastValueTo                             34585       1070             1098
ComplianceDocumentsGen::InstantiateDbResult     35       1069             12171
QDateTime->__construct                             8752       969             1793
php::mysql_query                         530       444             444
php::DateTime->__construct                 17506       310             310
UsersGen::InstantiateDbRow                     140       279             462
QQNode->GetColumnAlias                             1197      209             475
GroupsGen::InstantiateDbRow                     250       209             383
QQueryBuilder->AddSelectItem                     5036       174             233
QApplicationBase::SetErrorHandler             8751       169             189
QApplicationBase::RestoreErrorHandler             8751       164             165
php::date                                 8751       133             133
DashboardForm->Form_Create                     1       111             15793
QMySqliDatabaseResult->GetNextRow             3637       102             182
UsersGen::QuerySingle                             105       101             67

As we can see, InstantiateDbRow, QMySqliDatabaseRow->GetColumn and InstantiateDbResult are taking too much time?

So just to confirm: the query itself is not necessarily taking a long time, but it's the instantiation of the objects?

If unsure, I would recommend querying (e.g. within the MySQL console itself) to ensure that the query itself isn't taking too much time.  Taking a look at your query, it does look like you are doing quite a bit of logic across a fairly large hierarchy... so depending on how many rows you are doing -to-Many joins across, MySQL could be bottlenecked.

Regardless, if you are sure that the raw query itself is not a bottleneck, then the next question would be: how many rows are being returned by your query, and what are you doing with all those objects?

So for example, if you are returning 10000 rows (and thus instantiating 10000 objects), and all you are doing is something like a foreach on each object to perform something quick, then you might want to consider using QueryCursor and InstantiateCursur instead of QueryArray.

This forum post on QueryCursor should hopefully be of help.

I fit in with your conclusions and looking forward to your updates.